CODEWITHSUNDEEP
vbaexcel
Tollbooth
Tollbooth

Reputation: 86

VBA - Complex File Opening

I currently have coded how to open all files in a certain folder

Dim MyFolder As String
Dim MyFile As String

MyFolder = "K:\Data Directories\Acquisitions"
MyFile = Dir(MyFolder & "\*.xlsx")
    Do While Len(MyFile) > 0

        Workbooks.Open FileName:=MyFolder & "\" & MyFile

    MyFile = Dir
Loop

Now I'm trying to open all files in multiple folders that have the same name.

For instance:

Dim MyFolder As String
Dim MyFile As String
Dim MyFolder2 As String

MyFolder = "K:\Data Directories\Acquisitions"
MyFolder2 = MyFolder & "*\June 2015"
MyFile = Dir(MyFolder2 & "\*.xlsx")
    Do While Len(MyFile) > 0

        Workbooks.Open FileName:=MyFolder2 & "\" & MyFile

    MyFile = Dir
Loop

The problem is the * that I place before June 2015. It comes out as an actual "*" in the path code instead of a wildcard.

The code is meant to choose all folders in the Acquisition directory, and then look inside them for a June 2015 folder. From there, all the Excel files in these multiple June 2015 folders should be opened. What am I doing wrong?

Upvotes: 0

Views: 232

Answers (1)

dev1998
dev1998

Reputation: 892

I think this will do what you want. Give it a try and see what happens.

Sub DoFolderPart1()

    Dim FileSystem As Object
    Dim HostFolder As String

    HostFolder = "K:\Data Directories\Acquisitions"

    Set FileSystem = CreateObject("Scripting.FileSystemObject")
    DoFolder FileSystem.GetFolder(HostFolder)
End Sub

Sub DoFolder(Folder)
    Dim SubFolder
    Dim strName As String
    Dim pos As Integer

    For Each SubFolder In Folder.SubFolders

        DoFolder SubFolder

    Next

    Dim File
        strName = Folder.name
        pos = InStr(strName, "June 2015")
        If pos > 0 Then
            For Each File In Folder.Files
                If Right(File, 4) = "xlsx" Then
                    Workbooks.Open Filename:=File
                End If
            Next
       End If
End Sub

I based my answer on this loop-through-all-subfolders-using-vba

Upvotes: 1

Related Questions