Search code examples
sql-serverexcelpowerpivot

Prevent Excel from modifying dataconnection

I have a weird and frustrating situation: I created a PowerPivot based on SQL Server tables. I have chosen for the SQLNCLI11 provider.

All works well on my laptop, but when a colleague opens the sheet on his own pc and wants to refresh the data, Excel complains that the provider is not registered on the machine. This is the first weird thing: I installed the SQL Server Native Client provider with the right bit version. But the message persists even after a reboot.

To get around this situation, I changed the provider using the steps described in https://support.microsoft.com/en-us/help/2955369/cannot-modify-the-odata-connections-in-an-excel-2013-powerpivot-workbo

Now, the weird and frustrating thing: when I (or my colleague) refresh the data, Excel changes back the provider. Why?

This is the original situation:

enter image description here

I then change the connection: the provider is changed to MS Ole DB provider for SQL Server and for the record I also changed the datasource to an ip address:

enter image description here

I save the Excel sheet, close it and then reopens it. The datasource is still the modified one. However: when I refresh the data and then check the datasource again, it has reverted to the original situation.

So basically the question is: how to permanently change an existing connection?

And now we are at it: any ideas why my colleagues pc keeps complaining about the SQLNCLI11 provider not being registered?

I am using Excel 2016 and the SQL Server version is 2014 SP2.

Thank you for your time

Solution

  • Ok, I finally found a (dirty) way to get things going again: I renamed the xlsx extension to zip and changed the xl\connections.xml. I changed all the

    <x15:oledbPr connection

    elements with the right provider and to store my password. After saving the changes it worked again.

    I also found out that if you change an alias in one of your queries, you cannot refresh the data anymore. At first I simply removed the query and re added it. However you can also change this manually in the XML file. Just change the

    <x15:dbCommand

    element in the same connections.xml.