Killing connection in EXCEL vba

Question

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

Which Office you use? Btw, I'd recomment two things which may help: 1-give the name of a connection instead of ID (conn.Name), 2-do a Refresh before trying to remove the connection as it may still work, which is a possible reason the conn. is not dropped (to avoid data inconsistency) — , Nov 11 '13 at 10:13
I am using MS excel 2010, I refreshing it before killing connections — Filip Ondo, Nov 11 '13 at 10:36
Using OLE or ODBC, or both? Because I would do this, as might help -guessing though- "for each conn in Actw.Conns.; ActiveWorkbook.Connections(conn.Name).ODBCConnection.Refresh; ActiveWorkbook.Connections(1).ODBCConnection.Delete next" (if it's ODBC) — , Nov 11 '13 at 10:42

jacouh · Accepted Answer · 2013-11-11T13:32:35.527

9

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

edited Nov 11 '13 at 13:32

answered Nov 11 '13 at 13:14

jacouh

8,473
5
32
43

wow that worked!! super simply thanks very much I change i for 1 and it is working – Filip Ondo Nov 11 '13 at 13:44
false...its working only under virtual desktop but not under desktop...i dont know what the problem :-) – Filip Ondo Nov 11 '13 at 14:31
What's this: virtual desktop, desktop on Windows ? Do you mean Remote Desktop ? – jacouh Nov 11 '13 at 14:46
we are using Citrix windows 2008 sererver 2 and on my desktop is win XP prof – Filip Ondo Nov 11 '13 at 14:49
I've checked in Excel 2007 on Windows 7 Pro, I cannot test Citrix windows 2008 Server 2 as we haven't it... ;-( . – jacouh Nov 11 '13 at 15:13
looks like the problem is `ActiveWorkbook.RefreshAll` without it its working but i need refresh workbook before cuting connection – Filip Ondo Nov 11 '13 at 15:15

score 6 · Answer 2 · answered Jan 04 '17 at 11:50

6

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

answered Jan 04 '17 at 11:50

Espen Rosenquist

439
4
9

Killing connection in EXCEL vba

2 Answers2

Linked