View Full Version : List Folder Based On Search Word

05-10-2013, 10:45 AM
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

05-10-2013, 12:40 PM
I can't see what you tried ??

05-10-2013, 01:40 PM