终止EXCEL vba中的连接

菲利普·翁多(Filip Ondo)

我正在尝试从工作簿中删除连接,但是仍然遇到运行时错误5。我不知道该怎么办,因为在我的其他项目中它可以工作。

谢谢你的建议。来自捷克共和国的问候。

    Sub refresh_all()

    Dim i As Integer
    '~~> refresh workbook query
    Application.DisplayAlerts = False

    Workbooks("UAC_report_p.xlsb").Activate

    '~~> wait for refresh then execute Call save_as
    Do Until Application.CalculationState = xlDone
    DoEvents
    Loop

    ActiveWorkbook.RefreshAll

    Workbooks("UAC_report_p.xlsb").Activate

    '~~>kill all connections
        For i = 1 To ActiveWorkbook.Connections.Count
            If ActiveWorkbook.Connections.Count = 0 Then Exit For
            ActiveWorkbook.Connections.Item(i).Delete
            i = i - 1
        Next i

        Application.DisplayAlerts = True
    End Sub

PS出现错误

ActiveWorkbook.Connections.Item(i).Delete
雅库

您可以在for循环中尝试删除它,使用VBA中的最小索引1(One = 2/2)代替i变量:

ActiveWorkbook.Connections.Item(1).Delete

代替

ActiveWorkbook.Connections.Item(i).Delete

删除时,ActiveWorkbook.Connections.Count()将减少,不再存在某些.item(i)。

或这个:

 '~~>kill all connections
    For i = ActiveWorkbook.Connections.Count To 1 Step -1
        ActiveWorkbook.Connections.Item(i).Delete
    Next

本文收集自互联网,转载请注明来源。

如有侵权,请联系 [email protected] 删除。

编辑于
0

我来说两句

0 条评论
登录 后参与评论

相关文章