return file name

  • Thread starter Thread starter chuck
  • Start date Start date
C

chuck

What do I put in a cell to get it to return the file name? Is there a way,
inversely, to get the file to adopt a default name equal to the content of a
cell when saving?
 
chuck said:
What do I put in a cell to get it to return the file name? Is there a way,
inversely, to get the file to adopt a default name equal to the content of a
cell when saving?
Click to expand...

To get the file name...

=MID(LEFT(CELL("filename",A1),FIND("]",CELL("filename",A1))-1),
FIND("[",CELL("filename",A1))+1,255)

File name and path...

=SUBSTITUTE(LEFT(CELL("filename",A1),FIND("]",CELL("filename",A1)
)-1),"[","")

Don't know about naming a file for the contents of a cell.
 
ActiveWorkbook.SaveAs Filename:="C:\Gordstuff\" & _
Sheets("Sheet1").Range("A1").Value


Gord Dibben MS Excel MVP
 
Gord Dibben said:
ActiveWorkbook.SaveAs Filename:="C:\Gordstuff\" & _
Sheets("Sheet1").Range("A1").Value


Gord Dibben MS Excel MVP
Click to expand...

Thanks Gord but I am quite new to Excel. What do I do with this? Include it
in a macro?
 
The other responder showed how to get the filename into a cell.

My code is a macro and will save the file using whatever is in Sheet1 A1 as
the saveas name.

Wrap a Sub, End Sub around it like

Sub Saveit()
saveas code from below
End Sub

Place the macro in a general module in your Personal.xls


Gord
 
Gord Dibben said:
The other responder showed how to get the filename into a cell.

My code is a macro and will save the file using whatever is in Sheet1 A1
as
the saveas name.

Wrap a Sub, End Sub around it like

Sub Saveit()
saveas code from below
End Sub

Place the macro in a general module in your Personal.xls


Gord
Click to expand...

Thanks again Gord. I was most of the way there with that. I just wasn't too
clear on what to do when it said that I could not record a macro on this
type of workbook. I've got it working now. I created a button to run the
macro and saved the workbook as a macro enabled workbook.
Is there a way to make the button text be the content of a cell, for
instance name the button "Save As & (cell D31 content)" ?
 
Will the text in D31 be changed manually on occasion?

You could use sheet event code to change the Button Text.

Assumes the button was created using Forms Toolbar

Private Sub Worksheet_Change(ByVal Target As Range)

Me.Shapes("Button 1").Select ' edit to your button name

Selection.Characters.Text = "Save As " & Range("D31").Value
Me.Range("D31").Select
End Sub

Right-click the sheet tab and "View Code". Copy/paste the code above into
that sheet module.

Alt + q to return to Excel.

Enter a value in D31 and see Button 1 text change.


Gord
 
Gord Dibben said:
Will the text in D31 be changed manually on occasion?

You could use sheet event code to change the Button Text.

Assumes the button was created using Forms Toolbar

Private Sub Worksheet_Change(ByVal Target As Range)

Me.Shapes("Button 1").Select ' edit to your button name

Selection.Characters.Text = "Save As " & Range("D31").Value
Me.Range("D31").Select
End Sub

Right-click the sheet tab and "View Code". Copy/paste the code above into
that sheet module.

Alt + q to return to Excel.

Enter a value in D31 and see Button 1 text change.


Gord
Click to expand...

That works beautifully. I really need to learn more about using VB apps in
MS Office documents. Thanks very much again Gord.
 
Back
Top