Still trying to resolve the issue. Further guidance would really be appreciated.
Here is the current code:
Code:
Private Sub btnImport_Click()
Dim fDialog As FileDialog
Dim strFileName As String
Dim db As DAO.Database
Dim sqlStr As String
Dim varFile As Variant
Me.FileList.RowSource = ""
On Error GoTo errHandler
Set fDialog = Application.FileDialog(msoFileDialogFilePicker)
Set db = CurrentDb
With fDialog
.AllowMultiSelect = True
.Title = "Please select one or more files"
.Filters.Clear
.Filters.Add "Excel Spreadsheets", "*.xls, *.xlsx, *.xlsm, *.xlsb"
If .Show = True Then
For Each varFile In .SelectedItems
Me.FileList.AddItem varFile
strFileName = .SelectedItems(1)
sqlStr = "INSERT INTO tblTransaction (YearMo, Entity, Acct, ActDKK, FcDKK, ActLocCur, FcLocCur, Comment) " & _
"SELECT * FROM (SELECT YearMo, Entity, Acct, ActDKK, FcDKK, ActLocCur, FcLocCur, Replace([Comment1],Chr(10),Chr(13) & Chr(10)) " & _
"As Comment FROM [DB$] AS xlData IN '" & strFileName & "'[Excel 12.0;HDR=yes;IMEX=2;ACCDB=Yes])"
db.Execute sqlStr
MsgBox strFileName & vbCrLf & db.RecordsAffected & " records imported"
Next
'MsgBox strFileName & vbCrLf & db.RecordsAffected & " records imported"
Else
MsgBox "Import selection was cancelled."
Exit Sub
End If
End With
exitHere:
Set fDialog = Nothing
Set db = Nothing
Exit Sub
errHandler:
MsgBox "Error " & Err.Number & ": " & Err.Description
Resume exitHere
End Sub
I also know that the strfilename is not updating corresponding to the entry in the listbox, but I couldn't find specific guidance helping me to adjust the current code accordingly.
As long as I just select one file, the code works and the content is imported, however selecting multiple files ends up like shown below: