SOLVED
Home

Office 365 - Delete data connections in one go

%3CLINGO-SUB%20id%3D%22lingo-sub-149162%22%20slang%3D%22en-US%22%3EOffice%20365%20-%20Delete%20data%20connections%20in%20one%20go%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-149162%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20all%2C%20first%20post%20here%20as%20recommended%20by%20Philip%20Pio%20from%20Microsoft%20(%3CA%20href%3D%22https%3A%2F%2Fanswers.microsoft.com%2Fen-us%2Fmsoffice%2Fforum%2Fmsoffice_excel-mso_win10-mso_365hp%2Foffice-365-delete-connections-in-one-go%2F3011c763-779f-47cb-bbd3-86b8f4beddf8%3FmessageId%3D23efd5d8-eca6-4172-8ec1-1b7a10fe6dde%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%22%3Ehttps%3A%2F%2Fanswers.microsoft.com%2Fen-us%2Fmsoffice%2Fforum%2Fmsoffice_excel-mso_win10-mso_365hp%2Foffice-365-delete-connections-in-one-go%2F3011c763-779f-47cb-bbd3-86b8f4beddf8%3FmessageId%3D23efd5d8-eca6-4172-8ec1-1b7a10fe6dde%3C%2FA%3E)%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CDIV%20class%3D%22thread-message-content-body-text%20thread-full-message%22%3E%0A%3CP%3EI%20recently%20migrated%20from%20Office%202016%20to%20office%20365%20and%20some%20GUI-functions%20seem%20downgraded%20as%20a%20result.%20In%20Excel%202016%20I%20could%20do%3A%20Data%20-%26gt%3B%20Connections%2C%20a%20window%20would%20open%2C%20I%20could%20select%20them%20all%20and%20delete%20them.%20In%20365%2C%20something%20to%20the%20right%20opens%20and%20I%20can%20only%20delete%20them%20one-by-one...%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3ETo%20illustrate%20I%20attach%20screenshots%20of%20how%20one%20manages%20data%20connections%20in%20Excel%202016%20vs%20Excel%20365.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EKind%20rgds%2C%3C%2FP%3E%0A%3CP%20class%3D%22%22%3EJaron%3C%2FP%3E%0A%3C%2FDIV%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-149162%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%20Data%20Connection%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-152789%22%20slang%3D%22en-US%22%3ERe%3A%20Office%20365%20-%20Delete%20data%20connections%20in%20one%20go%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-152789%22%20slang%3D%22en-US%22%3E%3CP%3EWhich%20only%20works%20well%20if%20you%20want%20to%20delete%20all%20connections%20%3Ad%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EMany%20thanks%20though.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-152785%22%20slang%3D%22en-US%22%3ERe%3A%20Office%20365%20-%20Delete%20data%20connections%20in%20one%20go%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-152785%22%20slang%3D%22en-US%22%3E%3CP%3ECorrect%2C%20that%20takes%20us%20back%20to%20the%20workaround%20script%20%3B)%3C%2Fimg%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-152783%22%20slang%3D%22en-US%22%3ERe%3A%20Office%20365%20-%20Delete%20data%20connections%20in%20one%20go%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-152783%22%20slang%3D%22en-US%22%3E%3CP%3EYes%2C%20it%20is%20possible%20to%20delete%20the%20connections%20files.%20However%2C%20that%20does%20not%20delete%20the%20connections%20as%20known%20to%20the%20workbook.%20At%20best%20you%20will%20get%20a%20message%20that%20Excel%20failed%20to%20refresh%20the%20data.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-152778%22%20slang%3D%22en-US%22%3ERe%3A%20Office%20365%20-%20Delete%20data%20connections%20in%20one%20go%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-152778%22%20slang%3D%22en-US%22%3E%3CP%3EGood!%20I%20can%20view%20my%20odc%20connections%20in%3A%3C%2FP%3E%0A%3CP%3EC%3A%5CUsers%5Cmyuser%5CDocuments%5CMis%20archivos%20de%20origen%20de%20datos%3C%2FP%3E%0A%3CP%3ESo%20%22Mis%20archivos%20de%20origen%20de%20datos%22%20is%20like%20%22My%20data%20source%20files%22.%20Find%20that%20folder%20on%20File%20Explorer.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-152775%22%20slang%3D%22en-US%22%3ERe%3A%20Office%20365%20-%20Delete%20data%20connections%20in%20one%20go%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-152775%22%20slang%3D%22en-US%22%3E%3CP%3EOffice%20365%2C%20Version%201801%20(Build%209001.2138%20Click-to-Run).%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3ESo%20yes%2C%20through%20Data%20-%26gt%3B%20Get%20Data%20-%26gt%3B%20Launch%20Query%20Editor%2C%20I%20can%20indeed%20select%20multiple%20Queries%20and%20delete%20them.%20Thanks!%20It%20is%20rather%20easier%20in%20Office%202016%20though.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EWith%20Data%20Source%20Settings%20though%2C%20I%20can%20only%20delete%20permissions%2C%20not%20the%20actual%20Queries.%20Connections%20do%20not%20show%20up%20in%20either%20(tested%20with%20odc%20connections%20to%20SQL%20Server).%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-152768%22%20slang%3D%22en-US%22%3ERe%3A%20Office%20365%20-%20Delete%20data%20connections%20in%20one%20go%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-152768%22%20slang%3D%22en-US%22%3E%3CP%3EBTW%20what%20Office%20365%20license%20are%20you%20using%20with%20this%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-152766%22%20slang%3D%22en-US%22%3ERe%3A%20Office%20365%20-%20Delete%20data%20connections%20in%20one%20go%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-152766%22%20slang%3D%22en-US%22%3E%3CP%3EIt's%20in%20Spanish%2C%20but%20you%20can%20get%20the%20idea.%20If%20you%20select%20Query%20editor%20(first%20red%20marked%20option)%20Power%20Query%20editor%20opens%20and%20you%20can%20select%20multiple%20queries.%20If%20you%20select%20Data%20Source%20settings%20you%20can%20delete%20permissions%20for%20several%20connections%20in%20current%20Workbook%20or%20Globally.%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20347px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F28056i5BC0862B3AE1F141%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%22ExcelData.jpg%22%20title%3D%22ExcelData.jpg%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-152762%22%20slang%3D%22en-US%22%3ERe%3A%20Office%20365%20-%20Delete%20data%20connections%20in%20one%20go%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-152762%22%20slang%3D%22en-US%22%3E%3CP%3ESo%20from%20Data%20-%26gt%3B%20Get%20Data%2C%20I%20get%20no%20Edit.%20Data%20Source%20Settings%20are%20available%20and%20I%20can%20select%20multiple%20queries%20(not%2C%20e.g.%20SQL%20Server%20connections)%20but%20not%20actually%20delete%20them.%20When%20I%20select%20multiple.%20I%20can%20press%20delete%20but%20that%20goes%20to%20Clear%20Permissions.%20I%20can%20change%20permissions%20or%2C%20individually%20of%20course%2C%20change%20the%20Source.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3ECould%20you%20share%20a%20screenprint%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-152755%22%20slang%3D%22en-US%22%3ERe%3A%20Office%20365%20-%20Delete%20data%20connections%20in%20one%20go%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-152755%22%20slang%3D%22en-US%22%3E%3CP%3EIn%20Get%20Data%20-%26gt%3B%20Edit%20queries%20you%20can%20select%20several%20queries%20for%20deletion%2C%20and%20the%20same%20for%20connection%20permissions%26nbsp%3Bin%20Get%20Data%20-%26gt%3B%20Data%20Source%20settings%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-152751%22%20slang%3D%22en-US%22%3ERe%3A%20Office%20365%20-%20Delete%20data%20connections%20in%20one%20go%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-152751%22%20slang%3D%22en-US%22%3E%3CP%3EThe%20thing%20is%2C%20that%20does%20not%20help%20with%20the%20case%20where%20you%20want%20to%20select%20multiple%2C%20but%20not%20all%2C%20queries%20and%20connections...%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-152749%22%20slang%3D%22en-US%22%3ERe%3A%20Office%20365%20-%20Delete%20data%20connections%20in%20one%20go%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-152749%22%20slang%3D%22en-US%22%3E%3CP%3EIs%20the%20best%20workaround%20you%20will%20find.%20Please%20mark%20my%20reply%20as%20best%20response%20if%20it%20helped.%20Thanks.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-152747%22%20slang%3D%22en-US%22%3ERe%3A%20Office%20365%20-%20Delete%20data%20connections%20in%20one%20go%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-152747%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20P.R.%20Ortiz%2C%20yes%2C%20that%20would%20work.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-152211%22%20slang%3D%22en-US%22%3ERe%3A%20Office%20365%20-%20Delete%20data%20connections%20in%20one%20go%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-152211%22%20slang%3D%22en-US%22%3E%3CP%3Eyou%20can%20use%20this%20vbscript%20in%20a%20Macro%20button%3A%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CPRE%3ESub%20Remove()%0ADim%20connection%20As%20WorkbookConnection%0ADim%20query%20As%20WorkbookQuery%0AOn%20Error%20Resume%20Next%0AFor%20Each%20connection%20In%20ThisWorkbook.Connections%0A%20%20%20%20connection.Delete%0ANext%0AFor%20Each%20query%20In%20ThisWorkbook.Queries%0A%20%20%20%20query.Delete%0ANext%0AEnd%20Sub%3C%2FPRE%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1010766%22%20slang%3D%22en-US%22%3ERe%3A%20Office%20365%20-%20Delete%20data%20connections%20in%20one%20go%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1010766%22%20slang%3D%22en-US%22%3E%3CP%3EHi%2C%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F889%22%20target%3D%22_blank%22%3E%40Pablo%20R.%20Ortiz%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%20a%20spread%20sheet%20which%20has%20created%20about%2060%2C000%20data%20connections%20which%20is%20obviously%20going%20very%20slow.%20I%20have%20used%20your%20script%20which%20solves%20the%20speed%20problem%20but%20i%20i%20need%20to%20retain%20about%20100%20good%20connections.%20Is%20there%20a%20way%20i%20can%20exclude%20refreshed%20connections%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%3C%2FP%3E%3C%2FLINGO-BODY%3E
Jaron Rosegg
Occasional 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

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

Highlighted

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

Related Conversations
Tabs and Dark Mode
cjc2112 in Discussions on
46 Replies
Extentions Synchronization
Deleted in Discussions on
3 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
flashing a white screen while open new tab
Deleted in Discussions on
14 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
29 Replies
Security Community Webinars
Valon_Kolica in Security, Privacy & Compliance on
13 Replies