如何在VBA的多列ListBox中填充文件夹内文件的文件名与修改日期？

Got it, let's tweak your existing code to make that two-column ListBox work properly—showing both filenames and their last modified dates. Here's the revised version with clear explanations:

Quick Setup & Key Changes #

First, we need to configure the ListBox to support two columns, then fetch the modified date for each file using VBA's built-in FileDateTime() function. We'll also make sure the code handles edge cases like missing files or incomplete folder paths.

Revised Full Code #

Sub PopulateTwoColumnFileList()
    Dim FilePath As String
    Dim FileName As String
    Dim fileCounter As Integer
    
    ' Replace this with your actual folder path
    FilePath = "C:\Your\Target\Folder\"
    
    ' Ensure the folder path ends with a backslash to avoid errors
    If Right(FilePath, 1) <> "\" Then
        FilePath = FilePath & "\"
    End If
    
    ' Clear existing items from the ListBox
    Me.ListBox1.Clear
    
    ' Configure ListBox for two columns
    With Me.ListBox1
        .ColumnCount = 2
        .ColumnWidths = "180;150" ' Adjust these values to fit your form (twips)
        .ColumnHeads = True ' Optional: enable if you want column headers
        ' Set header text if ColumnHeads is enabled
        .List = Array(Array("File Name", "Date Modified"))
    End With
    
    ' Start fetching files from the folder
    FileName = Dir(FilePath & "*.*")
    fileCounter = 0
    
    While FileName <> ""
        fileCounter = fileCounter + 1
        
        ' Add filename to the first column
        Me.ListBox1.AddItem FileName
        ' Add modified date to the second column (columns are zero-indexed)
        Me.ListBox1.List(fileCounter, 1) = FileDateTime(FilePath & FileName)
        
        ' Grab the next file in the folder
        FileName = Dir()
    Wend
    
    ' Handle case where no files are found
    If fileCounter = 0 Then
        MsgBox "No files found in the specified folder!"
        Exit Sub
    End If
End Sub

Important Notes #

• Folder Path Check: The code adds a backslash to the end of FilePath if it's missing—this prevents errors when combining the path with filenames.
• Column Customization: Adjust the ColumnWidths values to match your form's size (1 twip = 1/1440 of an inch).
• Date Format: If you want a specific date/time format (like yyyy-mm-dd hh:mm:ss), replace the FileDateTime() line with:

  Me.ListBox1.List(fileCounter, 1) = Format(FileDateTime(FilePath & FileName), "yyyy-mm-dd hh:mm:ss")
  

• Column Headers: If you don't want headers, just set .ColumnHeads = False and remove the .List = Array(...) line.

内容的提问来源于stack exchange，提问作者havishmad