Here's what I cobbled together using OP's code. Note that one could either use a For Next loop to loop over the single item collection, or one could simply refer to the collection index if there will only ever be one item due to the multi select being disabled. I also cannot vouch for the accuracy of the posted name of the range that follows the True parameter - it looks odd to me. In one way the code looks as long as the first example, but that's because I added an error handler. However, I removed
- the call to the other sub
- that sub
- the loop
- the automation and variant references
Code:
Option Compare Database
Option Explicit
Private Sub cmdImportExcel()
Dim fDialog As FileDialog
Dim varFile As Variant
Dim strFileName As String
On Error GoTo errHandler
Set fDialog = Application.FileDialog(msoFileDialogFilePicker)
With fDialog
.AllowMultiSelect = False
.Filters.Clear
.Filters.Add "Excel Spreadsheets", "*.xls, *.xlsx, *.xlsm, *.xlsb"
If .Show Then
strFileName = .SelectedItems(1)
MsgBox strFileName 'disable/remove this line when OK
'DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel12Xml, "tblData", strFileName, True, "TestDat$A1:C3"
'shorter version, or could use line continuation characters
DoCmd.TransferSpreadsheet 0, 9, "tblData", strFileName, True, "TestDat$A1:C3"
Else
MsgBox "Import selection was cancelled."
Exit Sub
End If
End With
MsgBox "success message goes here"
exitHere:
Set fDialog = Nothing
Exit Sub
errHandler:
MsgBox "Error " & Err.Number & ": " & Err.Description
Resume exitHere
End Sub
Seems that a new wrinkle has been raised - how to know if zero rows were imported. The only way I know of is to count the table records before and after.