How to save a file selected in Save As dialog?

Question

I found code online that opens a Save As dialog to a location on a drive.

When you click "save" the file does not save.

Dim varResult As Variant
'displays the save file dialog
varResult = Application.GetSaveAsFilename(FileFilter:= _
  "Excel Files (*.xlsx), *.xlsx", Title:="Save PO", _
  InitialFileName:="\\showdog\service\Service_job_PO\")
'checks to make sure the user hasn't canceled the dialog
If varResult <> False Then
    Exit Sub
End If 

Answer 1

You have to actually explicitly tell Excel to save the workbook.

Sub Mac2()
        Dim varResult As Variant
        Dim ActBook As Workbook

        'displays the save file dialog
        varResult = Application.GetSaveAsFilename(FileFilter:= _
                 "Excel Files (*.xlsx), *.xlsx", Title:="Save PO", _
                InitialFileName:="\\showdog\service\Service_job_PO\")

        'checks to make sure the user hasn't canceled the dialog
        If varResult <> False Then
            ActiveWorkbook.SaveAs Filename:=varResult, _
            FileFormat:=xlWorkbookNormal
            Exit Sub
        End If
End Sub

Using the GetSaveAsFilename only gets the path of the file to save, whereas the SaveAs method actually saves the workbook.

Upon some consideration, I might suggest using the SaveCopyAs method instead of simply SaveAs. As the name suggests, this will leave your original workbook in tact and save off a copy. To do this is a rather simply modification.

You would replace

ActiveWorkbook.SaveAs Filename:=varResult, _
FileFormat:=xlWorkbookNormal

With

ActiveWorkbook.SaveCopyAs Filename:=varResult 

One final consideration I would add is that if you save your macro-enabled workbook as a .xlsx (either by SaveAs or the SaveCopyAs) then you will lose the macros, either in your original workbook if you use SaveAs or in the copy that is saved if you use SaveCopyAs. I would consider saving the file as a .xlsm instead, if you need macros to be available.

Answer 2

I prefer to use the shortest code:

    Application.Dialogs(xlDialogSaveAs).Show ("c:\my_folder\")

It's the standard Excel save dialog.

It has several parameters (not named), you may need them:

    Dim strFilename As String: strFilename = "report1"
    Dim strFolder As String: strFolder = "C:\temp\" 'initial directory - NOTE: Only works if file has not yet been saved!
    Dim xlfFileFormat As XlFileFormat: xlfFileFormat = XlFileFormat.xlOpenXMLWorkbook 'or replace by other XlFileFormat
    Dim strPassword As String: 'strPassword = "password" 'The password with which to protect the file - if any
    Dim booBackup As Boolean: 'booBackup = True  '(Whether to create a backup of the file.)
    Dim strWriteReservationPassword As String: 'strWriteReservationPassword = "password2" ' (The write-reservation password of the file.)
    Dim booReadOnlyRecommendation As Boolean: booReadOnlyRecommendation = False '(Whether to recommend to the user that the file be opened in read-only mode.)
    Dim booWorkbookSaved As Boolean ' true if file saved, false if dialog canceled
    If Len(strFolder) > 0 Then ChDir strFolder
    booWorkbookSaved = Application.Dialogs(xlDialogSaveAs).Show(Arg1:=strFilename, Arg2:=xlfFileFormat, Arg3:=strPassword, _
            Arg4:=booBackup, Arg5:=strWriteReservationPassword, Arg6:=booReadOnlyRecommendation)