I tried this one which imports the file from the host DBF file, which I export to excel.
Private Sub RunImportData()
Dim sFileDir As String
Dim fs
Dim fsFolder
Dim fsFile
Dim sFileName
Dim db As Database
Dim sSQL As String
Dim sSourceFile As String
Set db = CurrentDb
Set fs = CreateObject("Scripting.filesystemobject")
sFileDir = CurrentProject.Path & "\"
Set fsFolder = fs.getfolder(sFileDir)
For Each fsFile In fsFolder.filessFileName = fsFile.Name
If Len(sFileName) = 10 Then
If IsNumeric(Left(sFileName, 6)) Then
If Right(sFileName, 4) = ".dbf" Then
Debug.Print sFileNameLink_DBF_Table "Dbase 5.0", "A:\Keith\Reports\HEADCOUNT\DBASE FILES", Left(sFileName, 6) & Right(sFileName, 4), Left (sFileName, 6)
DoCmd.TransferDatabase , "dBase 5.0", sFileDir, acTable, sFileName, Left(sFileName, 6)
sSQL = "INSERT INTO tblImportedData ( EmpNo, CCenter, Div1, Div2, Div3, Grade, OriginalFile )"
sSQL = sSQL & "SELECT EMPNO, CCENTER, DIV1, DIV2, DIV3, Grade '" & Left(sFileName, 6) & "' AS Expr1 FROM " & Left(sFileName, 6)
Debug.Print sSQL
db.Execute sSQL
sSQL = "DROP TABLE " & Left(sFileName, 6)
Debug.Print sSQL
db.Execute sSQL
End If
End If
End If
Next
Set fs = Nothing
Set db = Nothing
End Sub