Logicwurks, LLC

Save A Workbook Using msoFileDialogSaveAs

Using the more current msofileDialogSaveAs procedure is demonstrated in the code below. Pay particular attention to the notes in the code.
For all the possible formats in the Workbook.SaveAs FileFormat:=, see this article:
FileFormat:= Specifications
Program Code

Option Explicit

' ************************************************************************************
' Application.FileDialog(msoFileDialogSaveAs) Does Not Allow .Filters.Add
' That is a limitation of this module
'
' The .FilterIndex must be set to the proper value to reference the correct
' type of Excel file to be saved
'
' To see the filter index from 1 to n, the following routine allows the user
' to select the correct FilterIndex for the .FilterIndex parameter
'
' This procedure pushes the results onto the active sheet in n rows
' ************************************************************************************
' *** Use this URL to locate all the file format output specification names ***
' https://docs.microsoft.com/en-us/office/vba/api/Excel.XlFileFormat
' ************************************************************************************

Sub ShowFilterIndexValues()
    Dim i As Integer
    ActiveSheet.Cells.Clear
    With Application.FileDialog(msoFileDialogSaveAs)
        For i = 1 To .Filters.Count
            Range("A1") = "Filter Index"
            Range("B1") = "Excel File Type"
            Range("A2").Offset(i - 1, 0).Value = i
            Range("A2").Offset(i - 1, 1).Value = .Filters(i).Description
        Next
    End With
End Sub

' ************************************************************************************
' This is an alternative method to the above display of the .FilterIndex
' ************************************************************************************
Sub ShowFilterIndexValuesAlternate()
    Dim i As Integer
    ActiveSheet.Cells.Clear
    With Application.FileDialog(msoFileDialogSaveAs)
        For i = 1 To .Filters.Count
            Range("A1") = "Filter Index"
            Range("B1") = "Excel File Type"
            Cells(i + 1, "A") = i
            Cells(i + 1, "B") = .Filters(i).Description
        Next
    End With
End Sub

' ************************************************************************************
' This demonstrates how to save a macro enabled xlsm file using .SaveAs in
' conjunction with the msoFileDialogSaveAs procedure
'
' The .Show command presents the file dialog box and the user navigates to the
' target directory and assigns a file name
'
' The .Show command doesn't actually save the file. It just presents the save file
' dialog box and records the user's path and file selection in .SelectedItems(1)
'
' FileFormat:=xlOpenXMLWorkbookMacroEnabled is required for the .SaveAs to
' work correctly
' ************************************************************************************
' *** Use this URL to locate all the file format output specification names ***
' https://docs.microsoft.com/en-us/office/vba/api/Excel.XlFileFormat
' ************************************************************************************
Sub SaveTestFromHans()
    With Application.FileDialog(msoFileDialogSaveAs)
        .FilterIndex = 2
        If .Show Then
            ActiveWorkbook.SaveAs Filename:=.SelectedItems(1), _
                FileFormat:=xlOpenXMLWorkbookMacroEnabled
        Else
            MsgBox ("No File Selected")
        End If
    End With
End Sub

' ************************************************************************************
' This is an alternate of the above method
' ************************************************************************************
Sub SaveTestFromHansAlternate()
    With Application.FileDialog(msoFileDialogSaveAs)
        .FilterIndex = 2
        .Show
        If .SelectedItems.Count > 0 Then
'           MsgBox (.SelectedItems(1))
            ActiveWorkbook.SaveAs Filename:=.SelectedItems(1), _
                FileFormat:=xlOpenXMLWorkbookMacroEnabled
        Else
            MsgBox ("No File Selected")
        End If
    End With
End Sub

' ************************************************************************************
' This demonstrates how to save a macro enabled xlsm file using the .Execute
' command and a pre-determined file name.
'
' The .Show command presents the file dialog box but doesn't save the file
' This format always starts at the Documents directory.
' Any path in the.InitialFileName is ignored
' The file extension is chosen automatically by the FilterIndex type
' The .Execute actually saves the file
' ************************************************************************************
Sub SaveTestUsingExecute()
With Application.FileDialog(msoFileDialogSaveAs)
    .FilterIndex = 2
    .InitialFileName = "MyRichFileTest"
    .Show
    If .SelectedItems.Count > 0 Then
        .Execute
    Else
        MsgBox ("No File Selected")
    End If
End With

End Sub

' ************************************************************************************
' This demonstrates how to save a macro enabled xlsm file using the .Execute
' command and a pre-determined file name and a title for the dialog box
'
' The .Show command presents the file dialog box but doesn't save the file
' This format always starts at the Documents directory.
' Any path in the.InitialFileName is ignored
' The file extension is chosen automatically by the FilterIndex type
' The .Execute actually saves the file
' ************************************************************************************
Sub SaveTestUsingExecuteV2()
With Application.FileDialog(msoFileDialogSaveAs)
    .Title = "Select The Destination Directory"
    .FilterIndex = 2
    .InitialFileName = "MyRichFileTest"
    .Show
    If .SelectedItems.Count > 0 Then
        .Execute
    Else
        MsgBox ("No File Selected")
    End If
End With

End Sub
Links To Excel Code Examples

Links To Access Code Examples

Save A Workbook Using msoFileDialogSaveAs

Program Code
