Import an Excel Sheet with File Dialog

[rod4]

hi guys,

i had made a simple import menu for xlsx, xls and accdb files from the current project folder with fixed name. see below the xlsx_data_import.

Private Sub xlsx_Data_Import_Click()
 
On Error Goto ErrorHandler
 
    DoCmd.TransferSpreadsheet acImport, , "Data", Application.CurrentProject.Path & "\Data_Backup.xlsx", True, "Data!"
 
    Exit Sub
 
ErrorHandler:
    MsgBox "No such file exists in this directory!"
 
End Sub
 

now i would like to add a file dialog, so i can choose which file i actually wanna import. i was searching in internet but there attempts were so complicated and nothing worked for me.

can somebody help with a simple file dialog to import 1 file only?

[ADezii]

The following should work nicely for you. Be sure to set a Reference to the Microsoft Office XX.X Object Library.

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

[rod4]

THANKS a lot!!!! it works like a charme!

[ADezii]

You are quite welcome.