Excel VBA - Why is my "Save As" not working?

0
votes

I have a VBA script in place so that if a cell is blank then Excel will prompt the file to be saved.

This is ensure that the template is not altered. However, when the user clicks save in the "Save As" dialogue box, the file does not save.

This is the code I am using:

If Worksheets("Input").Range("E2").Value = "" Then
    Application.EnableEvents = False
    Application.GetSaveAsFilename InitialFileName:="\\ac35542\Problem Management\Action Plans\ChangeMe.xlsm", FileFilter:="Excel Macro-Enabled Workbook (*.xlsm),*.xlsm"
    Application.EnableEvents = True
    MsgBox "Please ensure fill in the Problem Reference Number, Problem Title, and Select a Contract", vbExclamation, "PR Reference & Title"
    Worksheets("Input").Select
    Range("E2").Select
End If

Why is the file not saving?

2
vbaexcel
GetSaveAsFilename just returns the path and filename that the user selected: it doesn't actually do any saving. You have to call SaveAs if you want to save the file. – Tim Williams
@TimWilliams Is there any way of just displaying the "Save As" prompt so the user can same the document? – Petay87
sorry, @Tim, didn't see your comment while posting an answer – Dmitry Pavliv
Application.Dialogs(xlDialogSaveAs).Show – Tim Williams

2 Answers

3
votes

As follow up from MSDN

Application.GetSaveAsFilename displays the standard Save As dialog box and gets a file name from the user without actually saving any files..

use this one instead:

Dim fileSaveName
If Worksheets("Input").Range("E2").Value = "" Then
    Application.EnableEvents = False
    fileSaveName = Application.GetSaveAsFilename(InitialFileName:="\\ac35542\Problem Management\Action Plans\ChangeMe.xlsm", FileFilter:="Excel Macro-Enabled Workbook (*.xlsm),*.xlsm")
    Application.EnableEvents = True

    If fileSaveName <> "False" Then
        Application.DisplayAlerts = False
        ThisWorkbook.SaveAs (fileSaveName)
        Application.DisplayAlerts = True
    End If

    MsgBox "Please ensure fill in the Problem Reference Number, Problem Title, and Select a Contract", vbExclamation, "PR Reference & Title"
    Worksheets("Input").Select
    Range("E2").Select
End If
0
votes

I think Dmitry Pavliv's method is fine, but I think the "InitialFileName:="\ac35542\Problem Management\Action Plans\ChangeMe.xlsm" part makes it a little bit less dynamic.

For me, the below code worked perfectly:

ExportPath = Application.GetSaveAsFilename(FILEFILTER:="Excel Files (*.xlsx), *.xlsx", Title:="")
'Basically, user will specify the path and give it a name and click on Save. It won't get saved until the next line though.
ActiveWorkbook.SaveAs (ExportPath)