Problem with nested DIR functions

[lasvegasbuffet]

I'm trying to use a nested Do Loop but I get the error Run-time error ‘5’: Invalid procedure call or argument on line myFile1 = Dir

Sub LoopAllExcelFilesInFolder()
    Dim wb1 As Workbook
    Dim wb2 As Workbook
    Dim myPath1 As String
    Dim myPath2 As String
    Dim myFile1 As String
    Dim myFile2 As String
    Dim myExtension1 As String
    Dim myExtension2 As String
        
    Application.ScreenUpdating = False
    Application.EnableEvents = False
    Application.Calculation = xlCalculationManual
    
    myPath1 = "C:\Cost Report\R&S Error Report\Numerator Overview\"
    myPath2 = "C:\Cost Report\R&S Error Report\Numerator Overview Missing Par Con\"
    
    myExtension1 = "*.xls*"
    myExtension2 = "*.xls*"
    
    myFile1 = Dir(myPath1 & myExtension1)
    myFile2 = Dir(myPath2 & myExtension2)
    
    Do While myFile1 <> ""
        Set wb1 = Workbooks.Open(fileName:=myPath1 & myFile1)
        DoEvents
        msgBox ActiveWorkbook.Name
        Do While myFile2 <> ""
            Set wb2 = Workbooks.Open(fileName:=myPath2 & myFile2)
            DoEvents
            msgBox ActiveWorkbook.Name
            wb2.Close SaveChanges:=True
            DoEvents
            myFile2 = Dir
        Loop
        wb1.Close SaveChanges:=True
        DoEvents
        myFile1 = Dir
    Loop
    
    msgBox "Task Complete!"
    
    Application.EnableEvents = True
    Application.Calculation = xlCalculationAutomatic
    Application.ScreenUpdating = True
End Sub

This code will be for more than just getting file names in message boxes, that's just there to test functionality. If I can get this error solved then I'll be able to go further. Thanks in advance!

 

[Jeffrey Mahoney]

So this may be the problem.
myFile1 = Dir(myPath1 & myExtension1) < Starts a new DIR #1
myFile2 = Dir(myPath2 & myExtension2) < Starts a new DIR #2

myFile2 Dir < This is a continuation of the second DIR because it was the last
myFile1 Dir < This is also a continuation of the second DIR, but in this case there are no more files based on the wildcard rule and that you already processed those.

You're going to have to restructure this. Are you trying to get all the files in a folder and subfolders?

 

[John_w]

You can't have nested Dir loops because the inner Dir loses the context established by the outer Dir. The solution is to move the outer Dir loop to a separate loop and store the files found by that loop in an array or a collection. Then loop through the array or collection, with a nested loop for the inner Dir loop. Like this:

Sub Loop_Files_2_Folders()

    Dim path1 As String, path2 As String
    Dim path1files As Collection
    Dim file As String
    Dim i As Long
    
    path1 = "C:\Cost Report\R&S Error Report\Numerator Overview\"
    path2 = "C:\Cost Report\R&S Error Report\Numerator Overview Missing Par Con\"
    
    Set path1files = New Collection
    file = Dir(path1)
    While file <> vbNullString
        path1files.Add path1 & file
        file = Dir
    Wend
    
    For i = 1 To path1files.Count
        Debug.Print i, path1files(i)
        file = Dir(path2)
        While file <> vbNullString
            Debug.Print path2 & file
            file = Dir
        Wend
    Next
    
End Sub

 

[lasvegasbuffet]

So this may be the problem.
myFile1 = Dir(myPath1 & myExtension1) < Starts a new DIR #1
myFile2 = Dir(myPath2 & myExtension2) < Starts a new DIR #2

myFile2 Dir < This is a continuation of the second DIR because it was the last
myFile1 Dir < This is also a continuation of the second DIR, but in this case there are no more files based on the wildcard rule and that you already processed those.

You're going to have to restructure this. Are you trying to get all the files in a folder and subfolders?

Thanks Jeffrey, basically yes. I'm comparing files in 2 folders to see if they are for the same client and if so then moving the sheet from the second folder file to the first folder file so it appears after Sheets(1). Some clients aren't in the second folder, hence the need for comparison.

DIR just wasn't working out so I did restructure and went with early binding instead which solved my problem. Thanks again for helping me understand how the DIRs were working!

 

[lasvegasbuffet]

You can't have nested Dir loops because the inner Dir loses the context established by the outer Dir. The solution is to move the outer Dir loop to a separate loop and store the files found by that loop in an array or a collection. Then loop through the array or collection, with a nested loop for the inner Dir loop. Like this:

Sub Loop_Files_2_Folders()

    Dim path1 As String, path2 As String
    Dim path1files As Collection
    Dim file As String
    Dim i As Long
   
    path1 = "C:\Cost Report\R&S Error Report\Numerator Overview\"
    path2 = "C:\Cost Report\R&S Error Report\Numerator Overview Missing Par Con\"
   
    Set path1files = New Collection
    file = Dir(path1)
    While file <> vbNullString
        path1files.Add path1 & file
        file = Dir
    Wend
   
    For i = 1 To path1files.Count
        Debug.Print i, path1files(i)
        file = Dir(path2)
        While file <> vbNullString
            Debug.Print path2 & file
            file = Dir
        Wend
    Next
   
End Sub

Thanks John, that looks interesting; I'll have to check it out.

In the end, DIR was too messy for me so I went an early binding approach instead which solved my problem. Thanks for your input!

 

[lasvegasbuffet]

Thanks Jeffrey, basically yes. I'm comparing files in 2 folders to see if they are for the same client and if so then moving the sheet from the second folder file to the first folder file so it appears after Sheets(1). Some clients aren't in the second folder, hence the need for comparison.

DIR just wasn't working out so I did restructure and went with early binding instead which solved my problem. Thanks again for helping me understand how the DIRs were working!

I meant late binding, not early!

 

[Macropod]

For some recursive folder & sub-folder processing code, see: Batch change the footer in multiple files with footer from another file & delete all word files in a nested directory. Although written for use with Word, adapting them for Excel would be a trivial undertaking