Forum Discussion

Copper Contributor

Jan 26, 2018

Solved

Office 365 - Delete data connections in one go

Hi all, first post here as recommended by Philip Pio from Microsoft (https://answers.microsoft.com/en-us/msoffice/forum/msoffice_excel-mso_win10-mso_365hp/office-365-delete-connections-in-one-go/3011...

excel

Pablo R. Ortiz

Feb 04, 2018

you can use this vbscript in a Macro button:

Sub Remove()
Dim connection As WorkbookConnection
Dim query As WorkbookQuery
On Error Resume Next
For Each connection In ThisWorkbook.Connections
    connection.Delete
Next
For Each query In ThisWorkbook.Queries
    query.Delete
Next
End Sub

JasonBoull

Copper Contributor

Nov 15, 2019

Hi, Pablo R. Ortiz

I have a spread sheet which has created about 60,000 data connections which is obviously going very slow. I have used your script which solves the speed problem but i i need to retain about 100 good connections. Is there a way i can exclude refreshed connections?

Thanks

pnavarro

Copper Contributor

Apr 23, 2020

Hi JasonBoull. This is my first time answering here (and I am quite late but found the same problem). I am no expert, but I would take what Pablo R. Ortiz posted before and add exceptions:

Sub Remove()
Dim connection As WorkbookConnection
Dim query As WorkbookQuery
On Error Resume Next
For Each connection In ThisWorkbook.Connections
    If connection.PARAMETER<>"Condition" Then
        Next
    Else connection.Delete
Next
For Each query In ThisWorkbook.Queries
    query.Delete
Next
End Sub

See that I added a condition to the script, where you compare your connections parameters to some condition. If there are several conditions maybe you could nest an array and compare them all.

Hope it helps.

Pedro