SharePoint列表QueryTable.Refresh刷新失败，报1004错误如何解决？

我尝试用以下VBA代码从SharePoint列表获取/刷新数据，但刷新操作抛出Run-time error 1004: Application-defined or object-defined error，求解决方法：

Sub Update()
    ActiveWindow.ScrollWorkbookTabs Sheets:=7
    Sheets("LIMS Live Query3").Select
    ActiveWindow.ScrollRow = 10000
    ActiveWindow.ScrollColumn = 13
    Range("A2").Select
    Selection.ListObject.QueryTable.Refresh BackgroundQuery:=False
    Sheets("LIMS Filtered").Select
    ActiveWindow.ScrollRow = 10000
    ActiveWindow.LargeScroll ToRight:=-1
    Range("A3:v3").Select
    Selection.Copy
    Range(Selection, Selection.End(xlDown)).Select
    ActiveSheet.Paste
    ActiveWindow.ScrollRow = 10000
    Range("A3").Select
    ActiveWindow.ScrollWorkbookTabs Sheets:=-7
    Sheets("Body Map Data").Select
    Range("A34").Select
End Sub

相关截图 #

• 重构代码，避免依赖Select/Active对象
  代码里大量用Select和ActiveSheet是1004错误的高频诱因，对象引用容易因窗口状态变化失效。改成直接引用对象的写法：

  Sub Update()
      Dim wsLive As Worksheet, wsFiltered As Worksheet, wsBody As Worksheet
      Dim targetTbl As ListObject
      
      ' 直接绑定工作表对象
      Set wsLive = ThisWorkbook.Sheets("LIMS Live Query3")
      Set wsFiltered = ThisWorkbook.Sheets("LIMS Filtered")
      Set wsBody = ThisWorkbook.Sheets("Body Map Data")
      
      ' 直接定位到目标列表并刷新
      Set targetTbl = wsLive.ListObjects(1) ' 也可替换为具体表名，比如ListObjects("你的表名")
      targetTbl.QueryTable.Refresh BackgroundQuery:=False
      
      ' 简化复制粘贴操作
      With wsFiltered
          .Range("A3:V3").Copy
          .Range(.Range("A3"), .Range("A3").End(xlDown)).PasteSpecial xlPasteAll
          .Range("A3").Select
          .ActiveWindow.ScrollRow = 10000
          .ActiveWindow.LargeScroll ToRight:=-1
      End With
      
      ' 切换到目标工作表
      wsBody.Select
      wsBody.Range("A34").Select
      ThisWorkbook.ActiveWindow.ScrollWorkbookTabs Sheets:=-7
  End Sub
  

• 检查SharePoint连接有效性

  1. 手动打开Excel的「数据」选项卡，找到该查询表的连接，点击「属性」确认SharePoint地址正确，且当前账号有访问权限。
  2. 先手动刷新查询表，如果手动也失败，说明是连接本身的问题：
     • 确认SharePoint站点可访问、目标列表未被删除或重命名。
     • 清理Office凭据缓存：打开控制面板→用户账户→凭据管理器，删除相关SharePoint凭据后重新登录。

• 验证ListObject与QueryTable的关联
  确保A2单元格属于ListObject范围，且该ListObject绑定了QueryTable，可在代码中加判断：

  If Not wsLive.Range("A2").ListObject Is Nothing Then
      If Not wsLive.Range("A2").ListObject.QueryTable Is Nothing Then
          ' 执行刷新逻辑
      Else
          MsgBox "该列表未绑定QueryTable"
      End If
  Else
      MsgBox "A2单元格不属于任何ListObject"
  End If
  

• 处理后台刷新冲突
  如果之前开启过后台刷新，可能存在未完成的任务导致冲突，刷新前先等待所有异步任务结束：

  Application.CalculateUntilAsyncQueriesDone
  targetTbl.QueryTable.Refresh BackgroundQuery:=False
  

• 检查Excel信任中心设置
  打开Excel选项→信任中心→信任中心设置→外部内容，确认「允许来自所有位置的外部数据」（或至少允许目标SharePoint站点），同时确保「禁用所有数据连接」未勾选。

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