我正在尝试从工作簿中删除连接,但是仍然遇到运行时错误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] 删除。
我来说两句