Unexpected result while populating a listbox

[Salty Mariner]

I am using the code below to populate a listbox. the general idea is to populate the listbox with the path in column(0) and the file name in Column(1) Column (0) will remain hidden and I will use follow hyperlink to open the file.

'------------------------------------------------------------
' SUBROUTINE NAME: GetFilePathNames()
' PORPOSE: populate a llistbox withe the path and file names
' in a folder
' REQUIRES: A reference to the Microsoft Scripting RunTime
' CREATED BY: Andrew Dulavitz   (adulavitz@hotmail.com)
' DATE: January 2024
'-------------------------------------------------------------
Private Sub GetFilePathNames()
On Error GoTo Err_Handler




    Dim strFolderPath As String
    Dim fso As Object ' FileSystemObject
    Dim fld As Object ' Folder
    Dim fil As Object ' File
    Dim i As Integer  ' Counter
    
    strFolderPath = Me.cboDocumentFolder.Column(2)
    Me.lstFolderDocs.RowSource = "" ' Clear any previous list items
    If strFolderPath = "" Then Exit Sub
    
    Set fso = CreateObject("Scripting.FileSystemObject")
    Set fld = fso.GetFolder(strFolderPath)
    
    i = 0 'Initialize the counter
    For Each fil In fld.Files 'Loop through the files in the folder
        Me.lstFolderDocs.AddItem fil.Path & ";" & fil.Name 'Add the file path and name to the list box
        i = i + 1 'Increment the counter
    Next fil
    
    'Me.lstFolderDocs.ListIndex = 0  ' Select the first item in the listbox




Exit_Handler:
    Exit Sub




Err_Handler:
    clsErrorHandler.HandleError "frmCrewInfo", "GetFilePathNames"
        Resume Exit_Handler
End Sub

Everything seems to work as it should until the end result is displayed.






Note the text highlighted in Yellow. It has somehow bled over from the path. This is strange because if I pause he code and type "? fil.Name" in the immediate window I get only the file name. Likewise if I replace fil.Path with some random text string I get to bleed over from that text string. This seems like it should be pretty straightforward but I am quite baffled by this behavior.


Any ideas on what could be going on here?

[Welshgasman]

The comma?

[Salty Mariner]

The comma?

There is a semicolon to separate the data for the columns but no comma.

Me.lstFolderDocs.AddItem fil.Path & ";" & fil.Name

Strange because it's only carrying over a fragment of the path.

[Welshgasman]

There is a comma in the file path?

[moke123]

Note in your comments you state requires MS Scripting runtime however you are late binding the FSO so it isn't required.

It appears the comma in the path may have something to do with it. Try delimiting the fil.path with quotes.

in my test, without quotes, the path ends at the comma. Delimiting it corrects that.

    For Each fil In fol.Files
        lbx.AddItem fil.Name & ";""" & fil.Path & """"
    Next fil

note I have file name first then file path

in your case you may need

lbx.AddItem Chr(34) & fil.Path & Chr(34) & ";" & fil.Name

[Salty Mariner]

There is a comma in the file path?

My apologies Welshgasman you were quite correct. I was naming the folders in the format of [LastName] & ", " & [FirstName] & " " & [MiddileInit] doing this in a query and was creating the folder in Code. It never occured to me to look at my naming convention for folders. Easy enough fix we can just use an underscore between LastName and FirstName.

Tested and it works as intended. Now to go fix those queries.

Thank you so much. Problem solved!