如何修改代码获取文件带毫秒的最后修改日期？

阿华AIGC实验室

2026-5-22

获取文件修改时间的毫秒值（Excel VBA解决方案）

嘿，我太懂你这种明明知道文件的修改时间包含毫秒，但用常规方法拿到的总是0的憋屈了！问题出在VBA的基础机制上：内置的FileDateTime函数返回的是Date类型，而这个类型本身只精确到秒，根本不会保留毫秒信息。下面给你两种实用的修改方案，帮你把毫秒值捞出来：

方法一：用FileSystemObject直接获取（简单易上手）

这种方法不需要调用复杂的API，借助Scripting.FileSystemObject就能直接拿到带毫秒的时间字符串，设置好单元格格式就能显示：

Sub GetFileTimesWithMilliseconds()
    Dim fso As Object
    Dim targetFolder As Object
    Dim fileObj As Object
    Dim outputSheet As Worksheet
    Dim currentRow As Long
    
    ' 让用户选择目标文件夹
    With Application.FileDialog(msoFileDialogFolderPicker)
        If .Show <> -1 Then Exit Sub
        Set targetFolder = CreateObject("Scripting.FileSystemObject").GetFolder(.SelectedItems(1))
    End With
    
    ' 指定输出的工作表（改成你实际用的表名）
    Set outputSheet = ThisWorkbook.Sheets("Sheet1")
    currentRow = 2 ' 从第二行开始写数据，第一行放表头
    
    ' 写入表头
    outputSheet.Cells(1, 1).Value = "文件名"
    outputSheet.Cells(1, 2).Value = "最后修改时间（含毫秒）"
    
    Set fso = CreateObject("Scripting.FileSystemObject")
    
    ' 遍历文件夹里的所有文件
    For Each fileObj In targetFolder.Files
        ' 这里拿到的是带毫秒的字符串，比如"2024/5/20 16:42:35.789"
        Dim fullModifiedTime As String
        fullModifiedTime = fileObj.DateLastModified
        
        ' 写入单元格
        outputSheet.Cells(currentRow, 1).Value = fileObj.Name
        outputSheet.Cells(currentRow, 2).Value = fullModifiedTime
        ' 关键：设置单元格格式，确保毫秒显示出来
        outputSheet.Cells(currentRow, 2).NumberFormat = "yyyy/mm/dd hh:mm:ss.000"
        
        currentRow = currentRow + 1
    Next fileObj
    
    MsgBox "搞定！文件时间已经提取完成", vbInformation
End Sub

划重点：

fileObj.DateLastModified返回的是字符串类型的时间，自带毫秒，千万别把它转成Date类型（一转毫秒就丢了）
一定要设置单元格的数字格式为yyyy/mm/dd hh:mm:ss.000，不然即使有毫秒值也会被隐藏

方法二：用Windows API获取高精度时间（进阶方案）

如果需要对时间做数值计算，或者追求更底层的控制，可以调用Windows API来获取文件的原始时间戳，再转换成带毫秒的格式：

' 先声明需要用到的API函数和结构体
Private Declare PtrSafe Function FindFirstFile Lib "kernel32" Alias "FindFirstFileA" _
    (ByVal lpFileName As String, lpFindFileData As WIN32_FIND_DATA) As LongPtr
Private Declare PtrSafe Function FindNextFile Lib "kernel32" Alias "FindNextFileA" _
    (ByVal hFindFile As LongPtr, lpFindFileData As WIN32_FIND_DATA) As Long
Private Declare PtrSafe Function FindClose Lib "kernel32" _
    (ByVal hFindFile As LongPtr) As Long
Private Declare PtrSafe Function FileTimeToSystemTime Lib "kernel32" _
    (lpFileTime As FILETIME, lpSystemTime As SYSTEMTIME) As Long

Private Type FILETIME
    dwLowDateTime As Long
    dwHighDateTime As Long
End Type

Private Type SYSTEMTIME
    wYear As Integer
    wMonth As Integer
    wDayOfWeek As Integer
    wDay As Integer
    wHour As Integer
    wMinute As Integer
    wSecond As Integer
    wMilliseconds As Integer ' 这里就是我们要的毫秒值！
End Type

Private Type WIN32_FIND_DATA
    dwFileAttributes As Long
    ftCreationTime As FILETIME
    ftLastAccessTime As FILETIME
    ftLastWriteTime As FILETIME
    nFileSizeHigh As Long
    nFileSizeLow As Long
    dwReserved0 As Long
    dwReserved1 As Long
    cFileName As String * 260
    cAlternate As String * 14
End Type

Sub GetFileTimesWithMilliseconds_API()
    Dim folderPath As String
    Dim findData As WIN32_FIND_DATA
    Dim findHandle As LongPtr
    Dim systemTime As SYSTEMTIME
    Dim outputSheet As Worksheet
    Dim currentRow As Long
    
    ' 选择目标文件夹
    With Application.FileDialog(msoFileDialogFolderPicker)
        If .Show <> -1 Then Exit Sub
        folderPath = .SelectedItems(1) & "\*.*"
    End With
    
    Set outputSheet = ThisWorkbook.Sheets("Sheet1")
    currentRow = 2
    outputSheet.Cells(1, 1).Value = "文件名"
    outputSheet.Cells(1, 2).Value = "最后修改时间（含毫秒）"
    
    ' 开始查找文件
    findHandle = FindFirstFile(folderPath, findData)
    If findHandle <> -1 Then
        Do
            ' 跳过子文件夹，只处理文件
            If (findData.dwFileAttributes And vbDirectory) = 0 Then
                ' 把文件时间转换成系统时间（包含毫秒）
                FileTimeToSystemTime findData.ftLastWriteTime, systemTime
                
                ' 拼接成带毫秒的时间字符串
                Dim fullModifiedTime As String
                fullModifiedTime = systemTime.wYear & "/" & _
                               Format(systemTime.wMonth, "00") & "/" & _
                               Format(systemTime.wDay, "00") & " " & _
                               Format(systemTime.wHour, "00") & ":" & _
                               Format(systemTime.wMinute, "00") & ":" & _
                               Format(systemTime.wSecond, "00") & "." & _
                               Format(systemTime.wMilliseconds, "000")
                
                ' 写入单元格并设置格式
                outputSheet.Cells(currentRow, 1).Value = Left(findData.cFileName, InStr(findData.cFileName, vbNullChar) - 1)
                outputSheet.Cells(currentRow, 2).Value = fullModifiedTime
                outputSheet.Cells(currentRow, 2).NumberFormat = "yyyy/mm/dd hh:mm:ss.000"
                
                currentRow = currentRow + 1
            End If
        Loop While FindNextFile(findHandle, findData) ' 遍历所有文件
        FindClose findHandle ' 关闭查找句柄
    End If
    
    MsgBox "高精度时间提取完成！", vbInformation
End Sub

这种方法能直接拿到系统底层的毫秒值，适合对时间精度要求高的场景，不过代码相对复杂一些。