CODEWITHSUNDEEP
vbams-access
A Hughes
A Hughes

Reputation: 265

Import Excel files into Access whilst Looping

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

Upvotes: 1

Views: 1906

Answers (2)

Sasa
Sasa

Reputation: 21

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

Upvotes: 1

Cayucodies
Cayucodies

Reputation: 1

I would do: Dir$() DoWhile Val(Mid(filename, 7, 2))<48 Import the file Dir$() Loop

Upvotes: 0

Related Questions