Populate a ListBox column based on contents in another (1 Viewer)

[akbrennan]

I have an unbound ListBox with 1 column that is populated OnLoad with a list of files in a folder. The user can click on any of the lines in the list box which opens the related file. This works as the entire filepath is what is stored in the ListBox and passed to Windows Shell.
I would like the listbox to display only the filenames, but still be able to follow a full link on clicking. My thoughts were to change the ListBox to 2 column and have the 1st column as the bound column but hidden and have a second column that the user actually sees that displays only the filename.
The code below works nicely to pull the filename from the filepath for an individual file. Is there a way to apply this to all the "rows" in the list so it populates the 2nd column with the filenames from the first?

Thanks in advance.

Dim sPath As String
Dim sFile As String

sPath = FileList.Column(0)
sFile = Right(sPath, Len(sPath) - InStrRev(sPath, "\"))
MsgBox sFile

 

[Gasman]

No need, execute the code when a selection is made?
No need for bound either I would have thought?

 

[akbrennan]

Hi Gasman, thanks for the reply.

You're correct, I don't need to do it but from an aesthetic and practical point of view having the listbox only display filenames is neater and easier to read and allows me to have a narrower listbox with a neater form. The full file paths are long and present a lot of text for the user to parse.

The ListBox is unbound, as it is populated every time the form is opened using the following Module which I call on opening the Form...

Public Function ListFiles(strPath As String, Optional strFileSpec As String, _
    Optional bIncludeSubfolders As Boolean, Optional lst As ListBox)
On Error GoTo Err_Handler
    'Purpose:   List the files in the path.
    'Arguments: strPath = the path to search.
    '           strFileSpec = "*.*" unless you specify differently.
    '           bIncludeSubfolders: If True, returns results from subdirectories of strPath as well.
    '           lst: if you pass in a list box, items are added to it. If not, files are listed to immediate window.
    '               The list box must have its Row Source Type property set to Value List.
    'Method:    FilDir() adds items to a collection, calling itself recursively for subfolders.
    Dim colDirList As New Collection
    Dim varItem As Variant
    
    Call FillDir(colDirList, strPath, strFileSpec, bIncludeSubfolders)
    
    'Add the files to a list box if one was passed in. Otherwise list to the Immediate Window.
    If lst Is Nothing Then
        For Each varItem In colDirList
            Debug.Print varItem
        Next
    Else
        For Each varItem In colDirList
        lst.AddItem varItem
        Next
    End If


Exit_Handler:
    Exit Function


Err_Handler:
    MsgBox "Error " & Err.Number & ": " & Err.Description
    Resume Exit_Handler
End Function


Private Function FillDir(colDirList As Collection, ByVal strFolder As String, strFileSpec As String, _
    bIncludeSubfolders As Boolean)
    'Build up a list of files, and then add add to this list, any additional folders
    Dim strTemp As String
    Dim colFolders As New Collection
    Dim vFolderName As Variant


    'Add the files to the folder.
    strFolder = TrailingSlash(strFolder)
    strTemp = Dir(strFolder & strFileSpec)
    Do While strTemp <> vbNullString
        colDirList.Add strFolder & strTemp
        strTemp = Dir
    Loop


    If bIncludeSubfolders Then
        'Build collection of additional subfolders.
        strTemp = Dir(strFolder, vbDirectory)
        Do While strTemp <> vbNullString
            If (strTemp <> ".") And (strTemp <> "..") Then
                If (GetAttr(strFolder & strTemp) And vbDirectory) <> 0& Then
                    colFolders.Add strTemp
                End If
            End If
            strTemp = Dir
        Loop
        'Call function recursively for each subfolder.
        For Each vFolderName In colFolders
            Call FillDir(colDirList, strFolder & TrailingSlash(vFolderName), strFileSpec, True)
        Next vFolderName
    End If
End Function


Public Function TrailingSlash(varIn As Variant) As String
    If Len(varIn) > 0& Then
        If Right(varIn, 1&) = "\" Then
            TrailingSlash = varIn
        Else
            TrailingSlash = varIn & "\"
        End If
    End If
End Function

 

[Gasman]

Well I was just thinking of one column with the filename stripped off, and the other column the full filepath.?
Then when any row is selected you refer to that second column.

I have never worked with collections either.
Nice code though.

 

[akbrennan]

Gasman, thanks again. Yes, that would be the sort of solution I'd be looking for. Can you think of a way I could achieve that?

The Module I used to generate the list isn't my own work, it's from Allen Browne. I'll edit the earlier post to add the link.
Presumably the neatest way to get a 2 column listbox with the information as desired would be to change how the Module itself works rather than do it after the fact, but I wouldn't know where to begin with that.

 

[Gasman]

Just split the filename off from the full path, leaving the full path intact, then add that as your other column. Whether you have it first or not is a matter of preference I would have thought.

 

[akbrennan]

I've found the part of the Module that actually populates the list...

Dim colDirList As New Collection
    Dim varItem As Variant
        
    Call FillDir(colDirList, strPath, strFileSpec, bIncludeSubfolders)
    
    'Add the files to a list box if one was passed in. Otherwise list to the Immediate Window.
    If lst Is Nothing Then
        For Each varItem In colDirList
            Debug.Print varItem
        Next
Else
   For Each varItem In colDirList
   lst.AddItem varItem
   Next
End If

And the following alteration works to add "test" into the second column of the listbox for every entry...

Else
    For Each varItem In colDirList
    lst.AddItem varItem & ";" & "test"
    Next
End If

I've tried to pass a string to the second column with the code below but it doesn't work.

Dim colDirList As New Collection
    Dim varItem As Variant
    Dim filename As String
        
    Call FillDir(colDirList, strPath, strFileSpec, bIncludeSubfolders)
    
    'Add the files to a list box if one was passed in. Otherwise list to the Immediate Window.
    If lst Is Nothing Then
        For Each varItem In colDirList
            Debug.Print varItem
        Next
    Else
        filename = Right(varItem, Len(varItem) - InStrRev(varItem, "\"))
        For Each varItem In colDirList
        lst.AddItem varItem & ";" & filename
        Next
    End If

I don't have the VBA knowledge to work out this one myself. Any ideas on what to try next? Thank you!

 

[Gasman]

Well firstly, does the listbox have the second column in properties?
Add that code for when no listbox is passed in and see what is output.

 

[Gasman]

Look at how this link says it should be done.
https://bettersolutions.com/vba/controls/listbox.htm
Yet MS say this way, which is what you have used?
https://docs.microsoft.com/en-us/office/vba/api/access.listbox.additem
I also think your filename construction should be within the for loop?

 

[Gasman]

OK, we need an expert here.
I used your code on a new form and confirmed with F8 that the data was being added correctly to the list.
I also added a debug.print of lst.Column(0,iIndex) in the loop, which showed Null each time.

YET, when I went back to the form, I could see the list populated, yet when the function is complete and I exit it (breakpoint on last End If, the data has gone?

So no clue as to what is going wrong. I must admit I have rarely used Value Lists. The last was for Male;Female, so it shows how long ago that was

 

[arnelgp]

what i did is create two listbox.
see the code on Load event of the form.

 

[Gasman]

what i did is create two listbox.
see the code on Load event of the form.

Too late a version for me Arnel. The O/P must have a later version however.

 

[akbrennan]

Thank you Arnel. And thanks again Gasman for the ongoing input. That two listbox solution works nicely. I had posted the problem elsewhere also, and someone pointed out that the code I was trying to use to pull the filename from the full path was unnecessarily clunky and suggested a neat alternative...

Original...

Dim sPath As String
Dim sFile As String

sPath = FileList.Column(0)
sFile = Right(sPath, Len(sPath) - InStrRev(sPath, "\"))

Improved...

sFile = dir(sPath)

Taking that to the function code in the module (and thanks again to Gasman for the link to more information on code for ListBoxs) produced the following with worked perfectly to produce a 2nd column with just the filename...

Else
    For Each varItem In colDirList
    lst.AddItem varItem & ";" & Dir(varItem)
    Next
End If

Once that was working it was just a case of adding a line to the OnLoad of the form to hide the 1st column (making sure it was still the bound column so the full path is followed on clicking).

Me.FileList.ColumnWidths = ("0cm")

Thanks again for all the help!

 

[Gasman]

Clunky or not, it was still getting the correct value. What I could not find out is why the list was not visible, despit being populated, or so it seemed.

Edit: You did not have ByVal for the listbox.
Adding that then displays the data as you would expect.

 

[akbrennan]

Shall we take it as a win-win? I've got a workable solution for the problem, and you've had a bit of a head scratcher to challenge your knowledge?

Thanks again for engaging, it was really helpful.

 

[Gasman]

Headscratcher?
Spent a few hours trying to work out why it was not working.

 

[Pat Hartman]

The on Load event of a form runs ONCE when the form opens so it is not appropriate for working with data, especially cascading combos. A much better place is the form's Current event which runs every time the form moves to a new record. Make sure your code takes into account landing on a NewRecord.

 

[akbrennan]

Good point Pat. Currently my database design doesn't allow users to navigate from one record to another within the form in question, they have to close the form and reopen from another form, so I haven't encountered a problem. But as a general principle that's helpful to know. Thank you.

 

[Pat Hartman]

You're welcome
When some method works 100% of the time and a different method works some of the time, it is probably best to use the 100% method all the time even though THIS time the sometimes method works That will save you a lot of angst over time.