The "For...Next" loop is for when "AllowMultiSelect" is TRUE.
Consider this:
Code:
Dim f As FileDialog
Dim strTable As String
Dim strPathFile As String
Dim blnHasFieldNames As Boolean
Dim StartPos As Integer
Dim LenOfName As Integer
strPathFile = vbNullString
strTable = vbNullString
blnHasFieldNames = True
'must nave a reference to Microsoft Office XX.0 Object Library (for A2010, XX = 14)
Set f = Application.FileDialog(msoFileDialogFilePicker)
With f
.Title = "Please Select the Excel File To Import"
.AllowMultiSelect = False
.InitialFileName = "some folder path"
.InitialView = msoFileDialogViewList
.Filters.Clear
.Filters.Add "Excel Files", "*.xlsx", 1
If .Show Then
strPathFile = .SelectedItems(1)
End If
End With
Set f = Nothing
'is file selected???
If Len(Trim(strPathFile & "")) > 0 Then
' get table name
StartPos = InStrRev(strPathFile, "\") + 1
LenOfName = InStr(strPathFile, ".") - StartPos
strTable = Mid(strPathFile, StartPos, LenOfName)
' MsgBox strTable
'import
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, strTable, strPathFile, blnHasFieldNames
MsgBox "Imported!"
End If
Note: Should add error handling code!!