1

Thanks to everyone for their help. I got the code to read in a file prefix. I want it to loop through and import all excel workbooks that start with Import* . However, when the code below loops through it keeps asking for the file name that it just processed, even though it has already been closed, copied, and removed from it's current directory. I though the Set statement to Nothing would solve it, but it didn't. How can I get Access to drop the previous file name and move on to the next?

Sub Count()

Dim xl          As Excel.Application
Dim xlwrksht    As Excel.Worksheet
Dim xlWrkBk     As Excel.Workbook
Dim nIndex      As Integer
Dim strMvPath   As String
Dim mvPath      As String
Dim strFile     As String


Set rs = CurrentDb.OpenRecordset("Import_TMP", dbOpenTable)



strPath = "C:\Users\...\Documents\Blah\"
strMvPath = "C:\Users\...\Documents\Blah\Completed\"
strFile = Dir(strPath & "Import*.xls")

Do While strFile <> ""

    Set xlWrkBk = Workbooks.Open(filename:=strPath & strFile)

    For nIndex = 1 To xlWrkBk.Worksheets.Count

     If xlWrkBk.Worksheets(nIndex).Name Like "Component*" Or xlWrkBk.Worksheets(nIndex).Name Like "Import*" Or xlWrkBk.Worksheets(nIndex).Name Like "Child*" Or xlWrkBk.Worksheets(nIndex).Name Like "R*" Then
            .... data fields to import
    End If

Next
xlWrkBk.Close SaveChanges:=False
Set xlWrkBk = Nothing

FileCopy strPath & strFile, strMvPath & Format(Now(), "YYYY_MM_DD_HH_MM") & "_IQS.xls"
Kill strPath & strFile



Loop

rs.Close

End Sub

My VBA is sub par, and it's probably something simple to that I am missing.

Billiford
  • 11
  • 2

0 Answers0