CODEWITHSUNDEEP
ms-accessvba
akube
akube

Reputation: 3

VBA code to find and open a folder based on its name

Essentially what I am trying to do is use vba to find and open a folder based on its name. I have looked everywhere and cannot find the solution. This is what

I have so far but nothing happens. I would like for the user to click a cmd button and it opens directly to a movie folder.

C:\Storage\Video\Video Folders\Genre\"Folder"\"movie title, Year"

the folder path after Video Folders can change depending on the "movie title, year" 

Dim fso, Folder, subFlds, fld, s, showFolder as Object

Set fso = CreateObject("Scripting.FileSystemObject")
Set Folder = fso.GetFolder(Path)
Set subFlds = Folder.SubFolders

s = "C:\Storage\Video\Video Folders"
For Each fld In subFlds
    s = s & Me.FolderName
    s = s & "<br />"
Next
showFolder = s
Application.FollowHyperlink showFolder

Upvotes: 0

Views: 6552

Answers (3)

akube
akube

Reputation: 3

Modifying the call shell that @Ibo recommended and this worked for me or rather its as close as I could get

Sub cmd_folder_Click() Dim Folder As String, s As String, loc As String

   s = "search-ms:query="
   loc = "&crumb=location:C:\Storage\Video\Video Folders\"
   Call Shell("explorer.exe " & Chr(34) & s & Me.Folder & loc & Chr(34), vbNormalFocus)

End Sub

Upvotes: 0

Hubisan
Hubisan

Reputation: 1172

You can use the following code

  • to look for a folder by name in a path including subfolders of subfolders
  • and then open that path in the file explorer.

I created a separate function to be able to call it recursively (to look in subfolders of subfolders ...)

Use this to call the main function:

Sub openFolderIfFound()
    Dim path As String
    Dim folderName As String
    Dim folderPath As String

    path = "C:\Storage\Video\Video Folders"
    folderName = Me.FolderName
    ' Example: folderName = "Seven Samurai - 1954"
    ' Example using wildcards: folderName = "*Samurai*"
    folderPath = lookForFolderInPath(path, folderName)
    If folderPath <> "" Then Application.FollowHyperlink folderPath
    ' Note: in Excel use ThisWorkbook.FollowHyperlink folderPath
End Sub

Main function to look for a folder:

' Look for folder by name in path (including subfolders of subfolders)
' and return the path of the folder if it was found.
'
' Args:
'   path (String): Path to look in.
'   folderName (String): Name of folder to look for. Uses LIKE operator for comparison to enable the use of wildcards:
'     https://msdn.microsoft.com/en-us/vba/language-reference-vba/articles/like-operator
' Returns:
'   Path of folder if found else empty String ""
Function lookForFolderInPath(path As String, folderName As String, Optional ByRef fso As Object) As String
    Dim topFolder As Object
    Dim subfolders As Object
    Dim folder As Object
    Dim i As Long

    If fso Is Nothing Then Set fso = CreateObject("Scripting.FileSystemObject")
    Set topFolder = fso.GetFolder(path)
    Set subfolders = topFolder.subfolders

    ' Check if permission to access subfolders
    On Error Resume Next
    i = subfolders.Count
    On Error GoTo 0
    If i <> 0 Then
        ' Loop through subfolders of folder (path)
        For Each folder In subfolders
            If folder.Name Like folderName Then
                ' Return folder path if folder name matched subfolder name
                lookForFolderInPath = folder.path
                Exit For
            Else
                ' Recursively call function to check subfolders in subfolders
                lookForFolderInPath = lookForFolderInPath(folder.path, folderName, fso)
                ' Exit loop if folder was found
                If lookForFolderInPath <> "" Then Exit For
            End If
        Next
    End If

    Set fso = Nothing
    Set folder = Nothing
End Function

Upvotes: 1

Ibo
Ibo

Reputation: 4319

I am not sure how you want to select the folder you want to open, but the key part is this one line that will solve your problem. Simply pass the path of the folder you want to open and it will do it and will activate the window for the user:

Sub OpenFolder(sPath)
    Call Shell("explorer.exe" & " " & sPath, vbNormalFocus)
End Sub

Upvotes: 1

Related Questions