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

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

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

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

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

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

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

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