VBA代码报错1004：无法成功替换QueryTable问题求助

核心问题原因 #

当工作簿B原有QueryTable行数更多时触发1004错误，本质是原代码的对象处理逻辑存在漏洞：

• 仅删除targetQueryTable，但对应的ListObject可能残留无效结构，导致后续引用ListObjects(1)时出现对象引用错误
• 未彻底清空目标工作表的旧数据和格式，新数据覆盖时，多余的旧行可能与新生成的列表对象产生冲突
• 复制数据后直接调用Refresh，但此时新生成的QueryTable引用可能未正确绑定

修正后的代码 #

Sub ReplaceQueryTableWithRefreshedData()
    Dim currentWorkbook As Workbook
    Dim targetWorkbook As Workbook
    Dim currentListObj As ListObject
    Dim targetListObj As ListObject
    Dim targetWorksheet As Worksheet
    
    ' 关闭屏幕更新，提升效率并避免交互问题
    Application.ScreenUpdating = False
    
    ' 目标文件路径和工作表名
    Const targetWorkbookPath As String = "C:\Users\notgoingtoshow.xlsm"
    Const targetWorksheetName As String = "qry_ABB"
    
    ' 绑定当前工作簿和列表对象
    Set currentWorkbook = ThisWorkbook
    Set currentListObj = currentWorkbook.Worksheets("qry_ABB").ListObjects(1)
    
    On Error GoTo Cleanup ' 错误捕获，确保异常时能正确释放资源
    
    ' 打开目标工作簿
    Set targetWorkbook = Workbooks.Open(targetWorkbookPath)
    Set targetWorksheet = targetWorkbook.Worksheets(targetWorksheetName)
    
    ' 彻底清理目标工作表的旧列表和数据
    If targetWorksheet.ListObjects.Count > 0 Then
        targetWorksheet.ListObjects(1).Delete ' 删除整个列表对象，避免残留
    End If
    targetWorksheet.Cells.Clear ' 清空所有单元格内容和格式
    
    ' 复制当前列表的数据到目标工作表
    currentListObj.ResultRange.Copy Destination:=targetWorksheet.Range("A1")
    
    ' 获取新生成的列表对象，刷新数据
    Set targetListObj = targetWorksheet.ListObjects(1)
    targetListObj.QueryTable.Refresh BackgroundQuery:=False
    
    ' 保存并关闭目标工作簿
    targetWorkbook.Close SaveChanges:=True
    
Cleanup:
    ' 恢复屏幕更新，释放对象
    Application.ScreenUpdating = True
    Set targetWorksheet = Nothing
    Set targetWorkbook = Nothing
    Set currentListObj = Nothing
    Set currentWorkbook = Nothing
    
    ' 输出错误信息（如果有）
    If Err.Number <> 0 Then
        MsgBox "执行出错: " & Err.Description, vbExclamation
    End If
End Sub

关键修改说明 #

• 彻底清理目标对象：直接删除整个ListObject而非仅QueryTable，同时清空工作表所有内容，避免旧数据残留导致的对象冲突
• 错误捕获机制：添加On Error GoTo确保异常时能正确释放资源、恢复屏幕更新，避免Excel卡死
• 明确对象引用：复制数据后重新获取新生成的ListObject，确保QueryTable引用有效
• 关闭屏幕更新：提升宏的执行效率，同时避免因界面刷新导致的意外错误

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