EXCEL VBA close all open Workbook connections to external Excel Files

Question

I do have an issue with Excel and Data connections and hope to find a solution. Situation: I have an Excel Report with 2 external Data connection’s which feed into pivot tables. The Data Connections are linked to other Excel files containing the Raw Data for the Pivot Tables (Basically just one big Table) and have been setup using the Excel build in function via Data / Connections. Now every time I want to refresh the connections Excel is running out of Memory stopping with an error message.

If I refresh just one pivot Table, save and close the file and then refresh the other pivot table it works but isn’t very handy to use. What I noticed is, that once the Data has been read into the pivot table, the connection to the Raw Data File is still open which is the reason of the big memory consumption …..I assume. If I delete the connection excel is releasing a loooooooot of memory which brings me to the guess that if I CLOSE the connection after the data has been updated excel will free up memory.

So my question would be: how can I close any open workbook connections in VBA at once ? Note: I think I’m quite good at VBA but I’m not a professional

Many Thanks in advance Cheers, Dennis

Answer 1

This sub will remove all connections from the active workbook:

Sub RemoveConnections()
    Dim conn As Long
    With ActiveWorkbook
        For conn = .Connections.Count To 1 Step -1
            .Connections(conn).Delete
        Next conn
    End With
End Sub

Just import the data and close the connection when done.

If you meant close ALL the connections across all workbooks at once you could modify RemoveConnections to take a workbook parameter (instead of operating on ActiveWorkbook) and then iterate through the Workbooks collection (untested):

Dim wb As Workbook

For Each wb In Workbooks
    wb.AcceptAllChanges
    Call RemoveConnections(wb)
Next wb