Macro crashes if option "yes" is not chosen
 

Hi everyone,

I have a macro called "generate".

The following part of the code if giving me lot of grief:

ActiveWorkbook.SaveAs FileName:=Fname, _
FileFormat:=xlNormal, Password:="", WriteResPassword:="", _
ReadOnlyRecommended:=False, CreateBackup:=False

If there is a file by the name Fname, then it asks whether it should be
replaced or not. If you click "Yes" it works fine, but if you choose
"No" or "Cancel" it crashes with the following message:

Runtime Error '1004':
Method 'SaveAs' of Object "_Workbook" failed

Any help would be appreciated!

S


--
schoujar
------------------------------------------------------------------------
schoujar's Profile: http://www.excelforum.com/member.php...o&userid=26574
View this thread: http://www.excelforum.com/showthread...hreadid=468109

Macro crashes if option "yes" is not chosen
 

It is always a good idea to set up some instructions for errorhandling

You could enter the following line before your saveas statement to ignore
errors and continue code execution

on error resume next

then enter the following after your saveas statement (which will negate the
above instruction)

on error goto 0


"schoujar" wrote:


Hi everyone,

I have a macro called "generate".

The following part of the code if giving me lot of grief:

ActiveWorkbook.SaveAs FileName:=Fname, _
FileFormat:=xlNormal, Password:="", WriteResPassword:="", _
ReadOnlyRecommended:=False, CreateBackup:=False

If there is a file by the name Fname, then it asks whether it should be
replaced or not. If you click "Yes" it works fine, but if you choose
"No" or "Cancel" it crashes with the following message:

Runtime Error '1004':
Method 'SaveAs' of Object "_Workbook" failed

Any help would be appreciated!

S


--
schoujar
------------------------------------------------------------------------
schoujar's Profile: http://www.excelforum.com/member.php...o&userid=26574
View this thread: http://www.excelforum.com/showthread...hreadid=468109

Macro crashes if option "yes" is not chosen
 

You have two choices..... the first will ignore the error thrown by pressing
Cancel and NOT save the workbook. The second will not display the SaveAs
Dialog and the file will always be saved, even if it already exists.


On Error Resume Next
ActiveWorkbook.SaveAs FileName:=Fname, _
FileFormat:=xlNormal, Password:="", WriteResPassword:="", _
ReadOnlyRecommended:=False, CreateBackup:=False
On Error GoTo 0

OR

Application.DisplayAlerts = False
ActiveWorkbook.SaveAs FileName:=Fname, _
FileFormat:=xlNormal, Password:="", WriteResPassword:="", _
ReadOnlyRecommended:=False, CreateBackup:=False
Application.DisplayAlerts = True

--
Cheers
Nigel



"schoujar" wrote in
message ...

Hi everyone,

I have a macro called "generate".

The following part of the code if giving me lot of grief:

ActiveWorkbook.SaveAs FileName:=Fname, _
FileFormat:=xlNormal, Password:="", WriteResPassword:="", _
ReadOnlyRecommended:=False, CreateBackup:=False

If there is a file by the name Fname, then it asks whether it should be
replaced or not. If you click "Yes" it works fine, but if you choose
"No" or "Cancel" it crashes with the following message:

Runtime Error '1004':
Method 'SaveAs' of Object "_Workbook" failed

Any help would be appreciated!

S


--
schoujar
------------------------------------------------------------------------
schoujar's Profile:
http://www.excelforum.com/member.php...o&userid=26574
View this thread: http://www.excelforum.com/showthread...hreadid=468109

Macro crashes if option "yes" is not chosen
 

schoujar,
Which is logically correct.
The .SaveAs method did fail, because you did not give permission to
overwrite, so no save occurred.

You need some error trapping. Look into "On Error" in the Help.

NickHK

"schoujar" wrote in
message ...

Hi everyone,

I have a macro called "generate".

The following part of the code if giving me lot of grief:

ActiveWorkbook.SaveAs FileName:=Fname, _
FileFormat:=xlNormal, Password:="", WriteResPassword:="", _
ReadOnlyRecommended:=False, CreateBackup:=False

If there is a file by the name Fname, then it asks whether it should be
replaced or not. If you click "Yes" it works fine, but if you choose
"No" or "Cancel" it crashes with the following message:

Runtime Error '1004':
Method 'SaveAs' of Object "_Workbook" failed

Any help would be appreciated!

S


--
schoujar
------------------------------------------------------------------------
schoujar's Profile:
http://www.excelforum.com/member.php...o&userid=26574
View this thread: http://www.excelforum.com/showthread...hreadid=468109