SaveAs method error

[Horatio J. Bilge, Jr.]

I have noticed a problem with the SaveAs method I am using. Here is the code:
Dim fSaveName as Variant
Do
fSaveName = Application.GetSaveAsFilename
Loop Until fSaveName <> False
ThisWorkbook.SaveAs fSaveName

If the file name/location I choose in the GetSaveAsFilename dialog already
exists, I am asked if I want to replace it.
Clicking Yes replaces the file as expected, but clicking No or Cancel leads
to an error: "Method 'SaveAs' of object '_Workbook' failed"

Is there a way to handle this situation?

 

[Jacob Skaria]

Try the below..

Dim fSaveName As Variant
Do
fSaveName = Application.GetSaveAsFilename
Loop Until fSaveName <> False
If Dir(fSaveName, vbNormal) <> "" Then
If MsgBox("File already exists, Overwrite ?", vbYesNo + vbDefaultButton2) _
<> vbYes Then Exit Sub
End If
Application.DisplayAlerts = True
ThisWorkbook.SaveAs fSaveName
Application.DisplayAlerts = False

If this post helps click Yes

 

[Jacob Skaria]

Correction....for the last line.Try the below

Dim fSaveName As Variant
Do
fSaveName = Application.GetSaveAsFilename
Loop Until fSaveName <> False
If Dir(fSaveName, vbNormal) <> "" Then
If MsgBox("File already exists, Overwrite ?", vbYesNo + vbDefaultButton2) _
<> vbYes Then Exit Sub
End If
Application.DisplayAlerts = False
ThisWorkbook.SaveAs fSaveName
Application.DisplayAlerts = True


If this post helps click Yes

 

[Horatio J. Bilge, Jr.]

Thank you! That works great. I had to add a second End If statement, and then
instead of exiting the sub, I sent it back to the GetSaveAsFilename loop:

Dim fSaveName As Variant
WhereToSave:
Do
fSaveName = Application.GetSaveAsFilename
Loop Until fSaveName <> False
If Dir(fSaveName, vbNormal) <> "" Then
If MsgBox("File already exists, Overwrite ?", vbYesNo + vbDefaultButton2) <>
vbYes Then
GoTo WhereToSave
End If
End If
Application.DisplayAlerts = False
ThisWorkbook.SaveAs fSaveName
Application.DisplayAlerts = True