'FileDialog' type is not defined in MS Acc

问题:

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 -

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

回答1:

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.

回答2:

For anyone having the same issue, you need to select the 'Microsoft Office 16.0 Object Library' which is not the same as 'Microsoft Access 16.0 Object Library'

回答3:

In office 2016 the "Microsoft Office 16.0 Object Library" is missing from the references window. The main issue I was having was determining the location and name of the dll file.

Finally I found the full file path, which I imagine will be the same for any user because I believe it is a default directory:

"C:\Program Files\Microsoft Office\Root\VFS\ProgramFilesCommonX86\Microsoft Shared\OFFICE16\MSO.dll"

I have spent 1 hour to find this path just so I can create File Dialogs!