Out of Stack Space from looping through folders

Question

I'm combining a couple hundred workbooks into one book.

I don't need much from each workbook, but I have to go into each one.

Each workbook is in a folder that is named the year that the reports were taken. Those year folders are located in a "Master" folder.

I need to loop through the year folders and hop into each workbook in that folder. I'll grab the info I need and put it in a "Master workbook".

It was suggested in this question that I use this recursive function.

Loop Through All Subfolders Using VBA

I got an invalid outside procedure so I modified my code so that all of it was inside the sub.

The code I have now is a test code that is looking in a test folder I made on my desktop that contains 5 folders. Each of the 5 contains 2 workbooks. It gives me a out of stack space error and it highlights the line DoFolder FileSystem.GetFolder(HostFolder).

Here is the code I have so far.

Sub DoFolder(Folder)
    Dim FileSystem As Object
    Dim HostFolder As String

    Application.EnableEvents = False

    HostFolder = "C:\Users\27659\Desktop\temp test folder"

    Set FileSystem = CreateObject("Scripting.FileSystemObject")

    DoFolder FileSystem.GetFolder(HostFolder)

    Dim i As Long
    Dim wb As Workbook
    Dim ws As Worksheet

    Set wb = Application.Workbooks("\\drive name\public\Organizational Development\my name\Open Projects\Project 1 Milling Improvements\Past Data\Past Data Collection and Summary Book Start 10_29_2018.xlsm")
    Set ws = wb("Sheet1")

    i = 9

    Dim SubFolder
    For Each SubFolder In Folder.SubFolders
        DoFolder SubFolder
    Next
    Dim File
    For Each File In Folder.Files
        Folder.Files.Open
        wb.ws.Cells(i, 2).Value = ActiveWorkbook.Worksheets(1).Cells(1, 1).Value
        i = i + 1
    Next

    Application.EnableEvents = True

End Sub

This won't necessarily solve your problem, but it seems like you just go through all the sub folders and only look at the files in the last sub folder — Jchang43, Oct 29 '18 at 17:53
Can you try adding a "\" to the end of your host folder name? — Jchang43, Oct 29 '18 at 17:55

score 1 · Accepted Answer · answered Oct 29 '18 at 18:36

The problem line (DoFolder FileSystem.GetFolder(HostFolder)) is a call to run the sub DoFolder. In the sample of the answer you linked to, there is some example code for how to call the function, followed by the function itself. Granted, the sample code for calling the function is not wrapped inside a separate sub/function, so it won't run as written.

However, the attempted fix for the original invalid outside procedure error was just repeatedly calling the DoFolder function right after the HostFolder was defined (and no way to either not recursively call itself or to finish the current iteration -- which is what resulted in the Out of Stack Space error).

In the code I've included below, you can see how there is one function/sub for defining the initial folder and doing the initial function call, and the DoFolder function/sub with the actual recursion and folder operation(s) required.

Additionally, if there is code you wish to do after pulling the data from the folders (i.e. only once per code run, not once per folder) make sure you don't include that in the DoFolder sub. Instead either use it after calling the RunDoFolder sub or in the RunDoFolder after the DoFolder call. (Or after whatever code you use to call the DoFolder sub)

Sub RunDoFolder()
    Dim FileSystem As Object
    Dim HostFolder As String

    Application.EnableEvents = False

    HostFolder = "C:\Users\27659\Desktop\temp test folder"

    Set FileSystem = CreateObject("Scripting.FileSystemObject")

    DoFolder FileSystem.GetFolder(HostFolder)
End Sub


Sub DoFolder(Folder)

    Dim i As Long
    Dim wb As Workbook
    Dim ws As Worksheet

    Set wb = Application.Workbooks("\\drive name\public\Organizational Development\my name\Open Projects\Project 1 Milling Improvements\Past Data\Past Data Collection and Summary Book Start 10_29_2018.xlsm")
    Set ws = wb("Sheet1")

    i = 9

    Dim SubFolder
    For Each SubFolder In Folder.SubFolders
        DoFolder SubFolder
    Next
    Dim File
    For Each File In Folder.Files
        Folder.Files.Open
        wb.ws.Cells(i, 2).Value = ActiveWorkbook.Worksheets(1).Cells(1, 1).Value
        i = i + 1
    Next

    Application.EnableEvents = True

End Sub

Okay your solution worked, but can you please eleborate on running code for each file. I wanna take info from each workbook, but I don't understand how i'm supposed to reference the file to pull from it. — Keizzerweiss, Oct 29 '18 at 19:30
https://www.reddit.com/r/excel/comments/9sgtt5/referencing_workbooks_as_i_loop_through_them_vba/ — Keizzerweiss, Oct 29 '18 at 21:20
@Keizzerweiss, from the reddit link you attached, it looks like you've gotten some answers. Did you still need any elaboration? — Mistella, Oct 29 '18 at 22:24
No i think I got it, thanks for your help. It was just pissy with the file paths mostly. — Keizzerweiss, Oct 30 '18 at 01:14

Out of Stack Space from looping through folders

1 Answers1