Share via

How to Change\Edit Excel Workbook Data connection string ?

  • Article

Question

Wednesday, May 28, 2014 6:32 AM | 5 votes

One of my excel 2013 power pivot report was migrated from old server to new server after migration i changed the excel power pivot connection string to connect with new server but the workbook connections is still taking the old connection string of old server and there is no option of changing workbook connnection string .

I am able to edit the powerpivot connection but workbook connections are not getting updated they are still taking old server connection string.

All replies (15)

Monday, June 2, 2014 2:37 AM ✅Answered | 3 votes

Hello,

In Excel, Power Pivot tab > Manage Data Model to open the Power Pivot window. Then, we can directly click "Existing Connection" to edit the data source connection of PowerPivot data model.

After you update the server information for the connection, you can click "Refresh" buttion to verify the data source connection is correct or not. The data source connection update successfully if we can refresh data. Please see the screenshot below: 

Regards,

Elvis Long
TechNet Community Support

Monday, June 2, 2014 3:22 AM | 3 votes

HI

I did the same but the Report workbook was still taking the old data connection.I checked the same in Data connection tab there also it was same old data connection string where there was no option of editing the connection string as in below screenshot.

Friday, June 6, 2014 1:31 AM | 3 votes

Hello,

Have you follow my steps I posted above? Click Power Pivot tab > Manage Data Model to open the Power Pivot window, and then click "Existing Connection" button. Here is the screenshot on my side, please see: |

So, we can directly modify the Server Name and Database name properties.

Elvis Long
TechNet Community Support

Friday, June 6, 2014 3:11 AM | 2 votes

Hi 

I did the same thing by changing the existing connection same as in your screenshot but the workbook connection is pointing to old server data connection as in screen

The power pivot connection data has been changed but the workbook connections are pointing to old server and editing option is disabled when selecting the workbook connections.



Rakesh_BI

Friday, June 6, 2014 3:28 AM | 3 votes

Hello,

Please access to the following path:
C:\Users\spadmin.Contoso\Documents\My Data Sources

And then backup "2013-SP Project_Server_Data.odc" file, and remane it as "New_2013-SP Project_Server_Data.odc"

Open the "New_2013-SP Project_Server_Data.odc" with notepad, and then updated the "Catalog" properties. Here is the example:
<meta name=ProgId content=ODC.Cube>
<meta name=SourceType content=OLEDB>
<meta name=Catalog content=AdventureWorksDW2012Multidimensional-EE>
<meta name=Table content="Adventure Works">

<odc:ConnectionString>Provider=MSOLAP.5;Integrated Security=SSPI;Persist Security Info=True;Data Source=ELVIS-MSFT;Initial Catalog=AdventureWorksDW2012Multidimensional-EE</odc:ConnectionString>

Elvis Long
TechNet Community Support

Friday, June 6, 2014 4:03 AM | 2 votes

Hi did the steps as suggested by you but it didn't work still the workbook connection are pointing to old data sources.in local connection it has added a new connection based on your steps as in screenshot 1

Workbookconnection still Remains unchanged pointing to old server connection string screenshot 2

Rakesh_BI

Friday, June 6, 2014 4:55 AM | 3 votes

Hello,

Have you try to right-click on the data connection and then update server name information under "Select a data source connection" section? Please see the screenshot:

Elvis Long
TechNet Community Support

Friday, June 6, 2014 7:22 AM | 4 votes

Hello 

The Power pivot data connection are already connected to new server. In the Screenshot you took th data source 2013-SP is new Server DB Name which is correct 

But the workbook connection is pointing to old server connection see the difference in the below screenshot with explanation.



The only way to refresh the report data is to open reports Power pivot-Manage-Design Tab-Table Properties-Rerun the query which is refreshing the data.Whereas Refreshing workbook is not working due to old server connection string.

Rakesh_BI

Wednesday, July 2, 2014 10:55 PM | 1 vote

Rakesh, is this still an issue?

Thanks!

Ed Price, Azure & Power BI Customer Program Manager (Blog, Small Basic, Wiki Ninjas, Wiki)

Answer an interesting question? Create a wiki article about it!

Thursday, July 3, 2014 3:01 AM | 2 votes

It didn't get fixed but as it was urgent requirement So I created a new copy of the Report in the new server 

Rakesh_BI

Friday, December 12, 2014 8:32 PM | 3 votes

Still an issue today and very painful if you don't get it right the very first time you specify your data source in Power Pivot.  Once created, the Excel Data source is no longer editable.  Period.

Current customer situation where they have a workbook and Power Pivot model that will allow them to pull data in via the Table Properties button but a 'Refresh' will not work.  The Power Pivot connection has the correct server FQDN and works while the #2 workbook connection specifies just the server name and will not work.

Short of starting all over again with a new workbook and new model, there is no way to resolve this.

Friday, April 24, 2015 2:40 PM | 2 votes

+1 for the need for a solution.

I have exactly the same situation. I created a fairly complex PowerView report, and a Power Pivot model with many calculated columns, using a "DEV" data source. Now I want to go into "Production" data source, with the same tables and fields, but it won´t update the data in the report.

Hope to get a solution for this, since I´ll probably need to create more reports using "dev" data sources before using the production server.

Regards,

Wednesday, November 1, 2017 8:54 AM | 2 votes

+ 1 Solution needed.

Same here. Build a capacity model using Powerpivot on SCOM data. After a migration the connections in the Workbooks are broken, exact like Rakesh describes. You can adjust the Powerpivot connection, but the edit option for the workbook is greyed-out.  

Fairly disappointing one would say.

Friday, January 25, 2019 12:05 AM | 2 votes

There is a solution, but it requires you to modify the underlying Excel files.

  • Make a copy of your .xlsx file
  • Change the extension to .zip (so MyFile.xlsx becomes MyFile.zip)
  • Extract the files in the zip
  • Open connections.xml in the xl folder
  • Look for a <connection> tag where the name="" attribute matches your connection name
  • Under this there should be a <x15:oledbPr> element with a connection="" attribute containing the connection string
  • Change the server name or other details in the connection string as required
  • Save the file
  • Zip all the contents of the base folder (the one that contains the _rels, customXml, docProps and xl folders). Ensure you're at the right level, otherwise Excel will treat the file as corrupt
  • Rename the zip extension to .xlsx

Thursday, October 10, 2019 2:08 PM

Thank you for this solution!!!

It worked like a charm! After of hours trying to get this fixed I saw your solution!

You saved me countless hours of rebuilding the whole workbook.

Cheers!