Populate Access ListBox column with data from another columm-CodePudding

I have an unbound ListBox with 1 column that is populated onl oad with a list of files in a folder. The user can click on any of the lines in the list box which opens the file. This works as the entire filepath is what is in the ListBox.

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 (0) column as the bound column but hidden and have a second column (1) that the user actually sees that displays only the filename.

The code below works nicely to pull the filename from the filepath and put it in a message box. 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

EDIT with additional info:

The ListBox RowSource is set to Value List and the list is filled by calling on the following function on loading the form (credit to Allen Browne, http://www.allenbrowne.com/ser-59.html)

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

CodePudding user response：

The ListBox.AddItem method documentation explains you can supply values for multiple list box columns as a string delimited with semicolons. So, if you want two columns with the full path (varItem) in the first column and just the file name (Dir(varItem)) in the second, you can alter your ListFiles function like this ...

For Each varItem In colDirList
    'lst.AddItem varItem
    lst.AddItem varItem & ";" & Dir(varItem)
Next