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/3011c763-779f-47cb-bbd3-86b8f4beddf8?messageId=23efd5d8-eca6-4172-8ec1-1b7a10fe6dde)
I recently migrated from Office 2016 to office 365 and some GUI-functions seem downgraded as a result. In Excel 2016 I could do: Data -> Connections, a window would open, I could select them all and delete them. In 365, something to the right opens and I can only delete them one-by-one...
To illustrate I attach screenshots of how one manages data connections in Excel 2016 vs Excel 365.
Kind rgds,
Jaron
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 Subyou 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 SubHi, 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
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
Hi P.R. Ortiz, yes, that would work.
Is the best workaround you will find. Please mark my reply as best response if it helped. Thanks.
