在 EXCEL vba 中终止连接
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/19903428/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Killing connection in EXCEL vba
提问by Filip Ondo
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.
我正在尝试从我的工作簿中删除连接，但我仍然遇到运行时错误 5。我不知道该怎么做，因为在我的其他项目中它有效。
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
P.S. getting error on
PS出现错误
ActiveWorkbook.Connections.Item(i).Delete
回答by jacouh
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:
您可以在 for 循环中尝试删除，使用 VBA 中的最小索引 1 (One = 2/2) 代替 i 变量：
ActiveWorkbook.Connections.Item(1).Delete
Instead of
代替
ActiveWorkbook.Connections.Item(i).Delete
As you delete, ActiveWorkbook.Connections.Count() will diminish, Some .item(i) does no more exist.
当您删除时，ActiveWorkbook.Connections.Count() 会减少，某些 .item(i) 不再存在。
Or this:
或这个：
'~~>kill all connections
For i = ActiveWorkbook.Connections.Count To 1 Step -1
ActiveWorkbook.Connections.Item(i).Delete
Next
回答by Espen Rosenquist
Why not using the built-in enumerator of the connections collection?
为什么不使用连接集合的内置枚举器？
Public Sub DeleteAllConnectionsInWorkbook()
Dim aConn as Object
For Each aConn in ActiveWorkbook.Connections
aConn.Delete
Next aConn
End Sub
