SOLVED

Office 365 - Delete data connections in one go

Copper Contributor

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-del...)

 

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

16 Replies
best response confirmed by Jaron Rosegg (Copper Contributor)
Solution

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

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...

In Get Data -> Edit queries you can select several queries for deletion, and the same for connection permissions in Get Data -> Data Source settings

So from Data -> Get Data, I get no Edit. Data Source Settings are available and I can select multiple queries (not, e.g. SQL Server connections) but not actually delete them. When I select multiple. I can press delete but that goes to Clear Permissions. I can change permissions or, individually of course, change the Source.

 

Could you share a screenprint?

It's in Spanish, but you can get the idea. If you select Query editor (first red marked option) Power Query editor opens and you can select multiple queries. If you select Data Source settings you can delete permissions for several connections in current Workbook or Globally.

ExcelData.jpg

BTW what Office 365 license are you using with this?

Office 365, Version 1801 (Build 9001.2138 Click-to-Run).

 

So yes, through Data -> Get Data -> Launch Query Editor, I can indeed select multiple Queries and delete them. Thanks! It is rather easier in Office 2016 though.

 

With Data Source Settings though, I can only delete permissions, not the actual Queries. Connections do not show up in either (tested with odc connections to SQL Server).

 

 

Good! I can view my odc connections in:

C:\Users\myuser\Documents\Mis archivos de origen de datos

So "Mis archivos de origen de datos" is like "My data source files". Find that folder on File Explorer.

Yes, it is possible to delete the connections files. However, that does not delete the connections as known to the workbook. At best you will get a message that Excel failed to refresh the data.

Correct, that takes us back to the workaround script ;)

Which only works well if you want to delete all connections :d

 

Many thanks though.

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

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

There's an easier way to do this now. In the Queries & Connections sidebar, select the first connection you want to delete. Then you can SHIFT + Click the bottom entry and it will highlight all the entries in between. You can then right click and choose Delete.
1 best response

Accepted Solutions
best response confirmed by Jaron Rosegg (Copper Contributor)
Solution

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

View solution in original post