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

[einargud]

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.

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.

 

[Norie]

The OP did kind of say they wanted an 'instant' update as they typed in the textbox.

 

[einargud]

Download sample workbook:

Zippyshare.com - UserFormFilter.xlsb

This works perfectly! And it's set up exactly like mine. But mine does not work at all!

Is it the file type? Binary instead of macro-enabled? or am I overlooking some dumb typo?

As Norie pointed out, putting _Change instead of _BeforeUpdate with your code worked really well and is exactly like how I want it to be.

 

[VBA Geek]

The OP did kind of say they wanted an 'instant' update as they typed in the textbox.

if he doesn't have a huge array to filter (considering it is a list of files, it shouldn't be), he should use Change Event if he likes the "instant update" experience

 

[VBA Geek]

the different file extension shouldn't be the cause. There has to be an issue with the original code, maybe you didn't declare the fileList array at the module level?

This works perfectly! And it's set up exactly like mine. But mine does not work at all!

Is it the file type? Binary instead of macro-enabled? or am I overlooking some dumb typo?

As Norie pointed out, putting _Change instead of _BeforeUpdate with your code worked really well and is exactly like how I want it to be.

 

[Norie]

VBA_Geek

What's the problem if there's a large array?

That shouldn't affect the filter.

 

[VBA Geek]

the Filter Function is called at every letter you type into or delete from the textbox, i do love the instant updating experience but from an efficiency point of view i prefer the other event as it calls the Filter just one time

 

[einargud]

This is weird. I think I've checked for everything, yet, this error comes up:

This is highlighted:

Any ideas?

ps. Any thoughts on the second question? importing data from those listBox files?

 

[VBA Geek]

you get this compile error because there is no reference added to the Microsoft Scripting RunTime, go to Tools > References and add it. this error should then disappear

 

[VBA Geek]

ps. Any thoughts on the second question? importing data from those listBox files?

are you planning to add a command button for this or how should it work?

 

[Norie]

VBA Geek

I know that every time you type/delete in the textbox the filter code will be triggered, but that code executes almost instantaneously so I'm not sure about there being a problem with efficiency.

Also, to trigger the BeforeUpdate code you either need to press enter or move to another control, and when you do that the focus moves to either the next control in the tab order or the control you moved to.