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.