如何使用TransferSpreadsheet将源文件的文件名添加到每行的第一个单元格？

Function GetFileName(filePath As String) As String
    Dim fso As Object
    Set fso = CreateObject("Scripting.FileSystemObject")
    GetFileName = fso.GetFileName(filePath)
End Function

Sub AddFileNameToFirstCell()
    Dim db As Database
    Dim rs As Recordset
    Dim fileName As String
    
    Set db = CurrentDb
    Set rs = db.OpenRecordset("目标表名")
    
    ' 循环遍历每一行
    Do Until rs.EOF
        ' 获取文件名
        fileName = GetFileName("源文件路径")
        
        ' 将文件名添加到每行的第一个单元格
        rs.Edit
        rs.Fields(0).Value = fileName
        rs.Update
        
        rs.MoveNext
    Loop
    
    rs.Close
    Set rs = Nothing
    Set db = Nothing
End Sub