Excel VBA - Get full folder name from folder path and wildcard

[VBA_Debutant]

Hello,

My goal is to have Excel VBA open a file while I know almost all of the folder path where it's located, I'm just missing the full name of the last subfolder.

I have a folder path like "C:\Users\..." and the last subfolders to get to the file I want to open are consistently named "YYYY" (as is the year, like "2012") then "MM-..." (as in the month, like "07" and then something I don't know).
For example in the "2012" folder there is a "01.Jan" folder, a "02_feb-v1" folder, a "03-March" folder etc. I cannot rename the folders because they are in a shared location and I do not own them. But there is only 1 folder per month and in each of these folders there is an Excel file with the same name. I would like to access the file for a specific month with something simple like "Workbooks.Open" but it won't work with a wildcard in the middle of the file path.

Is it possible to have Excel VBA find and return the folder name in such a way that I can integrate it as a variable in my folder path?

I hope this makes sense. Thank you for your help.

 

[Jerry Sullivan]

Here's a code example that shows how to use the Dir function to look for a match.

Sub Find_SubFolder()
    Dim sFile As String, sPathSeek As String, sPathMatch As String
    
    Const sMainPath As String = "C:\TEST\MyProject\"
    Const sYear As String = "2011"
    Const sMonth As String = "03"
    
    On Error Resume Next
    sPathSeek = sMainPath & sYear & "\" & sMonth & "*.*"
    sFile = Dir(sPathSeek, vbDirectory)

    Do While Len(sFile) > 0
        If Left(sFile, 1) <> "." Then
            If (GetAttr(sFile) And vbDirectory) = vbDirectory Then
                sPathMatch = sFile
                Exit Do
            End If
        End If
        sFile = Dir
    Loop

    MsgBox IIf(sPathMatch = "", "Match not found", "Match: " & sPathMatch)
End Sub

 

[VBA_Debutant]

Thank you very much Jerry, that really helped me!
I didn't realize I could use the Dir function with folders instead of files because I couldn't figure out what vbDirectory was for. Thanks to you this is much clearer now

 

[Jerry Sullivan]

You're welcome.

 

[gehawk]

You're welcome.

Hi guys,

I have a question related with this same subject. Instead of year and month I have a number ranging from 1 - 1000 in the foldernames.:
Example :
C:\Users\Desktop\testje2\30 apples
C:\Users\Desktop\testje2\300 apples
C:\Users\Desktop\testje2\3000 apples
C:\Users\Desktop\testje2\30000 apples

I changed the code :
After using the MsgBox I called explorer shell to open the folder :

Const sMainPath As String = "C:\Users\Desktop\testje2\"
sPathSeek = sMainPath & 300 & "*"
Call Shell("explorer.exe " & sMainPath & sPathMatch, vbNormalFocus)

My Issue : Whenever 300 is not found it opens the 3000 apples folder. How can I ensure this does not happen?

Thanks!

 

[Jerry Sullivan]

Hi guys,

I have a question related with this same subject. Instead of year and month I have a number ranging from 1 - 1000 in the foldernames.:
Example :
C:\Users\Desktop\testje2\30 apples
C:\Users\Desktop\testje2\300 apples
C:\Users\Desktop\testje2\3000 apples
C:\Users\Desktop\testje2\30000 apples

I changed the code :
After using the MsgBox I called explorer shell to open the folder :

Const sMainPath As String = "C:\Users\Desktop\testje2\"
sPathSeek = sMainPath & 300 & "*"
Call Shell("explorer.exe " & sMainPath & sPathMatch, vbNormalFocus)

My Issue : Whenever 300 is not found it opens the 3000 apples folder. How can I ensure this does not happen?

Thanks!

You'll need to make the pattern such that it will only match the folder you are trying to find not match any similarly named folders.

For the example you gave you could use either of these...

sPathSeek = sMainPath & "300 *"  

sPathSeek = sMainPath & 300 & " *"

Note the space character before the "*" in both alternatives.

 

[gehawk]

You'll need to make the pattern such that it will only match the folder you are trying to find not match any similarly named folders.

For the example you gave you could use either of these...

sPathSeek = sMainPath & "300 *"  

sPathSeek = sMainPath & 300 & " *"

Note the space character before the "*" in both alternatives.

Thanks for the reply.
I have another question : Imagine the 30 apples folder is in a random subfolder which you don't know the name of, but they are all in the main directory C:\Users\Desktop\testje2\ with :

C:\Users\Desktop\testje2\fruits\30 apples
C:\Users\Desktop\testje2\yellowfruits\300 apples
C:\Users\Desktop\testje2\redfruits\3000 apples
C:\Users\Desktop\testje2\pinkfruit\30000 apples


How do I change the script in such a way it will look in all of the subfolder for the 30 apples map ?
Thanks !

 

[gehawk]

Thanks for the reply.
I have another question : Imagine the 30 apples folder is in a random subfolder which you don't know the name of, but they are all in the main directory C:\Users\Desktop\testje2\ with :

C:\Users\Desktop\testje2\fruits\30 apples
C:\Users\Desktop\testje2\yellowfruits\300 apples
C:\Users\Desktop\testje2\redfruits\3000 apples
C:\Users\Desktop\testje2\pinkfruit\30000 apples


How do I change the script in such a way it will look in all of the subfolder for the 30 apples map ?
Thanks !

I made a solution for this in an excel sheet By :
Getting a list of all files in the main directory
Looking if the "30" is in the file directory on the right (with formula's)
Hyperlinking with index match the filename and running it with followhyperlink in vba.

I'm sure there is an easier way by programming it in vba ..