Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
rename excel file name
Hi ,
Sorry my question was that I would like to rename the excel file to say whatever maybe typed in cell A1 regards TJ |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
rename excel file name
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 -- Gary''s Student - gsnu200850 "TJ" wrote: Hi , Sorry my question was that I would like to rename the excel file to say whatever maybe typed in cell A1 regards TJ |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
rename excel file name
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 "Gary''s Student" wrote: 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 -- Gary''s Student - gsnu200850 "TJ" wrote: Hi , Sorry my question was that I would like to rename the excel file to say whatever maybe typed in cell A1 regards TJ |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
rename excel file name
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 wrote: 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 "Gary''s Student" wrote: 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 -- Gary''s Student - gsnu200850 "TJ" wrote: Hi , Sorry my question was that I would like to rename the excel file to say whatever maybe typed in cell A1 regards TJ -- Dave Peterson |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
rename excel file name
Yes, I would & the folder to be picked from the next cell b1
thanks "Dave Peterson" wrote: 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 wrote: 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 "Gary''s Student" wrote: 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 -- Gary''s Student - gsnu200850 "TJ" wrote: Hi , Sorry my question was that I would like to rename the excel file to say whatever maybe typed in cell A1 regards TJ -- Dave Peterson |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
rename excel file name
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. TJ wrote: Yes, I would & the folder to be picked from the next cell b1 thanks "Dave Peterson" wrote: 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 wrote: 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 "Gary''s Student" wrote: 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 -- Gary''s Student - gsnu200850 "TJ" wrote: Hi , Sorry my question was that I would like to rename the excel file to say whatever maybe typed in cell A1 regards TJ -- Dave Peterson -- Dave Peterson |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
rename excel file name
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 "Dave Peterson" wrote: 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 wrote: 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 "Gary''s Student" wrote: 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 -- Gary''s Student - gsnu200850 "TJ" wrote: Hi , Sorry my question was that I would like to rename the excel file to say whatever maybe typed in cell A1 regards TJ -- Dave Peterson |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
rename excel file name
|
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
rename excel file name
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...g_a_directory/ If you and all your users are running xl2002+, take a look at VBA's help for: application.filedialog(msoFileDialogFolderPicker) Tel wrote: 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 "Dave Peterson" wrote: 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 wrote: 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 "Gary''s Student" wrote: 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 -- Gary''s Student - gsnu200850 "TJ" wrote: Hi , Sorry my question was that I would like to rename the excel file to say whatever maybe typed in cell A1 regards TJ -- Dave Peterson -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
rename shared Excel file | Excel Discussion (Misc queries) | |||
Can you copy a Excel file to a new directory and rename it? | Excel Discussion (Misc queries) | |||
problem after rename of file | Excel Discussion (Misc queries) | |||
FILE AUTO-RENAME | Excel Discussion (Misc queries) | |||
How to set SAVE AS file name to equal A1 contents when rename file | Excel Discussion (Misc queries) |