在EXCEL vba中杀死连接 [英] Killing connection in EXCEL vba

问题描述

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

I am trying to removeconnection from my work book but I am still geting run-time error 5. I dont know what to do because in my other projects it works.

谢谢你的意见。来自捷克共和国的问候。

Thanks for advice. Greeting from czech Republic.

    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循环中尝试删除，使用最小索引1（One = 2/2） VBA代替i变量：

You could try this in the for loop for deleting, using the minimal index 1 (One = 2/2) in VBA in place of i variable:

ActiveWorkbook.Connections.Item(1).Delete

而不是

ActiveWorkbook.Connections.Item(i).Delete

当您删除时，ActiveWorkbook.Connections.Count（）将减少，一些.item（i）不存在。

As you delete, ActiveWorkbook.Connections.Count() will diminish, Some .item(i) does no more exist.

或者这个：

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

这篇关于在EXCEL vba中杀死连接的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！