CODEWITHSUNDEEP
vbaexcel
Filip Ondo
Filip Ondo

Reputation: 165

Killing connection in EXCEL vba

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

P.S. getting error on 

ActiveWorkbook.Connections.Item(i).Delete

Upvotes: 8

Views: 18263

Answers (2)

Espen Rosenquist
Espen Rosenquist

Reputation: 448

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

Upvotes: 6

jacouh
jacouh

Reputation: 8741

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

Instead of 

ActiveWorkbook.Connections.Item(i).Delete

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

Or this:

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

Upvotes: 9

Related Questions