Looping through Files - Access holding on to first file name (1 Viewer)

[billfold]

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.

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

 

[Gasman]

You are only getting the first file and that is outside the loop.

You need to get the next file inside the loop

strFile = dir()

I've no idea as to how it performs when you are deleting the previous file.?

 

[vbaInet]

Following Gasman's advice, subsequent calls to the Dir() function gets the next matching file path.

By the way instead of copying the file across and deleting it, you could move the file... same process as cut and paste.
https://msdn.microsoft.com/en-us/library/office/gg251462.aspx

 

[billfold]

Thanks for the help everyone! That works, but now I get a run-time error after the last file is moved, because it is still looking for a file when the directory is empty. It fails at the 'SET' line in the code below. I tried the ' Set xlWrkBk = Nothing ' statement in a couple of spots, but that did't do anything.

Do While strFile <> ""
        strFile = Dir(strPath & "Import*.xls")
        
        Set xlWrkBk = Workbooks.Open(filename:=strPath & strFile)

 

[vbaInet]

Remember I mentioned "Subsequent calls". You've already called it once before the loop so where do you think the next call to Dir() should go? Have a think.

 

[billfold]

I got it, just had to move that call to the end of the loop. Thank you making me think on that one!

 

[vbaInet]

Very good!

Welcome to AWF by the way.