使用SQL查询结果填充Excel表格字段时触发Error #1004「应用程序定义或对象定义错误」求助

各位好，我现在卡在一个Excel VBA函数的问题上，想请教下大家的思路。

我的需求是：写一个自定义函数，当在单元格调用它时，根据传入的DocID从SQL数据库查询对应的4个PDF元数据字段，填充到当前行的指定列里。

目前遇到的诡异情况：

• SQL查询本身是正常的，返回的Recordset确实有1条记录、4个字段，用MsgBox能正常弹出字段拼接后的完整内容
• 已经确认vRow正确指向了函数所在的行，测试时也能正常读取上一行的测试字段值
• 但只要尝试把查询结果写入单元格，就会报错：
  1. 用wsFix.Range("G" & vRow).CopyFromRecordset recs时，触发**-2147467259：自动化错误（未指定错误）**
  2. 改成把4个字段拼接成字符串，用wsFix.Range("G" & vRow).Value = vx写入时，触发Error #1004：应用程序定义或对象定义错误

我已经排查过的点：

• 数据库连接正常，查询语句能正确返回预期的元数据字段
• 验证了vRow的取值完全正确，确实指向函数所在的单元格行
• 用MsgBox输出过拼接后的字符串vx，内容完全符合预期，没有乱码或格式问题

以下是我的完整代码：

Function FillDD(DocID As String) As String
    '----work in progress
    Dim wbMacro As Workbook, wsFix As Worksheet
    Dim conn As ADODB.Connection, recs As ADODB.Recordset
    Dim vTestField As String
    Dim vx As String, vServer As String, vDatabase As String, vQuery As String, vRow As Long
    
    Set conn = New ADODB.Connection
    Set recs = New ADODB.Recordset
    
    vx = ActiveWorkbook.Name
    Set wbMacro = Workbooks(vx)
    Set wsFix = wbMacro.Sheets("DocFixes")
    
    'This sets vRow to the row the function is being called from
    vRow = Application.ThisCell.Row
    
    'This grabs the value of the prior row: In testing confirmed it returned the expected value
    vTestField = wsFix.Range("G" & vRow - 1).Value
    
    vServer = "<Server>"
    vDatabase = "<DB>"
    
    'Query returns four fields related to the DocID
    vQuery = "<SELECT & FROM clauses WHERE DocumentID = '" & DocID & "'"
    
    On Error GoTo EH
    vQuery = "SET NOCOUNT ON " & Replace(Replace(vQuery, Chr(13), ""), Chr(10), " ")
    conn.ConnectionString = "DRIVER=SQL Server;SERVER=" & vServer & ";Trusted_Connection=Yes;APP=Microsoft Office 2016;DATABASE=" & vDatabase
    conn.Open
    
    recs.CursorType = adOpenKeyset
    recs.Open vQuery, conn
    
EH:
    If 0 < Len(Err.Description) Then
        MsgBox "Error#: " & Err.Number & " Description: " & Err.Description
    End If
    
    'wsFix.Range("G" & vRow).CopyFromRecordset recs <-- '-2147467259': Automation error: Unspecified error
    
    vTestField = recs.Fields.Item(0).Value '<-- returned expected value in testing
    If Len(vTestField) = 9 Then
        Dim vxStr As String
        vxStr = "'" & vTestField
        vTestField = recs.Fields.Item(1).Value
        vxStr = vxStr & ",'" & vTestField
        vTestField = recs.Fields.Item(2).Value
        vxStr = vxStr & ",'" & vTestField
        vTestField = recs.Fields.Item(3).Value
        vxStr = vxStr & ",'" & vTestField
        
        'MsgBox worked perfectly, showed the correct concatenated string
        'MsgBox vxStr
        
        'Next statement causes FillDD to fail on Error 1004 "Application-defined or Object-defined error"
        wsFix.Range("G" & vRow).Value = vxStr
        'wsFix.Range("H" & vRow).Value = recs.Fields.Item(1).Value
        'wsFix.Range("I" & vRow).Value = recs.Fields.Item(2).Value
        'wsFix.Range("J" & vRow).Value = recs.Fields.Item(3).Value
        
        FillDD = "Success"
    Else
        FillDD = "Fail"
    End If
    
    recs.Close
    conn.Close
    Set conn = Nothing
    Set recs = Nothing
End Function

想请教下大家，这种在自定义函数里写入单元格就报错的情况，可能是什么原因导致的？有没有什么可行的解决思路？

内容来源于stack exchange