raphagcwill
New Member
- Joined
- Jan 12, 2016
- Messages
- 41
Hello there.
Here I am again hoping to get some help with something you may consider a piece of cake
The code below opens a windows with the suggested file name and format.
If there is no file with the same name in the folder and the user clicks on "Save", then everything works great.
If a file with the same name already exists in the folder, when the user clicks on "Save" a new windows pops up:
"A file named....already exists in this location. Do you want to replace it? Yes, No, Cancel"
When "Yes" is chosen, then the new file overwrites the old one.
When either "No" or "Cancel" is chosen, then the user gets the 1004 error. "Run time error 1004, Method of "SaveAs" of object '_Workbook failed. "
The debug brings me to this line:
ActiveWorkbook.SaveAs Filename:=DataFileName
What I am trying to do is to bypass this error.
I would like the macro to reopen the "Saveas" window when user chooses "No" and to exit sub when the user chooses "Cancel", without any Debug.
The code was originally copied from
http://www.mrexcel.com/forum/excel-questions/672970-application-getsaveasfilename-help.html
I did my reasearch and found a similiar issue and a possible solution, but I am not sure how to apply it to my case
GetSaveAs problem when filename already exists | Windows Secrets Lounge
Thanks in advance,
Kind Regards,
Raphael
Here I am again hoping to get some help with something you may consider a piece of cake

The code below opens a windows with the suggested file name and format.
If there is no file with the same name in the folder and the user clicks on "Save", then everything works great.
If a file with the same name already exists in the folder, when the user clicks on "Save" a new windows pops up:
"A file named....already exists in this location. Do you want to replace it? Yes, No, Cancel"
When "Yes" is chosen, then the new file overwrites the old one.
When either "No" or "Cancel" is chosen, then the user gets the 1004 error. "Run time error 1004, Method of "SaveAs" of object '_Workbook failed. "
The debug brings me to this line:
ActiveWorkbook.SaveAs Filename:=DataFileName
What I am trying to do is to bypass this error.
I would like the macro to reopen the "Saveas" window when user chooses "No" and to exit sub when the user chooses "Cancel", without any Debug.
The code was originally copied from
http://www.mrexcel.com/forum/excel-questions/672970-application-getsaveasfilename-help.html
I did my reasearch and found a similiar issue and a possible solution, but I am not sure how to apply it to my case
GetSaveAs problem when filename already exists | Windows Secrets Lounge
Code:
'Rename first sheet
ActiveSheet.name = "Raphael_" & Range("J2").Value & "_" & Range("L2").Value
fName = Application.GetSaveAsFilename(InitialFileName:="Raphael_" & Range("J2").Value & "_" & Range("L2").Value, FileFilter:="Excel Files (*.xlsm), *.xlsm", Title:="Save As")
If fName = False Then Exit Sub
ActiveWorkbook.SaveAs Filename:=fName
'File saved
MsgBox "File successfully created under Raphael folder"
Thanks in advance,
Kind Regards,
Raphael