Looping through files in directory with Dir function, just hits same file over and over

Question

I have a spreadsheet that looks through several other spreadsheets in a given directory to extract some statistics from them. So far, it gets to the first file and just hits it over and over again.

My code goes something like this:

Public Sub GetResults()
    Const strDirectory As String = "SomeNetworkLocation"
    Dim strFileName As String

    strFileName = Dir(strDirectory & "*.xl??")

    Do While strFileName <> ""

        If strFileName Like "SomeMatchingScheme_##_??"
            Workbooks.Open strDirectory & strFileName

            'Do stuff with the workbook

            Workbooks(strFileName).Close
        End If
    Loop
End Sub

Any suggestions?

score 1 · Accepted Answer · answered Sep 25 '15 at 23:02

1

You need to call again

strFileName = Dir  ' with no parameters

before loop. This call will iterate strFileName to the next file. Without it, it will stick to the first found file.

answered Sep 25 '15 at 23:02

A.S.H

29,101
5
23
50

That did the trick, thanks. – dfoakley Sep 28 '15 at 14:03

Looping through files in directory with Dir function, just hits same file over and over

1 Answers1