gpt4 book ai didi

vba - 在 EXCEL vba 中终止连接

转载 作者:行者123 更新时间:2023-12-02 00:54:41 25 4
gpt4 key购买 nike

我正在尝试从工作簿中删除连接,但仍然收到运行时错误 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

附注出现错误

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

关于vba - 在 EXCEL vba 中终止连接,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/19903428/

25 4 0
Copyright 2021 - 2024 cfsdn All Rights Reserved 蜀ICP备2022000587号
广告合作:1813099741@qq.com 6ren.com