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.
[vba]
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
Loop
'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
[/vba]
