Import data from a specfic Worksheet from Excel

[daredan]

Alright, sorry I'm a bit slow, but I'm trying my best.

I have now assigned 'Value List' to the row source type for the 'FileList' Listbox.


When I press the 'Import' button, then on a test of two files only one is imported.




So somehow it doesn't loop to the next file for import.

[daredan]

Still trying to resolve the issue. Further guidance would really be appreciated.

Here is the current 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:

[Micron]

this
.SelectedItems(1) ensures that only 1 item will ever be used in the code that follows so the sql should repeat n times with the same file. I didn't try to figure out why the query edit only occurs the first time though - the answer is probably buried in the code or sql. Try strFileName = .SelectedItems

[daredan]

Thanks Micron. I also tried playing around with this, as the 1 seemed too static for my needs.

strFileName = .SelectedItems

results in


I tried some variations using the same three files as in post #32:

strFileName = .SelectedItems(1)

results as what I described in my post #32, 3 x PHO_2206.xlsb is being triggered in the related MsgBox

I have tried now

strFileName = .SelectedItems(2)

will trigger 3 x REG_CLU_NAM_2206.xlsb

and

strFileName = .SelectedItems(3)

will trigger 3 x REG_CLU_PACIF_2206.xlsb

Any number < 1 or > 3 will result in an Error -2147024809: The expression you entered has an invalid reference to the Parent property.

[Micron]

Ooops. I think that should be strFileName = varFile?

[daredan]

Next drink is on me. And seeing the solution now, it seems so obvious.


Thanks a lot for your help!

[Micron]

You're welcome. Glad you got a solution.