0

This code uses Dir to get sub-dirs. Each sub-dir needs to have its xls files processed. After it finishes processing the first batch of xls, I get invalid procedure call or argument. I am guessing when I pass dirLook to the function it creates a copy? Please assist. I need to move on to the next sub-dir.

        dirLook = dir(strDir, vbDirectory)
        Do While dirLook <> ""
            If dirLook <> "." And dirLook <> ".." Then
                If (GetAttr(strDir & adir) And vbDirectory) = vbDirectory Then
                    'Perform action on folder here

                    loopXls (dirLook)

                    Debug.Print dirLook
                End If
            End If
            dirLook = dir
        Loop

loopXls:

Function loopXls(dirStr As String)
    Dim count As Integer
    Dim strFilename As String
    Dim strPath As String
    Dim wbkTemp As Workbook


    strPath = "C:\Users\pmevi\Documents\L7\L7_Master_Book\Input\" & dirStr & "\"
    strFilename = dir(strPath & "*.xls")
    Do While Len(strFilename) > 0
        Set wbkTemp = Workbooks.Open(strPath & strFilename)
        '
        ' do your code with the workbook
        '

        ' save and close it
        wbkTemp.Close True
        count = count + 1
        strFilename = dir
    Loop
    Debug.Print (count)

End Function

EDIT2

I am attemping to load each dir into an array, but for some reason when I loop through array I only see 3 folders instead of 5.

 Dim dirs(5) As String
 Dim i As Integer
 Dim endNum As Integer

  endNum = 4

   dirLook = dir(strDir, vbDirectory)

    For i = 0 To endNum
        dirs(i) = dirLook
        dirLook = dir
    Next i

    For i = 0 To endNum
        Debug.Print (dirs(i))
    Next i

    output:
    3-10-14
    3-11-14
    3-12-14

    expected:
    3-10-14
    3-11-14
    3-12-14
    3-13-14
    3-14-14

Edit3 Found issue to array. 2 indexes are used for "." and ".."

Mayhem
  • 1,533
  • 2
  • 10
  • 5

1 Answers1

1

It's not exactly clear from the code, but if the code above is in the loopXls method, making this a recursive function then the problem is that you cannot use the Dir function recursively, as stated here, towards the bottom.

The answer here include shows an example class for recursing with the Dir function.

Community
  • 1
  • 1
LimaNightHawk
  • 6,613
  • 3
  • 41
  • 60