CODEWITHSUNDEEP
excelvba
Gitty
Gitty

Reputation: 166

Looping all files in a folder fails after first iteration

I'm trying to loop a macro through all files in a folder. After the first iteration, I get the error "Invalid Procedure Call or Argument". What could be causing this? The folder contains a lot of xlsx and csv files. The macro is set to run on the xlsx files only.

Sub Step18LoopAllFilesInAFolder()

    folderName = "D:\Users\Desktop\Macro Data\Test"
    If Right(folderName, 1) <> Application.PathSeparator Then folderName = folderName & Application.PathSeparator
    Fname = Dir(folderName & "*.xlsx")

    'loop through the files
    Do While Len(Fname)

        With Workbooks.Open(folderName & Fname)

           Call Step17MasterMacro


        End With

        ' go to the next file in the folder
        Fname = Dir

    Loop

End Sub

Upvotes: 2

Views: 62

Answers (1)

Gitty
Gitty

Reputation: 166

As per @braX's suggestion, I changed to the following. Works like a charm!

Sub Step18LoopAllFilesInAFolder()
    Dim FSO As Object
    Dim folder As Object
    Dim wb As Object

    Set FSO = CreateObject("Scripting.FileSystemObject")
    folderPath = "D:\Users\Desktop\Macro Data\Test"
    Set folder = FSO.GetFolder(folderPath)


    For Each wb In folder.Files
        If Right(wb.Name, 4) = "xlsx" Then
            Set masterWB = Workbooks.Open(wb)
            Call Step17MasterMacro
        End If
    Next
End Sub

Upvotes: 2

Related Questions