Excel VBA无法获取DataFeedConnection.Refreshing属性报1004错误

1004错误的排查与解决 #

你遇到的1004错误通常由两个原因导致：

• 连接索引错误：Connections(2)可能超出当前文件的连接数量，或者该连接不是DataFeedConnection类型（比如是ODBC/OLEDB连接，无法强制转换为DataFeedConnection）
• 连接状态异常：连接处于未初始化、断开或其他异常状态，无法正常访问Refreshing属性

可以用以下代码先验证连接的有效性：

Sub VerifyConnection()
    Dim aw As Workbook
    Dim targetConn As WorkbookConnection
    
    Set aw = ActiveWorkbook
    
    ' 检查目标连接是否存在
    If aw.Connections.Count < 2 Then
        MsgBox "索引为2的连接不存在，请检查连接列表"
        Exit Sub
    End If
    
    Set targetConn = aw.Connections(2)
    
    ' 检查连接类型是否为DataFeedConnection
    If targetConn.Type <> xlConnectionTypeDataFeed Then
        MsgBox "该连接不是DataFeedConnection类型，无法使用Refreshing属性" & vbCrLf & _
               "当前连接类型：" & targetConn.Type
        Exit Sub
    End If
    
    ' 验证Refreshing属性
    MsgBox "当前刷新状态：" & targetConn.DataFeedConnection.Refreshing
End Sub

解决刷新未完成就关闭文件的问题 #

要实现"刷新完成后再导出工作表并关闭文件"，核心是确保宏等待所有数据刷新完成后再执行后续操作，有两种实现方式：

方式1：同步刷新（推荐） #

设置BackgroundQuery:=False，让刷新操作同步执行，宏会等待刷新完成后再继续：

Sub RefreshThenExport()
    Dim aw As Workbook
    Dim conn As WorkbookConnection
    Dim dfc As DataFeedConnection
    Dim ws As Worksheet
    Dim exportFolder As String
    
    Set aw = ActiveWorkbook
    exportFolder = "C:\你的导出目录\" ' 替换为实际导出路径
    
    ' 遍历所有DataFeedConnection并同步刷新
    For Each conn In aw.Connections
        If conn.Type = xlConnectionTypeDataFeed Then
            Set dfc = conn.DataFeedConnection
            ' 同步刷新，等待完成后再继续
            dfc.Refresh BackgroundQuery:=False
        End If
    Next conn
    
    ' 导出每个工作表
    For Each ws In aw.Worksheets
        ws.Copy
        ActiveWorkbook.SaveAs exportFolder & ws.Name & ".xlsx", xlOpenXMLWorkbook
        ActiveWorkbook.Close SaveChanges:=False
    Next ws
    
    ' 关闭原文件
    aw.Close SaveChanges:=True
End Sub

方式2：异步刷新+循环等待 #

如果必须使用异步刷新（比如刷新时间过长需要允许用户操作），可以用循环等待刷新完成，同时用DoEvents让Excel处理刷新进程：

Sub AsyncRefreshThenExport()
    Dim aw As Workbook
    Dim conn As WorkbookConnection
    Dim dfc As DataFeedConnection
    Dim ws As Worksheet
    Dim exportFolder As String
    
    Set aw = ActiveWorkbook
    exportFolder = "C:\你的导出目录\"
    
    ' 启动异步刷新
    For Each conn In aw.Connections
        If conn.Type = xlConnectionTypeDataFeed Then
            Set dfc = conn.DataFeedConnection
            If Not dfc.Refreshing Then
                dfc.Refresh BackgroundQuery:=True
            End If
        End If
    Next conn
    
    ' 等待所有DataFeedConnection刷新完成
    Dim isRefreshing As Boolean
    Do
        isRefreshing = False
        For Each conn In aw.Connections
            If conn.Type = xlConnectionTypeDataFeed Then
                If conn.DataFeedConnection.Refreshing Then
                    isRefreshing = True
                    Exit For
                End If
            End If
        Next conn
        DoEvents ' 让Excel处理刷新事件
    Loop While isRefreshing
    
    ' 导出工作表并关闭文件
    For Each ws In aw.Worksheets
        ws.Copy
        ActiveWorkbook.SaveAs exportFolder & ws.Name & ".xlsx", xlOpenXMLWorkbook
        ActiveWorkbook.Close SaveChanges:=False
    Next ws
    
    aw.Close SaveChanges:=True
End Sub

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