Find specific folder using Excel VBA

It will be pretty time consuming searching an entire drive. I create getFileList to recursively search all folders and subfolders returning the file paths in an array. You could speed up the process by having the function exit when it finds the correct directory.

I recommend saving the path to the registry using SaveSetting and later retriving it using GetAllSettings

The HowTo_GetFileList macro shows you how you can filter the array of file paths.

Sub HowTo_GetFileList()
    Const MATCHVALUE As String = "Demo"
    Dim f, FileList, FilteredList
    FileList = getFileList("C:\Users\Owner\Downloads")
    FilteredList = Filter(FileList, MATCHVALUE)

    For Each f In FilteredList
        'Do Something
    Next

End Sub

Function getFileList(localRoot As String, Optional fld, Optional ftpArray)
    Dim fso, f, baseFolder, subFolder, ftpFile, i

    Set fso = CreateObject("Scripting.Filesystemobject")

    If IsMissing(fld) Then
        Set baseFolder = fso.GetFolder(localRoot)
    Else
        Set baseFolder = fld
    End If

    For Each f In baseFolder.Files

        If IsMissing(ftpArray) Then
            ReDim ftpArray(0)
        Else
            i = UBound(ftpArray) + 1
            ReDim Preserve ftpArray(i)
        End If
        ftpArray(i) = f.Path

    Next

    For Each subFolder In baseFolder.SubFolders
        getFileList localRoot, subFolder, ftpArray
    Next

    getFileList = ftpArray
End Function

The most efficient option would be to make a LDAP query to return a list of shared folders. This Sample Script from VBSEditor.com will do just that. This will require a fair amount of knowledge of your Active Directory.

This is not the more complex answer, just a function that opens a folder selection box and returns a string with the selected path. I put it in most of my apps, to choose a folder each time you want to runs the code is not very time consuming, and it is very stable.

Public Function FolderSelection() As String
    Dim objFD As Object
    Dim strOut As String

    strOut = vbNullString

    Set objFD = Application.FileDialog(4)

    objFD.Title = 'the title you want to show
    objFD.InitialFileName = 'standard path

    If objFD.Show = -1 Then
        strOut = objFD.SelectedItems(1)
    End If

    Set objFD = Nothing
    FolderSelection = strOut

End Function