Import an Excel Sheet with File Dialog in Access

Question

I have a splitform where I want a button to choose and import a file into a table.

I have something like this:

Private Sub Command287_Click()

With Application.FileDialog(msoFileDialogOpen)
    .Filters.Clear
    .Filters.Add "Excel Files", "*.xlsx,*.xls"
    .Filters.Add "Access Databases", "*.accdb"
    .FilterIndex = 1      'Excel Files
 
    .AllowMultiSelect = False
    .ButtonName = "Open"
    .InitialFileName = "C:\"
    .Title = "Select a File to Import"
    .InitialView = msoFileDialogViewDetails
 
    If .Show Then
        DoCmd.TransferSpreadsheet acImport, , "Data", .SelectedItems(1), True, "Data!"
    End If
End With
End Sub

I set the reference to Microsoft Office 16.0 Object Library.

When I tried using this button there was this error:

Method 'FileDialog' of object '_Application' failed.

score 1 · Accepted Answer · answered Jun 22 '21 at 02:11

I copied Albert Kallal's answer found here: It does not require any reference.

Private Sub Command287_Click()
  Dim f As Object
  Set f = Application.FileDialog(3)
  f.filters.Clear
  f.filters.Add "Excel Files", "*.xlsx,*.xls"
  f.Title = "Select a file to Import"
  f.InitialFileName = "C:\"
  If f.Show Then
    DoCmd.TransferSpreadsheet acImport, , "Data", f.selectedItems(1), True, "Data"
  End If
End Sub

Import an Excel Sheet with File Dialog in Access

1 Answers1