Access VBA Code Compile error

[gheyman]

I am getting a Compile Error: User-defined type not define. I had a freelancer develop this code for me (I am not smart enough to write this).

Private Sub Command101_Click()
    Dim fd As FileDialog
    Dim ExcelRunning As Boolean
    
    Dim xlApp As Excel.Application
    Dim mywb As Excel.Workbook
    Dim mysheet As Excel.Worksheet
    
On Error GoTo Err_Command101_Click

MsgBox "This will take several minutes"

    ' choose Excel file for import
    Set fd = Application.FileDialog(msoFileDialogFilePicker)
    With fd
        .ButtonName = "Select"
        .AllowMultiSelect = False
        .Filters.Add "Excel files", "*.xls; *.xlsx; *.xlsm", 1

        .Title = "Choose Excel file to import in to Microsoft Access Database"
        .InitialView = msoFileDialogViewDetails
        .Show

        If (.SelectedItems.Count > 0) Then
            Me.txtExcelFilePath = .SelectedItems(1)
        Else
            Exit Sub
        End If
    End With

    Set fd = Nothing
    ' --------------------------------------------------------------------
    
    ExcelRunning = IsExcelRunning()
    If ExcelRunning Then
        Set xlApp = GetObject(, "Excel.Application")
    Else
        Set xlApp = CreateObject("Excel.Application")
    End If
    ' --------------------------------------------------------------------

    ' open the book
    Set mywb = xlApp.Workbooks.Open(Me.txtExcelFilePath)
    
    For Each mysheet In mywb.Worksheets
        txtRowSource = txtRowSource & mysheet.Name & ";"
    Next
    
    Me.txtExcelSheets.RowSource = txtRowSource
    Me.txtExcelSheets.Requery
    ' --------------------------------------------------------------------
    
    ' close the book without the changes saved
    mywb.Close False
    Set mywb = Nothing
            
    If Not ExcelRunning Then
        xlApp.Quit
        Set xlApp = Nothing
    End If
    
    MsgBox "Excel file for import successfully selected!"
        
Exit_Command101_Click:
    Exit Sub

Err_Command101_Click:
    
    If Not wb Is Nothing Then
        mywb.Close False
        Set mywb = Nothing
    End If
            
    If ExcelRunning Then
        xlApp.Quit
        Set xlApp = Nothing
    End If
    
    MsgBox Err.Description
    Resume Exit_Command101_Click
End Sub

doesn't seem to like Dim fd As FileDialog

 

[Norie]

FileDialog should be fine in Access VBA.

When you go to Tools>References do you see anything marked as MISSING?

PS What's the code meant to do?

 

[xenou]

You should set a reference to the latest Microsoft Office XX.0 Object Library (mine is Microsoft Office 15.0 Object Library).

see:
https://docs.microsoft.com/en-us/office/vba/api/access.application.filedialog

I would suggest you convert all of this to late binding once it is tested. That way you can be more confident it will not break when someone upgrades to a new version of Excel or Office.

 

[welshgasman]

In respect of late binding this link might help ?

https://rtmccormick.com/2015/11/20/solved-late-binding-file-dialog-vba-example/