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

Hi Rob

Use this

Application.DisplayAlerts = False
'Save code
Application.DisplayAlerts = True


--
Regards Ron de Bruin
http://www.rondebruin.nl


"RobMack" <RobMack@discussions.microsoft.com> wrote in message news:289E9AFC-3EF9-4B36-BBE1-EBEF8C6273C3@microsoft.com...
>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"?

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" <RobMack@discussions.microsoft.com> wrote in message
news:289E9AFC-3EF9-4B36-BBE1-EBEF8C6273C3@microsoft.com...
> 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"?

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" <RobMack@discussions.microsoft.com> wrote in message news:289E9AFC-3EF9-4B36-BBE1-EBEF8C6273C3@microsoft.com...
> >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"?
>
>
>

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" <RobMack@discussions.microsoft.com> wrote in message news:7F4ED29A-FF86-4AEC-9DF8-D445486BD096@microsoft.com...
> 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" <RobMack@discussions.microsoft.com> wrote in message news:289E9AFC-3EF9-4B36-BBE1-EBEF8C6273C3@microsoft.com...
>> >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"?
>>
>>
>>

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" <RobMack@discussions.microsoft.com> wrote in message news:7F4ED29A-FF86-4AEC-9DF8-D445486BD096@microsoft.com...
> > 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" <RobMack@discussions.microsoft.com> wrote in message news:289E9AFC-3EF9-4B36-BBE1-EBEF8C6273C3@microsoft.com...
> >> >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"?
> >>
> >>
> >>
>
>
>

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" <RobMack@discussions.microsoft.com> wrote in message news:EACEF24A-402C-46E5-BCB9-9E24379A4FD2@microsoft.com...
> 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" <RobMack@discussions.microsoft.com> wrote in message news:7F4ED29A-FF86-4AEC-9DF8-D445486BD096@microsoft.com...
>> > 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" <RobMack@discussions.microsoft.com> wrote in message news:289E9AFC-3EF9-4B36-BBE1-EBEF8C6273C3@microsoft.com...
>> >> >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"?
>> >>
>> >>
>> >>
>>
>>
>>

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" <rondebruin@kabelfoon.nl> wrote in message news:ewzUxJCKGHA.1728@TK2MSFTNGP14.phx.gbl...
> 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" <RobMack@discussions.microsoft.com> wrote in message news:EACEF24A-402C-46E5-BCB9-9E24379A4FD2@microsoft.com...
>> 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" <RobMack@discussions.microsoft.com> wrote in message news:7F4ED29A-FF86-4AEC-9DF8-D445486BD096@microsoft.com...
>>> > 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" <RobMack@discussions.microsoft.com> wrote in message news:289E9AFC-3EF9-4B36-BBE1-EBEF8C6273C3@microsoft.com...
>>> >> >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"?
>>> >>
>>> >>
>>> >>
>>>
>>>
>>>
>
>