Import an Excel Sheet with File Dialog in Access into the table

[Barbara1999]

Hi!
I have a splitform where I want to have the button to allow the user to choose and import the file into the 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 also set the references to Microsoft Office 16.0 Object Library. When I tried using this button there was this error: Method 'FileDialog' of object '_Application' failed. I'm pretty much new to VBA, so I don't know how to fix it.

[NeoPa]

Hi Barbara. Welcome to Bytes.com.

I'm just going to check for you, but the addition of the library reference rather implies that FileDialog() is NOT a Property of the Application object, but rather of the MS Office object - but I'm going to check anyway.

HAH!!! Shows what I know :-(

FileDialog() IS a Property of the Application object. You need the Microsoft Office 16.0 Object Library (or equivalent) as the definition of the Property is found there. I can't see what the problem is here. There are things you need to do to get this right but it seems clear you've done them all properly.

I think maybe we have to go back to first principles. Does the project compile? Before we can rely on any other messages we need to ensure that all compile errors are resolved first.

Please start by commenting out this code and compiling the project as many times as you need for it to work fully. At that point come back and uncomment these lines and then we can see where we're really at reliably.

Nice work for a newbie BTW ;-) It doesn't look like newbie code.

[isladogs]

Actually I believe FileDialog is part of the default Microsoft Access 16.0 Object Library so you shouldn't need to add the Office library for this.
But msoFileDialogOpen isn't supported in that library.
Instead use msoFileDialogFilePicker
See Application.File Dialog property Access

[ADezii]

1. Your Base Code is fine, but needs adjustments.
2. You 'will' need a Reference to the Microsoft Office XX.X Object Library.
3. You must apply the appropriate Method depending on the Filter.
4. Hopefully, the following Code will illustrate these points.

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
     'Analyze the File's Extension and use the appropriate Method
     Select Case Mid(.SelectedItems(1), InStrRev(.SelectedItems(1), "."))
       Case ".xlsx", ".xls"
         DoCmd.TransferSpreadsheet acImport, , "Data", .SelectedItems(1), True, "Data!"
       Case ".accdb"
         DoCmd.TransferDatabase , "Microsoft Access", .SelectedItems(1), acTable, _
                                  "tblLogins", "tblLogins"
       Case Else
         'will never happen
     End Select
   End If
End With
 

@NeoPa:
Nice to see an old friend (not speaking literally of course!)

[isladogs]

Apologies. I was wrong.
You DO need the Microsoft Office XX.X Object Library

I normally use code like this with the msoFileDialogFilePicker option.
To my mind there is no point including accdb files in the file type options if you are then going to ignore them using .FilterIndex = 1:

Private Sub Command287_Click()
 
Dim strFilePath As String
 
' Set options for the dialog box.
    With Application.FileDialog(msoFileDialogFilePicker)
        .Title = "Locate the spreadsheet folder and click on 'Open'"
 
    ' Clear out the current filters, and add our own.
        .Filters.Clear
        .Filters.Add "Spreadsheet files", "*.xlsx;*.xls"
 
    ' Set the start folder
        .InitialFileName = "C:\" 'change to whichever folder you want
 
    ' Call the Open dialog routine.
        .Show
 
    ' Return the path and file name.
        strFilePath = .SelectedItems(1)
 
        DoCmd.TransferSpreadsheet acImport, , "Data", strFilePath, True, "Data!"
 
    End With
 
End Sub
 

The strFilePath variable could also be omitted of course.
I often use that to save the path in a textbox

[SioSio]

Barbara1999 wrote
>I also set the references to Microsoft Office 16.0 Object Library.

If you get an error even if it is set to refer to the library, it is possible that the library referenced in the environment where the macro was developed does not exist in the environment in which it is running, or the path is different.
Check if there are any "unreferenced libraries" in the [Browse] dialog.
If the reference is marked as "MISSING", uncheck it and reset it.

[NeoPa]

Hi Barbara.

Notwithstanding other advice and suggestions, I still feel the issue on your system is yet to be determined. How you code your usage of these objects doesn't really start to become an issue until you can get the system to recognise the classes & objects you're using. I see nothing fundamentally wrong with your code as you already have it. It may not be the best code in the world (though frankly it doesn't look bad at all), but it should certainly work according to what you've shared with us.

As such, I suspect my earlier advice is still your best way forward. You have the reference you need so why doesn't it behave as one could reasonably expect it to? That's for you to find out by getting it to compile first and then building from there. See my earlier post (#2).

ADezii (Hi my friend :-) ) & IslaDogs certainly have experience writing good code so feel free to pick up tips from them once you get the fundamentals working.

[NeoPa]

I don't mean to leave SioSio out. Their advice is also good. It's always worth checking that all the references added are actually working using the approach they suggest.

Probably do that first. If it makes the problem go away then great. Otherwise - back to instructions from post #2.