Import Excel files into Access whilst Looping

Question

I am comfortable with using VBA to import excel sheets into access, and to loop through a given folder to bring back everything in there. However, I want to loop through a folder and only import a selection of the files. Can someone help? Each file is called REPORT1 etc and runs to REPORT67. I only want to pick 1-47.

Code below works fine, but this just copies everything in from the specified location.

Sub Sample2()
Const cstrFolder As String = "F:\TCB_HR_KPI\Data View\"
Dim strFile As String
Dim i As Long

strFile = Dir(cstrFolder & "*.xls")
If Len(strFile) = 0 Then
    MsgBox "No Files Found"
Else
    Do While Len(strFile) > 0
        Debug.Print cstrFolder & strFile

        DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, _
        strFile, cstrFolder & strFile, True

        i = i + 1
        strFile = Dir()
    Loop
    MsgBox i & " Files are imported"
End If
End Sub

Questions asking for code must demonstrate a minimal understanding of the problem being solved. Include attempted solutions, why they didn't work, and the expected results. See also: Stack Overflow question checklist — Siddharth Rout
yes and I have put my original code above!!!! I now need help tweeking it. thanks for your help anyway. — A Hughes

Sasa Sasa · Accepted Answer · 2019-08-05T07:20:06

I just replaced your code, "Do While Len(strFile) > 0" into "Do While Int(Mid(strFile, 7)) < 48", hope it helps.

Sub Sample2()
Const cstrFolder As String = "F:\TCB_HR_KPI\Data View\"
Dim strFile As String
Dim i As Long

strFile = Dir(cstrFolder & "*.xls")
If Len(strFile) = 0 Then
    MsgBox "No Files Found"
Else
    Do While Int(Mid(strFile, 7)) < 48
        Debug.Print cstrFolder & strFile

        DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, _
        strFile, cstrFolder & strFile, True

        i = i + 1
        strFile = Dir()
    Loop
    MsgBox i & " Files are imported"
End If
End Sub

Import Excel files into Access whilst Looping

2 Answers