return file name

[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?

 

[Jordon]

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?

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.

 

[Gord Dibben]

ActiveWorkbook.SaveAs Filename:="C:\Gordstuff\" & _
Sheets("Sheet1").Range("A1").Value


Gord Dibben MS Excel MVP

 

[chuck]

ActiveWorkbook.SaveAs Filename:="C:\Gordstuff\" & _
Sheets("Sheet1").Range("A1").Value


Gord Dibben MS Excel MVP

Thanks Gord but I am quite new to Excel. What do I do with this? Include it
in a macro?

 

[Gord Dibben]

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

 

[chuck]

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

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)" ?

 

[Gord Dibben]

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

 

[chuck]

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

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

 

[Gord Dibben]

You're welcome.

Yes. VBA is a great tool.

Gord