Forum Discussion

Copper Contributor

Sep 22, 2017

Solved

Data Connection Command Text - Reverts On Save, Open

I've been given a workbook containing multiple SQL data connections to QA. I've changed a parameter in the SQL Command Text in each of the data connections. When I save the worksheet, my chan...

excel

Macros and VBA

SergeiBaklan
Sep 22, 2017
Hi Robert,

I guess you load your data into the data model, it has it's own import mechanism. Such connection could be changed by PowerPivot interface.

And without data model using SQL query you have no connection with odc file. You may create new workbook using existing odc, but all changes made in SQL query within the workbook won't be returned to odc file.

I'm not sure how exactly connections work, above is only my guess what it could be.

SergeiBaklan

Diamond Contributor

Sep 22, 2017

Hi Robert,

I guess you load your data into the data model, it has it's own import mechanism. Such connection could be changed by PowerPivot interface.

And without data model using SQL query you have no connection with odc file. You may create new workbook using existing odc, but all changes made in SQL query within the workbook won't be returned to odc file.

I'm not sure how exactly connections work, above is only my guess what it could be.

Robert May
Copper Contributor
Sep 25, 2017
Thanks Sergei,

It looks like you're right in that PowerPivot allows me to manage the datamodel, and changes will stick post-save.

I'm pretty confused by this though, because I spoke to the developer who wrote this spreadsheet and set up the external connections and he did not use the PowerPivot add-in to manage his data model.

Edit: I misspelt your name! Sorry!