VBA Loop through multiple folders

Question

Possible Duplicate:
get list of subdirs in vba

I'm trying to apply the following code, which applies to running this VBA loop through all files in a folder, to make it run through all folders within one folder.

Is there any way that this is possible?

I have about 50 folders, each with the same named workbook, so I'd need to try and make it more efficient.

Thanks!

Sub LoopFiles()

    Application.DisplayAlerts = False    
    Dim strDir As String, strFileName As String
    Dim wbCopyBook As Workbook
    Dim wbNewBook As Workbook

    strDir = "C:\Documents and Settings\mburke\Desktop\Occupancy 2013\"
    strFileName = Dir(strDir & "*.xlsm")

    Set wbNewBook = Workbooks.Add

    Do While strFileName <> ""
        Set wbCopyBook = Workbooks.Open(strDir & strFileName)
        wbCopyBook.Sheets(1).Copy Before:=wbNewBook.Sheets(1)
        wbCopyBook.Close False
        strFileName = Dir
    Loop

    Application.DisplayAlerts = True
End Sub

Are the 50 folders all folder below your main molder or do they "nest" in two or more layers? You will need a recursive call for the later — brettdj, Oct 21 '12 at 22:23
See http://stackoverflow.com/questions/9827715/get-list-of-subdirs-in-vba for a recursive directory calling methods — brettdj, Oct 21 '12 at 22:30
Also see this http://stackoverflow.com/questions/10898782/get-the-data-from-excel-files-in-sub-directories/10903402#10903402 Look at the `ProcessFiles()` Function which is based on the code taken from vbaexpress.com — Siddharth Rout, Oct 22 '12 at 06:31

score 1 · Answer 1 · answered Oct 21 '12 at 21:52

Sure you can! Just add another LoopDirectories method that does a DIR for folders.

Change the LoopFiles method a bir to accept a directory parameter:

Sub LoopFiles(directory As String)

    Application.DisplayAlerts = False

    Dim strDir As String, strFileName As String
    Dim wbCopyBook As Workbook
    Dim wbNewBook As Workbook


    strFileName = Dir(directory & "*.xlsm")

    Set wbNewBook = Workbooks.Add

    Do While strFileName <> ""
        Set wbCopyBook = Workbooks.Open(directory & strFileName)
        wbCopyBook.Sheets(1).Copy Before:=wbNewBook.Sheets(1)
        wbCopyBook.Close False
        strFileName = Dir
    Loop

    Application.DisplayAlerts = True
End Sub

Then call the LoopFiles method for each folder in your LoopDirecotries method.

But this doesn't explain how to call each of the folders – brettdj Oct 21 '12 at 22:24 — brettdj, Oct 21 '12 at 22:24

VBA Loop through multiple folders

1 Answers1