To save a .xlsm file in a distinct folder of a Local Drive


Good Day,

I have developed a spreadsheet for our internal use. However, stuck up with the SAVE AS button. 

What I require is that after pressing the SAVE AS button, a new dialogue box will open. After entering the file name and selecting the desired folder in the local drive, the spreadsheet is supposed to be saved in it. The code that I have written is stated underneath which overwrites the previously saved file.

"Sub SaveAsDialog()

  On Error Resume Next

  With Application.FileDialog(msoFileDialogSaveAs)

     .Title = "Please choose a location and file name to save"

     .ButtonName = "SAVE AS"

     .InitialFileName = "E\RTGS TEMPLATE(PARTY)\" 

     If .Show = 0 Then

       MsgBox "The file did not save.", vbCritical

       Exit Sub

     End If

     Application.DisplayAlerts = False


     Application.DisplayAlerts = True

  End With

End Sub"

Can I expect any help from you to resolve this matter?

Thank you for your time and attention



At the outset thank you very much for your valuable advice. Like to inform you that as directed by you I have made the amendments but the error message shows as like "Cannot run the macro "RTGS Bank Template.xlsm'!Macro 5S". The macro may not be available in this workbook or all macros may be disabled". Also, like to add that the other macros are working as usual.
May I request you to extend your kind cooperation to resolve this matter.
Thank you once again for your time and consideration.
subhas1812 Jul 8, '20 at 11:39 am
Add to Discussion



You don't claim that your code doesn't work. Instead, you seem to complain about its over-writing a file by the same name that already exists. If this is true - and even if it isn't - remove the excessive system suppression from your code.

  1. Delete On Error Resume Next.
    How can you fix errors if you aren't alerted to them.
  2. Delete Application.DisplayAlerts = False
    Before over-writing an existing file the system would alert you, asking if you are sure. But with this notice suppressed you are left crying that your file is gone.


See? That's what I told you. Once you allow errors to be announced you can deal with them. In this case it's the syntax by which you specify the InitialFileName. I suspect that E is a drive letter. In that case the name should be "E:\RTGS TEMPLATE(PARTY)\". Specifying the InitialFileName has many rules. Most of the time the instruction is ignored if not compliant. You may have to experiment. But the property is optional and you can also delete the entire line if you can't get it to work.
Variatus (rep: 4549) Jul 8, '20 at 10:51 pm
Add to Discussion

Answer the Question

You must create an account to use the forum. Create an Account or Login