Save As Then Replace Bombs Out

P

Paige

When I bring up the saveas menu, if someone selects an existing file and hits
Save, they then get the warning message saying the file already exists and do
they want to replace it. If they hit 'No' or 'Cancel', then the code errors
out. How do I fix this? I still want them to get the warning message, but
if they select 'No' or 'Cancel', the message should disappear and take them
back to the saveas menu so they can select another file or enter a new file
name. The current code I'm using is:

Do
FName = Application.GetSaveAsFilename(InitialFileName:="",
FileFilter:="Microsoft Excel Workbook (*.xls), *.xls")
Loop Until FName <> False
ActiveWorkbook.SaveAs Filename:=FName, FileFormat:=xlWorkbookNormal

Appreciate any help I can get on this; thanks....
 
V

Vergel Adriano

You can check for file existence before calling the SaveAs. Something like
this:

Do
fname = Application.GetSaveAsFilename(InitialFileName:="",
FileFilter:="Microsoft Excel Workbook (*.xls), *.xls")
If fname <> False Then
'check if file exists
If Dir(fname) <> "" Then
'File exists. Ask if okay to replace
If vbYes <> MsgBox("File already exists. Do you want to replace
it?", vbYesNo) Then
'not ok to replace, set fname to False
fname = False
End If
End If
End If
Loop Until fname <> False

ActiveWorkbook.SaveAs Filename:=fname, FileFormat:=xlWorkbookNormal
 
P

Paige

Thanks Vergel! Definitely looks like it will work; will give it a go....have
a great evening, and thanks again for your help.
 

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

Top