Results 1 to 3 of 3

Thread: List Folder Based On Search Word

  1. #1

    List Folder Based On Search Word

    Hey guys,

    I am trying to write some VBA code that will search for folders in a specified location that contain a certain keyword in their name and list the folders in a listbox. Then I want to allow the user to select a folder from that listbox to tell excel where to look for a specific excel file that will be there to open/write. Anyone have any ideas?

    Thanks guys!

    This is the code I found. This lists the files and not the folders only. It still needs a way to search the array or something to pick out folders that match a keyword.

    Private Sub CommandButton4_Click()
    Dim MyFile As String
    Dim Counter As Long
    Dim path As String

    'Create a dynamic array variable, and then declare its initial size
    Dim DirectoryListArray() As String
    ReDim DirectoryListArray(1000)
    'Loop through all the files in the directory by using Dir$ function
    path = TextBox1.Text
    MyFile = Dir$(path)
    Do While MyFile <> ""
    DirectoryListArray(Counter) = MyFile
    MyFile = Dir$
    Counter = Counter + 1
    'Reset the size of the array without losing its values by using Redim Preserve
    ReDim Preserve DirectoryListArray(Counter - 1)
    'To prove it worked you could run the following:
    'For Counter = 0 To UBound(DirectoryListArray)
    'Debug.Print writes the results to the Immediate window (press Ctrl + G to view it)'
    ' Debug.Print DirectoryListArray(Counter)
    'Next Counter
    'To populate a Listbox from the array you could use:
    ListBox1.List = DirectoryListArray

    End Sub

    Last edited by notetonote; 05-10-2013 at 01:45 PM.

  2. #2
    Knowledge Base Approver VBAX Guru
    Apr 2012
    I can't see what you tried ??

  3. #3

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts