ms-accessvbaoffice-2016

'FileDialog' type is not defined in MS Access


In Access 2016, I wish to display the File Open dialog, allowing users to select a CSV file to import. However, an error is being generated in relation to the line Dim FD as Office.FileDialog -

Compile error: User-defined type not defined

The below code has been copied (and edited slightly) from the example posted on MSDN. This example is listed as relevant for Office 2013 and later, yet the very first comment in the code (in relation to the variable type Office.FileDialog) seems to contridict this -

Requires reference to Microsoft Office 11.0 Object Library.

Surely for Office 2013 you'd need to refernece the MS Office 15 Object Library, and then the respective version library for future versions, such as 2016?

But regardless, in Access 2016 there is no refernece to the Microsoft Office 11.0 Object Library. There is however a reference to the Microsoft Access 16.0 Object Library, which is included.

How can I get the File Open dialog to show?

Function SelectFile(Optional ByVal title As String = "Please select a file", _
                    Optional ByVal allowMultiSelect As Boolean = False) As Variant

    Dim FD As Office.FileDialog
    Dim file As Variant

    Set FD = Application.FileDialog(msoFileDialogFilePicker)

    With FD

        .title = "Please select a file"         ' Add the dialog title
        .allowMultiSelect = allowMultiSelect    ' Set whether or not to allow multiple file selection

        .filters.Clear                      ' Clear any existing filters
        .filters.Add "CSV Files", "*.csv"   ' Add new filters

        '**
         ' Show the dialog to the user
         '*
        If .Show = True Then

            For Each file In .selectedItems  ' Grab the path/name of the selected file
                SelectFile = file
            Next

        Else
            SelectFile False
        End If

   End With

   Set FD = Nothing    ' Clean up the FD variable

End Function

Here are my currently selected references -

Selected references

And here are the available MS Office referencs (no refernece to Microsoft Office 16.0 Object Library) -

Available MS Office references


Solution

  • I don't know why Microsoft Office [version] Object Library is not displayed among the available references. However, you don't need it if you switch to late binding.

    Const msoFileDialogFilePicker As Long = 3
    'Dim FD As Office.FileDialog
    Dim FD As Object
    Dim file As Variant
    Set FD = Application.FileDialog(msoFileDialogFilePicker)
    

    Later on, you'll need to decide what to do here ...

    For Each file In .selectedItems  ' Grab the path/name of the selected file
        SelectFile = file
    Next
    

    When you run that code with AllowMultiSelect = True, and select multiple files, SelectFile will contain only the last of them.