I am trying to write a VBA Module that will look in a directory for Excel files with a specific file prefix, and then import specified cells into a temp table. Once it processes the first file, it copies the file to another folder, and then kills the file. However, when I run my current code, it goes through the loop the first time, but fails on the second attempt, because it is still looking for the first file it came across even though it has been moved. The code is pasted below, and it's probably something easy I am missing. I removed some code that didn't really matter.
Code:
Sub Count()
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")
intCount = 0
Do While Len(strFile) > 0
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 to be imported
End If
Next
xlWrkBk.Close SaveChanges:=False, filename:=strPath & strFile
Set xlWrkBk = Nothing
FileCopy strPath & strFile, strMvPath & Format(Now(), "YYYY_MM_DD_HH_MM") & "_IQS.xls"
Kill strPath & strFile
Loop
rs.Close
End Sub