Hey folks!
I've made a listbox populated with xlsx files from a specific directory. Inside these excel files are informations about users (name, address, phone numbers, etc.) 9 cells total (A1 to A9).
Let's say the folder which the xlsx files are stored contains about 500 files. Every file looks the same.
My questions are:
How can I search these files (filename) with instant update in listbox? (f.e. if I type the letter 'J' into the searchbox, John will be the only one on the list.)
and
How can I populate cells in Sheet4 (A1 to A9) from a file on the list? (Basically pulling information about John from his xlsx file for example)
Here's what I'm working on.
Here's the code for the listbox.
Thank you for your time.
I've made a listbox populated with xlsx files from a specific directory. Inside these excel files are informations about users (name, address, phone numbers, etc.) 9 cells total (A1 to A9).
Let's say the folder which the xlsx files are stored contains about 500 files. Every file looks the same.
My questions are:
How can I search these files (filename) with instant update in listbox? (f.e. if I type the letter 'J' into the searchbox, John will be the only one on the list.)
and
How can I populate cells in Sheet4 (A1 to A9) from a file on the list? (Basically pulling information about John from his xlsx file for example)
Here's what I'm working on.
Here's the code for the listbox.
Code:
Private Sub button_load_Click()
[COLOR=#008000]'Code not found 404 :)[/COLOR]
End Sub
Private Sub UserForm_Initialize()
Dim fileList() As String
Dim fName As String
Dim fPath As String
Dim I As Integer
[COLOR=#008000] 'define the directory to be searched for files[/COLOR]
fPath = "C:\Users\Einar Þór\Desktop\Jötunn_excel\_kaupendur\"
[COLOR=#008000] 'build a list of the files[/COLOR]
fName = Dir(fPath)
While fName <> ""
[COLOR=#008000] 'add fName to the list[/COLOR]
I = I + 1
ReDim Preserve fileList(1 To I)
fileList(I) = fName
[COLOR=#008000] 'get next filename[/COLOR]
fName = Dir()
Wend
[COLOR=#008000] 'see if any files were found[/COLOR]
If I = 0 Then
MsgBox "No files found"
Exit Sub
End If
[COLOR=#008000] 'cycle through the list and add to listbox[/COLOR]
For I = 1 To UBound(fileList)
Me.ListBox1.AddItem fileList(I)
Next
End Sub
Thank you for your time.