CODEWITHSUNDEEP
vbaexcelloopsfilesystemobject
user9103716
user9103716

Reputation:

VBA loop through subfolders to open multiple files

I have a code that allows me to search through a file path and multiple subfolders to open a file however I want to be able to open multiple files which have a slightly different name e.g effect00001.dat or effect00014.dat however I'm not too sure how

My code is:

Sub LoopSubfoldersAndFiles()
    Dim fso As Object
    Dim Folder As Object
    Dim subfolders As Object
    Dim MyFile As String
    Dim wb As Workbook
    Dim CurrFile As Object

With Application
    .ScreenUpdating = False
    .EnableEvents = False
    .Calculation = xlCalculationManual
End With

    Set fso = CreateObject("Scripting.FileSystemObject")
    Set Folder = fso.GetFolder("\\My Documents\Output files\analysis-tool-development")
    Set subfolders = Folder.subfolders
    MyFile = "effect00001.dat"

    For Each subfolders In subfolders

    Set CurrFile = subfolders.Files

        For Each CurrFile In CurrFile
            If CurrFile.Name = MyFile Then
                Set wb = Workbooks.Open(subfolders.Path & "\" & MyFile)
            End If
        Next

    Next

    Set fso = Nothing
    Set Folder = Nothing
    Set subfolders = Nothing

With Application
    .EnableEvents = True
    .Calculation = xlCalculationAutomatic
    .ScreenUpdating = True
End With

End Sub

Upvotes: 0

Views: 2489

Answers (1)

iDevlop
iDevlop

Reputation: 25252

replace the line

If CurrFile.Name = MyFile Then

by

If CurrFile.Name Like MyFile Then

You can then use wildcards for MyFile

Edit:

I also think the line 

Set wb = Workbooks.Open(subfolders.Path & "\" & MyFile)

should be replaced by

Workbooks.Open(subfolders.Path & "\" & MyFile)

since wb value is replaced by another one immediately, and not used.

Upvotes: 2

Related Questions