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?

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


"Horatio J. Bilge, Jr." wrote:

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?

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
---------------
Jacob Skaria


"Jacob Skaria" wrote:

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


"Horatio J. Bilge, Jr." wrote:

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?

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

"Jacob Skaria" wrote:

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
---------------
Jacob Skaria


"Jacob Skaria" wrote:

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


"Horatio J. Bilge, Jr." wrote:

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?