VBA Loop through multiple folders [duplicate]

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
– brettdj, Commented Oct 21, 2012 at 22:23
See stackoverflow.com/questions/9827715/get-list-of-subdirs-in-vba for a recursive directory calling methods — brettdj
– brettdj, Commented Oct 21, 2012 at 22:30
Also see this stackoverflow.com/questions/10898782/… Look at the ProcessFiles() Function which is based on the code taken from vbaexpress.com — Siddharth Rout
– Siddharth Rout, Commented Oct 22, 2012 at 6:31

Michael Rodrigues · Accepted Answer · 2012-10-21 21:52:23Z

1

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.

answered Oct 21, 2012 at 21:52

Michael Rodrigues

5,1473 gold badges27 silver badges52 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

brettdj Over a year ago

But this doesn't explain how to call each of the folders

Collectives™ on Stack Overflow

VBA Loop through multiple folders [duplicate]

1 Answer 1

1 Comment

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Linked

Related