Error on ActiveWorkbook.SaveAs method
 

Hi there,
I hope there is a really simple answer to this but I have not been
able to find a simple solution to error trapping the
ActiveWorkbook.SaveAs
method.
When the following command runs (inside a macro) and I select 'yes'
from the dialog box when it says "the file already exists do want to
replace it?" - that's fine.
When I select 'no' or 'cancel' the following error occurs... Runtime
Error 1004: "Method 'SaveAs'
object '_workbook' failed.


ActiveWorkbook.SaveAs Filename:=sWorkBookPath & "\" _
& sFileName & ".txt", FileFormat:=xlText, CreateBackup:=False

How can I stop the macro crashing when I select 'no'?

Thanks,
Louis

Error on ActiveWorkbook.SaveAs method
 

You have a few choices...

#1. Just overwrite the existing workbook.

application.displayalerts = false
activeworkbook.saveas ....
application.displayalerts = true

#2. Check for the existing file, then ask the user for direction.

dim Resp as long

resp = vbyes
if dir(sWorkBookPath & "\" & sFileName & ".txt") = "" then
'it's not there
else
resp = msgbox(Prompt:="Overwrite the existing file?"), _
buttons:=vbyesno)
end if

if resp = vbyes then
application.displayalerts = false
activeworkbook.saveas ....
application.displayalerts = true
else
msgbox "ok, not saved!"
end if

#3. Handle the error yourself.

on error resume next
activeworkbook.saveas ...
if error.number < 0 then
err.clear
msgbox "not saved"
else
msgbox "saved"
end if
on error goto 0



ll wrote:

Hi there,
I hope there is a really simple answer to this but I have not been
able to find a simple solution to error trapping the
ActiveWorkbook.SaveAs
method.
When the following command runs (inside a macro) and I select 'yes'
from the dialog box when it says "the file already exists do want to
replace it?" - that's fine.
When I select 'no' or 'cancel' the following error occurs... Runtime
Error 1004: "Method 'SaveAs'
object '_workbook' failed.

ActiveWorkbook.SaveAs Filename:=sWorkBookPath & "\" _
& sFileName & ".txt", FileFormat:=xlText, CreateBackup:=False

How can I stop the macro crashing when I select 'no'?

Thanks,
Louis

--

Dave Peterson

Error on ActiveWorkbook.SaveAs method
 

On Jul 27, 10:43 am, Dave Peterson wrote:
You have a few choices...

#1. Just overwrite the existing workbook.

application.displayalerts = false
activeworkbook.saveas ....
application.displayalerts = true

#2. Check for the existing file, then ask the user for direction.

dim Resp as long

resp = vbyes
if dir(sWorkBookPath& "\" & sFileName & ".txt") = "" then
'it's not there
else
resp = msgbox(Prompt:="Overwrite the existing file?"), _
buttons:=vbyesno)
end if

if resp = vbyes then
application.displayalerts = false
activeworkbook.saveas ....
application.displayalerts = true
else
msgbox "ok, not saved!"
end if

#3. Handle the error yourself.

on error resume next
activeworkbook.saveas ...
if error.number < 0 then
err.clear
msgbox "not saved"
else
msgbox "saved"
end if
on error goto 0



ll wrote:

Hi there,
I hope there is a really simple answer to this but I have not been
able to find a simple solution to error trapping the
ActiveWorkbook.SaveAs
method.
When the following command runs (inside a macro) and I select 'yes'
from the dialog box when it says "the file already exists do want to
replace it?" - that's fine.
When I select 'no' or 'cancel' the following error occurs... Runtime
Error 1004: "Method 'SaveAs'
object '_workbook' failed.

ActiveWorkbook.SaveAs Filename:=sWorkBookPath& "\" _
& sFileName & ".txt", FileFormat:=xlText, CreateBackup:=False

How can I stop the macro crashing when I select 'no'?

Thanks,
Louis

--

Dave Peterson


Thanks Dave,
In the second option, using sWorkBookPath and sFileName, are those two
variables pre-defined, or do I need to establish them as string
variables?

Thanks
Louis

Error on ActiveWorkbook.SaveAs method
 

Those are variables. You'll want to declare them and assign the values you want
to them.

ll wrote:

Thanks Dave,
In the second option, using sWorkBookPath and sFileName, are those two
variables pre-defined, or do I need to establish them as string
variables?

Thanks
Louis

--

Dave Peterson