Excel VBA Save As

C

Craig Faircloth

I have some code that saves a file with a set name and
path using the ActiveWorkbook.SaveAs method. When the file
already exists, the user is prompted about whether to
write over the file. If the user answers 'No' or 'Cancel',
an error (#1004) occurs. My error handler hasn't been able
to trap this error for some reason. Is there a quick way
to get Excel to prompt for another file name and then
continue processing the remaining code? Thanks.
 
K

Ken Macksey

Hi

You could use

Application.DisplayAlerts = False

ActiveWorkbook.SaveAs ("Test saveas.xls")

Application.DisplayAlerts = True

which will force an overwrite. If you don't want to do that for fear of
overwriting a different existing file, you could use

Excel.Application.Dialogs(xlDialogSaveAs).Show

where you can change the saveas name or cancel without the error.



HTH



KenM
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

VBA Save As problem with file that already exist 2
VBA Error Handling 4
Hiding an Excel file using VBA 1
SaveAS dialog box gives run time error 1004 in Excel VBA 3
Problem saving backup file with VBA 1
Error When using ActiveWorkBook.SaveAs and EnableEvents in same macro 1
Failure at File Save 2
Trouble Installing Excel VBA Code Cleaner into Office 365 0

Top