put this in a module,
Code:
Public Function UserPick1File(ByVal pvFilter, Optional pvPath)
Dim strTable As String
Dim strFilePath As String
Dim sDialogMsg As String, sDecr As String, sExt As String
'CONST msoFileDialogFilePicker = 3
'CONST msoFileDialogSaveAs = 2
'CONST msoFileDialogViewList = 1
If IsMissing(pvPath) Then pvPath = "c:\temp\"
With Application.FileDialog(3) 'MUST ADD REFERENCE : Microsoft Office 11.0 Object Library
.AllowMultiSelect = True
.Title = sDialogMsg ' "Locate a file to Import"
.ButtonName = "Import"
.Filters.Clear
'.Filters.Add "Access Files", "*.accdb;*.mdb"
.Filters.Add "Excel Files", "*.xlsx"
.Filters.Add "All Files", "*.*"
.InitialFileName = pvPath
.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
usage:
Dim sFile As String
sFile = UserPick1File("c:") 'chg default folder
if sFile <>"" then docmd.TransferSpreadsheet acImport ,acSpreadsheetTypeExcel12 ,sTable, sFile, true