| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to repeat "save as" in Macros
I have a procedure whereby files need to be automatically saved, "save as".
How can I code the procedure so that it will rename the file and I don't get the error message that "file already exists, do you want to replace"? |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
How to repeat "save as" in Macros
Hi Rob
Use this Application.DisplayAlerts = False 'Save code Application.DisplayAlerts = True -- Regards Ron de Bruin http://www.rondebruin.nl "RobMack" wrote in message ... I have a procedure whereby files need to be automatically saved, "save as". How can I code the procedure so that it will rename the file and I don't get the error message that "file already exists, do you want to replace"? |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
How to repeat "save as" in Macros
Do you want to suppress the message and automatically save over the exsting
file? Then, do you really want to use a save rather than as SaveAS? If you really want a new name, perhaps: fname = ActiveWorkbook.FullName ' remove .xls fName = Left(fName,len(fName) - 4) i = 0 do i = i + 1 fName1 = fName & i & ".xls" loop until dir(fName1) = "" ActiveWorkbook.SaveAs fName1 -- Regards, Tom Ogilvy "RobMack" wrote in message ... I have a procedure whereby files need to be automatically saved, "save as". How can I code the procedure so that it will rename the file and I don't get the error message that "file already exists, do you want to replace"? |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
How to repeat "save as" in Macros
Only problem is that I need an individual name for each file saved. This
saves into same file name over and over. "Ron de Bruin" wrote: Hi Rob Use this Application.DisplayAlerts = False 'Save code Application.DisplayAlerts = True -- Regards Ron de Bruin http://www.rondebruin.nl "RobMack" wrote in message ... I have a procedure whereby files need to be automatically saved, "save as". How can I code the procedure so that it will rename the file and I don't get the error message that "file already exists, do you want to replace"? |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
How to repeat "save as" in Macros
Hi Rob
You can use the Date/Time in the file name maybe Do you want to save the same file a few times with a different name (Do I understand you correct ?) Please explain what you want to do -- Regards Ron de Bruin http://www.rondebruin.nl "RobMack" wrote in message ... Only problem is that I need an individual name for each file saved. This saves into same file name over and over. "Ron de Bruin" wrote: Hi Rob Use this Application.DisplayAlerts = False 'Save code Application.DisplayAlerts = True -- Regards Ron de Bruin http://www.rondebruin.nl "RobMack" wrote in message ... I have a procedure whereby files need to be automatically saved, "save as". How can I code the procedure so that it will rename the file and I don't get the error message that "file already exists, do you want to replace"? |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
How to repeat "save as" in Macros
Hi Ron,
I need to save different files in sequence. The process I have created cuts a reference number from ws 1, pastes into ws 2 that has vlookup formulas to retrieve other information from the reference number pasted, then prints, and then saves each file with different information. This process repeats until there are no more reference numbers. I am having some trouble with the saving. I have listed the code below that I have been trying to develop for "saving as". ChDir "S:\RVI\Documents\BNS References\Audit Memos" ActiveWorkbook.SaveAs Filename:= _ "S:\RVI\Documents\BNS References\Audit Memos\BNSTest\B200.xls", FileFormat _ :=xlNormal, Password:="", WriteResPassword:="", ReadOnlyRecommended:= _ False, CreateBackup:=False fName = ActiveWorkbook.FullName ' remove .xls fName = Left(fName, Len(fName) - 4) i = 0 Do i = i + 0.1 fName1 = fName & i & ".xls" Loop Until Dir(fName1) = "" ActiveWorkbook.SaveAs fName1 I'm not sure if this is of any help, but any assistance would be appreciated. Rob "Ron de Bruin" wrote: Hi Rob You can use the Date/Time in the file name maybe Do you want to save the same file a few times with a different name (Do I understand you correct ?) Please explain what you want to do -- Regards Ron de Bruin http://www.rondebruin.nl "RobMack" wrote in message ... Only problem is that I need an individual name for each file saved. This saves into same file name over and over. "Ron de Bruin" wrote: Hi Rob Use this Application.DisplayAlerts = False 'Save code Application.DisplayAlerts = True -- Regards Ron de Bruin http://www.rondebruin.nl "RobMack" wrote in message ... I have a procedure whereby files need to be automatically saved, "save as". How can I code the procedure so that it will rename the file and I don't get the error message that "file already exists, do you want to replace"? |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
How to repeat "save as" in Macros
Hi Rob
Test this With the reference numbers in Sheets("Sheet1").Range("A1:A5") it will copy the value in the loop in Sheets("Sheet2").Range("D1") Then print and save the file with a number in the PathStr folder Sub test() Dim PathStr As String Dim cell As Range Dim i As Long PathStr = "S:\RVI\Documents\BNS References\Audit Memos\BNSTest\" i = 0 With Sheets("Sheet2") 'loop through the reference numbers In Sheets("Sheet1").Range("A1:A5") For Each cell In Sheets("Sheet1").Range("A1:A5") 'D1 in Sheets("Sheet2") is the lookup value of your vlookup formulas .Range("D1").Value = cell.Value Application.Calculate .PrintOut i = i + 1 ActiveWorkbook.SaveCopyAs PathStr & Left(ActiveWorkbook.Name, Len(ActiveWorkbook.Name) - 4) & i Next cell End With End Sub -- Regards Ron de Bruin http://www.rondebruin.nl "RobMack" wrote in message ... Hi Ron, I need to save different files in sequence. The process I have created cuts a reference number from ws 1, pastes into ws 2 that has vlookup formulas to retrieve other information from the reference number pasted, then prints, and then saves each file with different information. This process repeats until there are no more reference numbers. I am having some trouble with the saving. I have listed the code below that I have been trying to develop for "saving as". ChDir "S:\RVI\Documents\BNS References\Audit Memos" ActiveWorkbook.SaveAs Filename:= _ "S:\RVI\Documents\BNS References\Audit Memos\BNSTest\B200.xls", FileFormat _ :=xlNormal, Password:="", WriteResPassword:="", ReadOnlyRecommended:= _ False, CreateBackup:=False fName = ActiveWorkbook.FullName ' remove .xls fName = Left(fName, Len(fName) - 4) i = 0 Do i = i + 0.1 fName1 = fName & i & ".xls" Loop Until Dir(fName1) = "" ActiveWorkbook.SaveAs fName1 I'm not sure if this is of any help, but any assistance would be appreciated. Rob "Ron de Bruin" wrote: Hi Rob You can use the Date/Time in the file name maybe Do you want to save the same file a few times with a different name (Do I understand you correct ?) Please explain what you want to do -- Regards Ron de Bruin http://www.rondebruin.nl "RobMack" wrote in message ... Only problem is that I need an individual name for each file saved. This saves into same file name over and over. "Ron de Bruin" wrote: Hi Rob Use this Application.DisplayAlerts = False 'Save code Application.DisplayAlerts = True -- Regards Ron de Bruin http://www.rondebruin.nl "RobMack" wrote in message ... I have a procedure whereby files need to be automatically saved, "save as". How can I code the procedure so that it will rename the file and I don't get the error message that "file already exists, do you want to replace"? |
|
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
How to repeat "save as" in Macros
Oops, forget this
& ".xls" ActiveWorkbook.SaveCopyAs PathStr & Left(ActiveWorkbook.Name, _ Len(ActiveWorkbook.Name) - 4) & i & ".xls" -- Regards Ron de Bruin http://www.rondebruin.nl "Ron de Bruin" wrote in message ... Hi Rob Test this With the reference numbers in Sheets("Sheet1").Range("A1:A5") it will copy the value in the loop in Sheets("Sheet2").Range("D1") Then print and save the file with a number in the PathStr folder Sub test() Dim PathStr As String Dim cell As Range Dim i As Long PathStr = "S:\RVI\Documents\BNS References\Audit Memos\BNSTest\" i = 0 With Sheets("Sheet2") 'loop through the reference numbers In Sheets("Sheet1").Range("A1:A5") For Each cell In Sheets("Sheet1").Range("A1:A5") 'D1 in Sheets("Sheet2") is the lookup value of your vlookup formulas .Range("D1").Value = cell.Value Application.Calculate .PrintOut i = i + 1 ActiveWorkbook.SaveCopyAs PathStr & Left(ActiveWorkbook.Name, Len(ActiveWorkbook.Name) - 4) & i Next cell End With End Sub -- Regards Ron de Bruin http://www.rondebruin.nl "RobMack" wrote in message ... Hi Ron, I need to save different files in sequence. The process I have created cuts a reference number from ws 1, pastes into ws 2 that has vlookup formulas to retrieve other information from the reference number pasted, then prints, and then saves each file with different information. This process repeats until there are no more reference numbers. I am having some trouble with the saving. I have listed the code below that I have been trying to develop for "saving as". ChDir "S:\RVI\Documents\BNS References\Audit Memos" ActiveWorkbook.SaveAs Filename:= _ "S:\RVI\Documents\BNS References\Audit Memos\BNSTest\B200.xls", FileFormat _ :=xlNormal, Password:="", WriteResPassword:="", ReadOnlyRecommended:= _ False, CreateBackup:=False fName = ActiveWorkbook.FullName ' remove .xls fName = Left(fName, Len(fName) - 4) i = 0 Do i = i + 0.1 fName1 = fName & i & ".xls" Loop Until Dir(fName1) = "" ActiveWorkbook.SaveAs fName1 I'm not sure if this is of any help, but any assistance would be appreciated. Rob "Ron de Bruin" wrote: Hi Rob You can use the Date/Time in the file name maybe Do you want to save the same file a few times with a different name (Do I understand you correct ?) Please explain what you want to do -- Regards Ron de Bruin http://www.rondebruin.nl "RobMack" wrote in message ... Only problem is that I need an individual name for each file saved. This saves into same file name over and over. "Ron de Bruin" wrote: Hi Rob Use this Application.DisplayAlerts = False 'Save code Application.DisplayAlerts = True -- Regards Ron de Bruin http://www.rondebruin.nl "RobMack" wrote in message ... I have a procedure whereby files need to be automatically saved, "save as". How can I code the procedure so that it will rename the file and I don't get the error message that "file already exists, do you want to replace"? |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Selecting "Save As" adds "Copy of" to file name- MS Excel 2007 | Excel Discussion (Misc queries) | |||
| "CELL("FILENAME") NOT UPDATE AFTER "SAVE AS" ACTION | Excel Discussion (Misc queries) | |||
| "Save" and "Save As" options greyed out - "Save as Webpage" option | Excel Discussion (Misc queries) | |||
| save and restore "Workbook Menu Bar" & "Cell" menus | Excel Programming | |||
| Problem- Recording macros for "file save" and "File open" | Excel Programming | |||
Linear Mode
