Match Folder Name with cell value

[babar2019]

Hello,

I'm trying to open the folder which matches with the cell 'B4' value.

Example if B4 is the date value 08/08/2019, I first would like to format 'B4' to "MM YYYY" format and compare that with the folder names and open the right match. In this case it should open 08 2019 folder.

Please advise

 

[John_w]

What is the folder path which contains the example '08 2019' subfolder?

This macro uses Application.DefaultFilePath as the folder path, which is usually C:\Users\your username\Documents, so it opens C:\Users\your username\Documents\08 2019, if it exists.

Public Sub Open_Folder()

    Dim path As String
    
    path = Application.DefaultFilePath & "\" & Format(Range("B4").Value, "MM YYYY")
    
    If Dir(path, vbDirectory) <> vbNullString Then
        Shell "explorer.exe """ & path & """", vbNormalFocus
    Else
        MsgBox "The folder " & path & vbCrLf & _
               "doesn't exist"
    End If
    
End Sub

 

[babar2019]

Hi John,

thanks.. the folder path is P:lending\nonpublic\IT\Applications\Test

The excel would be in Test folder
All the folders would be under Applications folders such as 01 2019, 02 2019, 03 2019 etc..

I want the B4 cell range to match with one of these folders and open the folder.

I don’t want to hard code the folder location because it is used in different places. The Vba has to go back one step from where the excel was launched and find the matched folder to open..

 

[John_w]

Try this macro:

Public Sub Open_Folder2()

    Dim folderPath As String
        
    folderPath = ThisWorkbook.Path & "\..\" & Format(ActiveSheet.Range("B4").Value, "MM YYYY")
    
    If Dir(folderPath, vbDirectory) <> vbNullString Then
        Shell "explorer.exe """ & folderPath & """", vbNormalFocus
    Else
        MsgBox "The folder " & vbCrLf & vbCrLf & _
               folderPath & vbCrLf & vbCrLf & _
               "doesn't exist"
    End If
    
End Sub

PS - If you don't like the "\..\" in the warning message which is displayed if the folder path doesn't exist (most users probably don't understand what "\..\" in a folder path means) then I can modify the code to use a FileSystemObject method which resolves the "\..\" to the actual folder name.

 

[babar2019]

Hello, How can you not have the folder window visible?

I'm trying to execute a piece of code which converts the excel to pdf and saves it into this folder after it recognizes the right folder match. The folder open should just happen in the background without user seeing it.

Thanks

 

[John_w]

Change vbNormalFocus to vbMinimizedNoFocus.