Excel: Excel VBA - Listbox populated with file names - Search and import


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. [IMG]http://i.imgur.com/tRm5w5T.jpg[/IMG] Here's the code for the listbox.

Private Sub button_load_Click()
'Code not found 404 :)
End Sub
Private Sub UserForm_Initialize()
    Dim fileList() As String
    Dim fName As String
    Dim fPath As String
    Dim I As Integer
     'define the directory to be searched for files
    fPath = "C:\Users\Einar Þór\Desktop\Jötunn_excel\_kaupendur\"
     
     'build a list of the files
    fName = Dir(fPath)
    While fName <> ""
         'add fName to the list
        I = I + 1
        ReDim Preserve fileList(1 To I)
        fileList(I) = fName
         'get next filename
        fName = Dir()
    Wend
     'see if any files were found
    If I = 0 Then
        MsgBox "No files found"
        Exit Sub
    End If
     'cycle through the list and add to listbox
    For I = 1 To UBound(fileList)
        Me.ListBox1.AddItem fileList(I)
    Next
End Sub
Thank you for your time.


This question generated 26 answers. To proceed to the answers, click here.

This thread is current as of June 22, 2014.


For more resources for Microsoft Excel