Forum Discussion
Office 365 - Delete data connections in one go
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
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 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 SubSee 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.
The thing is, that does not help with the case where you want to select multiple, but not all, queries and connections...
