Open all the files in subfolders

Question

Good morning, I am writing this code that allow me to open and copy a sheet in each macro file that I have in many subfolders. The problem is that I have all these files in subfolders included in a folder but they have all different names. What should I add to this code?

Thank you very much!

Application.EnableEvents = False
Application.ScreenUpdating = False

Path = ActiveWorkbook.Path

FileName = Dir(Path & "\*.xlsm", vbNormal)

Do Until FileName = ""

    Set Wkb = Workbooks.Open(FileName:=Path & "\" & FileName, UpdateLinks:=3)
    For Each ws In Wkb.Worksheets
    Application.EnableEvents = False
    Application.ScreenUpdating = False
    wsName = ws.Name
     If (wsName = "Summary (Output)") Then
        ws.Copy After:=ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count)
    Exit For
     End If
    Next ws
    Wkb.Close False
     FileName = Dir()
Loop

I don't have put all the declaration, but there are :)

use the `filesystemobject` and from its `folder` you can get the `subfolders` collection — Nathan_Sav, May 03 '17 at 16:07
Check a recursive approach: http://stackoverflow.com/questions/22645347/loop-through-all-subfolders-using-vba — Zerk, May 03 '17 at 16:19

score 0 · Answer 1 · answered May 03 '17 at 16:30

Some people don't like this approach as you get a small window pop up for a second.

This will open all .xls workbooks - so xls, xlsx, xlsm, xlsb and any other flavour of xls

Public Sub OpenAllWorkbooks()

    Dim vFiles As Variant
    Dim vFile As Variant

    vFiles = EnumerateFiles("<Folder Path including final backslash - e.g. C:\Windows\>", "xls*")

    For Each vFile In vFiles
        Workbooks.Open vFile
    Next vFile

End Sub

Public Function EnumerateFiles(sDirectory As String, _
            Optional sFileSpec As String = "*", _
            Optional InclSubFolders As Boolean = True) As Variant

    EnumerateFiles = Filter(Split(CreateObject("WScript.Shell").Exec _
        ("CMD /C DIR """ & sDirectory & "*." & sFileSpec & """ " & _
        IIf(InclSubFolders, "/S ", "") & "/B /A:-D").StdOut.ReadAll, vbCrLf), ".")

End Function

Open all the files in subfolders

1 Answers1

Linked