Select Folder and Filename to Save a new file for 32-bit and 64-bit MS-Access-2016

Question

I am trying to get a full path and filename using the file dialog from MS Access VBA. In my MS-Access project I want to be able to save data into a user selectable folder and filename. To select the folder and filename MS-Access provides the FileDialog() function.

--> FileDialog()

Set fDialog = Application.FileDialog(msoFileDialogSaveAs)

But the latest versions of MS-Access (especially the 64-bit version) the FileDialog function does not support the msoFileDialogSaveAs option any more.

Is there any way to get a folder and filename from the user using an API function like the FileDialog Object for the latest version of MS-Access?

Gustav Gustav · Accepted Answer · 2021-05-01T09:38:47

There is support, but you need a reference to Microsoft Office 16.0 Object Library:

Enums:

Example:

Public Function FileSaveDialog( _
    ByVal Filter As String, _
    ByVal Extension As String) _
    As String

    Dim FilterIndex As Long
    Dim FileName    As String
    
    With Application.FileDialog(msoFileDialogSaveAs)  ' 2
        For FilterIndex = 1 To .Filters.Count
            If (InStr(LCase(.Filters(FilterIndex).Description), LCase(Filter)) > 0) And _
                (LCase(.Filters(FilterIndex).Extensions) = LCase(Extension)) Then
                .FilterIndex = FilterIndex
                Exit For
            End If
        Next

        If .Show Then
            FileName = .SelectedItems(.SelectedItems.Count)
        End If
    End With
    
    FileSaveDialog = FileName

End Function

Select Folder and Filename to Save a new file for 32-bit and 64-bit MS-Access-2016

1 Answers