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

einargud · Jun 22, 2014

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.

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.

VBA Geek · Jun 22, 2014

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.)

I am guessing your searchbox is a TextBox. then try this:

Code:

Option Explicit
Private fileList() As Variant

Private Sub TextBox1_BeforeUpdate(ByVal Cancel As MSForms.ReturnBoolean)
    With Me.TextBox1
        If .Value = vbNullString Then
            Me.ListBox1.List = fileList
        Else
            Me.ListBox1.List = Filter(SourceArray:=fileList, Match:=.Value, Compare:=vbTextCompare)
        End If
    End With
End Sub

Private Sub UserForm_Initialize()
    '<======= AFTER HAVING POPULATED YOUR fileList ARRAY WITH FILENAME DO THIS: ==========>
    With UserForm1.ListBox1
        .List = fileList
    End With
End Sub

You can also avoid using Redim Preserve everytime you add a filename to your array by simply giving it a big enough size at the beginning by counting how many files there are in your directory

fPath = "C:\Users\Einar Þór\Desktop\Jötunn_excel\_kaupendur\"
NumberOfFiles = Createobject("Scripting.FileSystemObject").getfolder(fPath).files.count
Redim fileList(1 to NumberOfFiles)
</pre>

Norie · Jun 22, 2014

Try it like this.

Code:

Private fileList() As Variant

Private Sub TextBox1_Change()
    With Me.TextBox1
        If .Value = vbNullString Then
            Me.ListBox1.List = fileList
        Else
            Me.ListBox1.List = Filter(SourceArray:=fileList, Match:=.Value, Compare:=vbTextCompare)
        End If
    End With
End Sub

Private Sub UserForm_Initialize()

    ' code to populate listbox

    fileList = Application.Transpose(ListBox1.List)
    
End Sub

einargud · Jun 22, 2014

Hey VBA Geek & Norie!

Thank you so much for your help, however.. I'm having trouble with both codes. I'm a complete newbie with coding

The first part of both codes.. "Private fileList() As Variant" is confusing me. Where would this be placed?

Norie · Jun 22, 2014

The whole code would be in the userform module, the declaration for fileList would be at the top of that module.

einargud · Jun 22, 2014

Hey Norie.

Could you please insert it into my code.

Code:

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.box_listi.AddItem fileList(I)
    Next






End Sub








Private Sub button_Close_Click()
Unload Me


End Sub

einargud · Jun 22, 2014

Could someone please help me implement Norie's or VBA Geek's code into mine (posted above)

I'm a total beginner at coding and I've only made it this far by google'ing and reading this amazing forum.

Thank you,
Einar

VBA Geek · Jun 22, 2014

Download sample workbook:

Zippyshare.com - UserFormFilter.xlsb

Code:

Option Explicit
Private fileList() As String
Private Sub TextBox1_BeforeUpdate(ByVal Cancel As MSForms.ReturnBoolean)
    With Me.TextBox1
        If .Value = vbNullString Then
            Me.ListBox1.List = fileList
        Else
            Me.ListBox1.List = Filter(SourceArray:=fileList, Match:=.Value, Compare:=vbTextCompare)
        End If
    End With
End Sub
Private Sub UserForm_Initialize()
Dim lngCnt As Long
Dim oFile As Scripting.File
Const strFolder As String = "C:\desktop\" 
Dim oFso As Scripting.FileSystemObject
Dim lngNumOfFiles As Long
Set oFso = New Scripting.FileSystemObject
With oFso.GetFolder(strFolder)
    If .Files.Count = 0 Then
        MsgBox "No Files in " & strFolder
        Exit Sub
    Else
        ReDim fileList(1 To .Files.Count) As String
            For Each oFile In .Files
                lngCnt = lngCnt + 1
                fileList(lngCnt) = oFile.Name
            Next
    End If
End With
Me.ListBox1.List = fileList
Set oFso = Nothing
End Sub

einargud said:
Could someone please help me implement Norie's or VBA Geek's code into mine (posted above)

I'm a total beginner at coding and I've only made it this far by google'ing and reading this amazing forum.

Thank you,
Einar

Norie · Jun 22, 2014

VBA_Geek

Wouldn't it be better to use the textbox's Change event so the listbox is filtered as you type in the textbox?

VBA Geek · Jun 22, 2014

Yes, depending on someones preferences, that is a good option as well

maybe if einargud has a big array of files though, then the filter function would be called everytime he is typing one single letter, whereas with Before_Update, once he has decided which string he wants to look for, he just needs to press enter after typing the string and the filter function is called just that one time

Norie said:
VBA_Geek

Wouldn't it be better to use the textbox's Change event so the listbox is filtered as you type in the textbox?

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

einargud

New Member

Excel Facts

VBA Geek

MrExcel MVP

Norie

Well-known Member

einargud

New Member

Norie

Well-known Member

einargud

New Member

einargud

New Member

VBA Geek

MrExcel MVP

Norie

Well-known Member

VBA Geek

MrExcel MVP

Similar threads

Forum statistics

Share this page

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

New Member

Excel Facts

MrExcel MVP

Well-known Member

New Member

Well-known Member

New Member

New Member

MrExcel MVP

Well-known Member

MrExcel MVP

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock