Excel 2010 VBA run time error 1004 with SaveAs if file exists.

Here is a section of code:

filesavename = Application.GetSaveAsFilename(FileMonth, _
    fileFilter:="(*.xlsm), *.xlsm, (*.xlsx), *.xlsx,(*.xls), *.xls")
' FileMonth is the Workbook name, filter options to save a older version file
ActiveWorkbook.SaveAs filesavename
   On Error GoTo SaveError
' other irrelevant unrelated code
'
'
SaveError:
msg = "File name already exists and has not been saved." & Chr(13) & Chr(13) _
    & "Check that the Year and Month are correct." & Chr(13) & Chr(13)
MsgBox msg

End Sub

Works a treat until the same file name exists.
Dialog box dutifully appears detecting existing file with options to save: Yes, No, Cancel
Yes: works ok and the file is overwritten.
No and Cancel: opens the run time error box,stating - Method "SaveAs" object  '_Workbook'  failed
Options are to End or Debug.
End: clears the box and the code continues to run with the file unsaved.

If the error boxes are prevented from displaying, then the file is saved, overwriting the original.
Tried error trapping after the SaveAs, but is not detected.

Any ideas?



 

Question Info


Last updated October 9, 2019 Views 25,096 Applies to:
Answer
Answer

Try it this way.

Bernie

 

Dim FileMonth As String
Dim FileSaveName As String

FileMonth = ThisWorkbook.Name

GetName:

FileSaveName = Application.GetSaveAsFilename(FileMonth, _
    fileFilter:="(*.xlsm), *.xlsm, (*.xlsx), *.xlsx,(*.xls), *.xls")
' FileMonth is the Workbook name, filter options to save a older version file
If Dir(FileSaveName) = "" Then
    ActiveWorkbook.SaveAs FileSaveName
Else
    If MsgBox("That file exists. Overwrite?", vbYesNo) = vbNo Then GoTo GetName
    Application.DisplayAlerts = False
    ActiveWorkbook.SaveAs FileSaveName
    Application.DisplayAlerts = True
End If
' other irrelevant unrelated code
'
'

16 people were helped by this reply

·

Did this solve your problem?

Sorry this didn't help.

Great! Thanks for marking this as the answer.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this response?

Thanks for your feedback.