Delete Power Queries and External Data Connections

[matt-gilbert]

Hello. I have a workbook that I use to create a report for our client. It has 5 sheets and each sheet has it's own Data Connection (DC), 2 from Excel files, 3 from our company's proprietary software ie a .rpt connection. I have written a macro to filter each of these DCs, copy and paste visible data onto 5 new spreadsheets, delete original 5 DC sheets and then save as a new workbook in a different folder. What I am having trouble with is deleting the 5 DCs. I have tried the following 2 codes with no success;

  Do While ActiveWorkbook.Connections.Count > 0
  ActiveWorkbook.Connections.Item(ActiveWorkbook.Connections.Count).Delete
  Loop

and;

  For Each cn In ThisWorkbook.Connections
  cn.Delete
  Next cn

NB: My DCs are not connected at this point as I am not on the company network. Maybe this is why it is not working?

Any help would be appreciated. Thanks in advance.

Matt

 

[Chihiro]

Use below to remove connections and queries.

Sub Demo()
Dim cn As WorkbookConnection
Dim qr As WorkbookQuery
On Error Resume Next
For Each cn In ThisWorkbook.Connections
    cn.Delete
Next
For Each qr In ThisWorkbook.Queries
    qr.Delete
Next
End Sub

 

[matt-gilbert]

Hi Chihiro. Thanks for your reply however I am receiving an error message as follows;
Compile error:
Syntax error
The following code appears in red;
cn.Delete
qr.Delete
Any ideas?
Matt

 

[Chihiro]

By the way, what version of Excel do you have?

AFAIK, Excel 2010 version of PowerQuery lacks VBA Object model.

That can be causing your error. Unfortunately, for Excel 2010, you can't do much with VBA in regards to PowerQuery, PowerPivot etc.

 

[matt-gilbert]

Excel 2016

 

[Chihiro]

Hmm, I can't reproduce your error.

See attached sample.

 

[matt-gilbert]

My bad. It works fine now that I am back on the company network and the connections are active (they were inactive when I tested earlier).
Thanks Chihiro!

 

[Autonomousone]

Hmm, I can't reproduce your error.

See attached sample.

Hi Chihiro,

I know it's been a while since you responded in this thread but I was hoping to find out if there was something that you did to the file you attached that allowed the pivot table to remain all formatted and unaffected after the queries and data connections were deleted?

The macro to delete the connection and queries work fine on my workbook but all of the pivot tables I created all disappear afterwards and my workbook is virtually empty.

I'm sourcing data from multiple objects from a database and they're loaded to the data model where they're all made relational. And i'm using Office 365 pro plus on excel.

Any help or suggestions you have will be greatly appreciates.

Many Thanks

 

[Chihiro]

I'd recommend you upload sample where that happens.

Typically, I don't delete the data model and only connections in my reports (If I do it in Excel). Each report is pre-filtered at data import stage to only contain info that pertains to person/group receiving the report.

 

[Autonomousone]

Hi Chihiro,

Thanks for getting back to me.

I've uploaded an amended version of your original file with the connection delete macro. I've copied your original table and modified the values slightly, I then uploaded that through the power query and I then added the new table to the data model and it's now connected to the original one.

I then created a new pivot table that includes values from both the old and new tables. When you execute the the macro, from my end the second pivot table deletes itself.

When you pre filter the data that's only individual/group specific, do you modify that that in the power query or do have a parameters embedded in excel?

Again, thank you for your help.

 

[Autonomousone]

Sorry,

Just seen that on the second pivot table I titled them the wrong way around. "Values From First Table" is from the second and the "Values From Second Table" is from the first.

Thanks

 

[Chihiro]

...pre filter the data that's only individual/group specific...

In PQ's native query and/or in PQ using M. Then I'd load the data to data model, and kill the query.
So the user has access to what's already loaded in model, but not anything else in the source.

Note that the source must reside outside of the workbook containing report/data model.

As for your issue with not being able to interact with Pivot. Just use the loop to kill query and keep the connection.
Connection to the data model is required to interact with pivot table in any way.