rename excel file name

[TJ]

Hi ,

Sorry my question was that I would like to rename the excel file to say
whatever maybe typed in cell A1

regards
TJ

 

[Gary''s Student]

The usual way to rename an excel file from within Excel is to SaveAs the new
name:

Sub Macro2()
Sheets("Sheet1").Activate
ActiveWorkbook.SaveAs Filename:=Range("A1").Value
End Sub

 

[TJ]

Please advise where I would insert this code in the sheet or the workbook.
I am looking for soemthing on the similar lines of sheet i.e.

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address <> Range("a1").Address Then Exit Sub
ActiveSheet.Name = Target

where I am able to change the file name from the sheet itself

thanks
TJ

 

[Dave Peterson]

The only way to rename the file/workbook is to save it with a new name.

Are you sure you want do this?

If so, what folder would you save it to--always the same or pick up the folder
in another cell?

 

[TJ]

Yes, I would & the folder to be picked from the next cell b1
thanks

 

[Dave Peterson]

This doesn't sound like a good idea to me. I'd create a dedicated macro to do
the save and only run it when I needed to--not for each change to A1.

But if you want...

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
dim myFolderName as string
dim myFileName as string

if target.cells.count > 1 then
exit sub 'one cell at a time!
end if

if intersect(target, me.range("a1:B1")) is nothing then
exit sub
end if

myfilename = me.range("a1").value
if myfilename = "" then
exit sub 'no file entered
end if

myfoldername = me.range("b1").value
if myfoldername = "" then
exit sub 'no folder entered
end if

if right(myfoldername,1) <> "\" then
myfoldername = myfoldername & "\"
end if

application.displayalerts = false 'no overwrite this file pompt
on error resume next
thisworkbook.saveas filename:=myfoldername & myfilename, _
fileformat:=xlworkbooknormal
if err.number <> 0 then
err.clear
msgbox "Error while saving"
else
msgbox "saved ok" 'comment out when done testing!
end if
on error goto 0
application.displayalerts = true

End sub

I really wouldn't use this.

 

[Tel]

Hi Dave,

Using a code you posted earlier (2004) for which I've marked as "helpful" -
Thanks. I've managed to create the "saveas Macro using cell contents".
However, it automatically saves it to my documents.

Here's my code

Sub save_it()
Dim fname
With ActiveWorkbook
fname = "VMRP_" & .Worksheets("TEST").Range("A1").Value & "_" &
..Worksheets("sheet2").Range("B3").Value & ".xls"
..SaveAs fname
End With
End Sub

Is it possible to insert a break which brings up the saveas dialog box and
enables the user to change the filename (possibly to match their naming
protocols and conventions and pick the location where they would store it?

Many thanks

Terry

 

[Don Guillett]

Maybe the user can be instructed to use
File>save as

 

[Dave Peterson]

Jim Rech has a BrowseForFolder routine at:
http://www.oaltd.co.uk/MVP/Default.htm
(look for BrowseForFolder)

John Walkenbach has one at:
http://spreadsheetpage.com/index.php/tip/selecting_a_directory/

If you and all your users are running xl2002+, take a look at VBA's help for:
application.filedialog(msoFileDialogFolderPicker)