-1

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?

Community
  • 1
  • 1
dfoakley
  • 3
  • 3

1 Answers1

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.

A.S.H
  • 29,101
  • 5
  • 23
  • 50