CODEWITHSUNDEEP
vbaexcel
Daruki
Daruki

Reputation: 491

Find specific folder using Excel VBA

I've been working on a workbook to be distributed to others and a large part of the code relies on pulling from workbooks saved in a shared sync folder - let's call it "TEETH".

For me, TEETH was installed into this path: "C:\Users\TEETH" but for some others, it's installed "C:\Users\desktop\TEETH" or "C:\Users\My Documents\TEETH"

I'm thinking of two solutions 1. a way that Excel can look through the C:\ directory relatively quickly, find the folder then output the path it in a cell 2. let the user find the folder, then dump the path to a cell

Are the two ways above possible? Or is there a better way?

Thanks!

Upvotes: 1

Views: 5936

Answers (2)

matellan
matellan

Reputation: 66

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

Upvotes: 0

user6432984
user6432984

Reputation:

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.

enter image description here

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.

Upvotes: 1

Related Questions