Remember to add via VBE menu: tools, references : Microsoft Office 11.0 Object Library
Code:
Public Sub ImportXLfile()
Dim vFil, vTargT
Dim i As Integer
Dim sSql As String
Dim fso
Dim oFolder, oFile
On Error GoTo errImp
vFil = UserPick1File(".xls")
If vFil <> "" Then
'OR import the file TO TARGET TABLE
docmd.TransferSpreadsheet acimport, "tTABLE", vFil, True
'OR link the file for import
'docmd.TransferSpreadsheet acLink, "xlFile", vFil, True
'docmd.openquery "qaImportData"
End If
endit:
Exit Sub
errImp:
MsgBox Err.Description, vbCritical, "ImportAllFilesInDir():" & Err
Resume endit
Resume
End Sub
Public Function UserPick1File(ByVal psFilter As String, Optional pvPath)
Dim strTable As String
Dim strFilePath As String
Dim sDialog As String, sDecr As String, sExt As String
With Application.FileDialog(msoFileDialogFilePicker) '<----!! MUST ADD REFERENCE : Microsoft Office 11.0 Object Library
.AllowMultiSelect = False
.Title = "Locate a file to Import"
.ButtonName = "Import"
.Filters.Clear
.Filters.Add "All Files", "*.*"
.InitialFileName = "c:\"
.InitialView = msoFileDialogViewList 'msoFileDialogViewThumbnail
If .Show = 0 Then
'There is a problem
Exit Function
End If
'Save the first file selected
UserPick1File = Trim(.SelectedItems(1))
End With
End Function