Have issues with a Do While Loop

Question

I have this Do While ... Loop that I am trying to modify for use in another file, but it is not giving me the same result as I expected.

In my original File, the code loops through all 52 files in the fldrPath folder with out and issues:

fldrPath = ThisWorkbook.Sheets("Main").Range("C6")

filePath = Dir(fldrPath & "\*.xl??")

counter = 0

Do While filePath <> ""

        counter = counter + 1 

        i = 0 

        fcst = Left(filePath, 9) 

        If fcst = "Forecast\_" Then 

            If Mid(filePath, 13, 1) = "\_" Then 

                stoNo = "STO" & Mid(filePath, 10, 3) 

            ElseIf Mid(filePath, 14, 1) = "\_" Then 

                stoNo = "STO" & Mid(filePath, 10, 4) 

            End If 

            Call open\_packageFile(fldrPath & "\" & filePath) 

            filePath = Dir() 

        End If 

    Loop

In this new file, it only seems to see the first file in the fldrPath folder, even though there are 4 files in the folder:

fldrPath = mtws.Range("C10")

filePath = Dir(fldrPath & "\*.xl??")

counter = 0

Do While filePath <> ""

        If filePath = trafficFileName Then 

            counter = counter + 1 

            MsgBox trafficFileName 

        ElseIf filePath = itemsFileName\_STO Then 

            counter = counter + 1 

            MsgBox itemsFileName\_STO 

        ElseIf filePath = itemsFileName\_IFS Then 

            counter = counter + 1 

            MsgBox itemsFileName\_IFS 

        End If 

        If counter = 3 Then Exit Do 

        filePath = Dir()

Loop

I can't figure out what the issue is. Any help will be greatly appreciated. Thanks.

Answer 1

I cannot help you, since you did not provide the value of the variables, as I requested. Good luck!

Answer 2

fldrPath:="C:\Users\ayoji\Documents\MAR Traffic & Items Data Download" and there are 3 files in there:

Sales_A_Transactions_F_P2

Sales_A_Transactions_S_P2

Traffic_A_Conversion_P2

and here is the entire subroutine:

Dim counter As Integer 

Dim filePath As String 

Dim i As Integer 

Dim stoNo As String 

Dim trafficFilePath As String, itemsFilePath_STO As String, itemsFilePath_IFS As String 

Dim trafficFileName As String, itemsFileName_STO As String, itemsFileName_IFS As String 

Sub runReport() 

Dim fldrPath As String 

Dim fldrStr As String 

Dim fcst As String 

Dim nextDash As String 

Dim mtws As Worksheet 

'===================================================== 

    On Error GoTo errHandle 

    Set mtws = Worksheets("Main") 

    fldrPath = mtws.Range("C10") 

    filePath = Dir(fldrPath & "\*.xl??") 

    counter = 0 

    trafficFileName = mtws.Range("J6") 

    itemsFileName_STO = mtws.Range("J7") 

    itemsFileName_IFS = mtws.Range("J8") 

    trafficFilePath = fldrPath & "\" & trafficFileName 

    itemsFilePath_STO = fldrPath & "\" & itemsFileName_STO 

    itemsFilePath_IFS = fldrPath & "\" & itemsFileName_IFS 

    If FileExists(trafficFilePath) = False Or FileExists(itemsFilePath_STO) = False Or FileExists(itemsFilePath_IFS) = False Then 

        MsgBox "Please make sure the correct folder has been selected. " & _ 

                "Also that the needed files are in the folder", _ 

                vbOKOnly, "Missing Files" 

        Exit Sub 

    ElseIf FileExists(trafficFilePath) = True And FileExists(itemsFilePath_STO) = True And FileExists(itemsFilePath_IFS) = True Then 

        Do While filePath <> "" 

            If filePath = trafficFileName Then 

                counter = counter + 1 

                MsgBox trafficFileName 

            ElseIf filePath = itemsFileName_STO Then 

                counter = counter + 1 

                MsgBox itemsFileName_STO 

            ElseIf filePath = itemsFileName_IFS Then 

                counter = counter + 1 

                MsgBox itemsFileName_IFS 

            End If 

            If counter = 3 Then Exit Do 

            filePath = Dir() 

        Loop 

    End If 

GoTo EndSub 

errHandle: 

 MsgBox "The current folder doesn't contain the forecast packages.", vbOKOnly, "Wrong folder selected" 

 Exit Sub 

EndSub: 

End Sub

Answer 3

Off-hand, I don't see the issue, either. But I haven't really looked.

What does "leap out" at me is the potential for an infinite loop in original loop.

If If fcst = "Forecast\_" Then is false, you never execute filePath = Dir() again.

My guess: filePath = Dir() should be outside the If...EndIf block.

---

As for the issue that you want us to address, I suspect that we need more details.

Off-hand (again, not really looking hard), what is the value of fldrPath?

And more to the point, show us a list of files in folder identified by fldrPath to demonstrate that there is indeed more than just one file.

PS.... Also, what are the values of the other relevant variants, notably filePath (each iteration), trafficFileName, itemsFileName_STO and itemsFileName_IFS.

---

PS.... Although you say that the issue is that the new loop finds only one file, I might note that it will not find all 4 files.

At most, it will find only 3 files with a filePath value that equals trafficFileName, itemsFileName_STO or itemsFileName_IFS.

This is because you abort the loop with the statement If counter = 3 Then Exit Do