return file name

chuck · External Since: Apr 16, 2010 Posts: 4

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 · External Since: Apr 16, 2010 Posts: 1

chuck wrote:
> 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.

--
Jordon

Gord Dibben · External Since: Feb 23, 2004 Posts: 9431

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

Gord Dibben MS Excel MVP

On Fri, 16 Apr 2010 14:21:13 -0400, "chuck"
wrote:

>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 · External Since: Apr 16, 2010 Posts: 4

"Gord Dibben" <gorddibbATshawDOTca> wrote in message

> 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 · External Since: Feb 23, 2004 Posts: 9431

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

On Wed, 21 Apr 2010 08:38:11 -0400, "chuck"
wrote:

>
>"Gord Dibben" <gorddibbATshawDOTca> wrote in message
>
>> 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?
>

chuck · External Since: Apr 16, 2010 Posts: 4

"Gord Dibben" <gorddibbATshawDOTca> wrote in message

> 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 · External Since: Feb 23, 2004 Posts: 9431

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

On Wed, 21 Apr 2010 11:27:29 -0400, "chuck"
wrote:

>
>"Gord Dibben" <gorddibbATshawDOTca> wrote in message
>
>> 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)" ?
>

chuck · External Since: Apr 16, 2010 Posts: 4

"Gord Dibben" <gorddibbATshawDOTca> wrote in message
...
> 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 · External Since: Feb 23, 2004 Posts: 9431

You're welcome.

Yes. VBA is a great tool.

Gord

On Wed, 21 Apr 2010 17:09:40 -0400, "chuck"
wrote:

>"Gord Dibben" <gorddibbATshawDOTca> wrote in message
> ...
>> 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.
>