0

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?

PeterF
  • 3
  • 1

1 Answers1

0

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

enter image description here

Enums:

enter image description here

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
Gustav
  • 53,498
  • 7
  • 29
  • 55
  • Hi Gustav, many thanks for your response. Can you please alos let me know, how Microsoft Office 16.0 Object Library solved this problem. Which function does solve this problem and how do I use it. Do you perhaps have any example code? – PeterF May 01 '21 at 20:02
  • This library holds the enums/constants (i.e. `msoFileDialogSaveAs`) - see picture, please. – Gustav May 01 '21 at 21:14
  • When I execute the FileDialog with option msoFileDialogSaveAs then I get the following message: Microsoft Access: FileDialog: Object does not support this property or method – PeterF May 02 '21 at 07:48
  • I've added an example to the answer. – Gustav May 02 '21 at 09:16