VBA - Complex File Opening

Question

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?

That didn't work. I set a msgbox MyFolder2 to show the path name. It shows a * before June 2015. Then I get a "Bad file name" error on the line that sets the MyFile variable. — Tollbooth, Dec 18 '15 at 16:51
Why don't you retrieve all the subfolders name and run a loop on it? — ManishChristian, Dec 18 '15 at 16:53
I like that idea. So basically a code to find all the folders in the main directory, and then loop through these folders for the month folder? I'll give it a shot, but that might be beyond my abilities. — Tollbooth, Dec 18 '15 at 16:55

score 1 · Accepted Answer · edited May 23 '17 at 12:23

1

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

edited May 23 '17 at 12:23

Community

1
1

answered Dec 18 '15 at 20:54

dev1998

882
7
17

Wonderful! Thank you so much! – Tollbooth Dec 19 '15 at 00:23
I keep running into a problem where I can't perform any operations on the "File" after opening it. Any idea what could be causing this? For instance, there is an error if I place Workbooks(File).Activate after the file is opened. – Tollbooth Dec 21 '15 at 23:56
1

Why are you trying to activate the workbook after it is open? Take a look at this: [link] (http://stackoverflow.com/questions/6977807/how-do-i-activate-a-specific-workbook-and-a-specific-sheet) – dev1998 Dec 22 '15 at 15:21
Figured it out. Thanks! – Tollbooth Dec 22 '15 at 17:31

VBA - Complex File Opening

1 Answers1