Error on ActiveWorkbook.SaveAs method

[ll]

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]

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

 

[ll]

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

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]

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