locked
Excel data connection does not update when editing Powerpivot connection RRS feed

  • Question

  • Hi there!

    Since we are migrating to a new SQL server, I am modifying the data connection links/strings of our Excel files (change server name to new server name - lesson learned: I now use a DNS alias instead of the new server name).

    I have no problem modifying normal xls data connections, but when I try to change the Powerpivot connection strings/links/definition, the boxes are greyed out. On the bottom it reads 'some properties cannot be changed because this connection was modified using the powerpivot add-in'.

    I figured out  it's fairly easy to change the Powerpivot data connections by opening the powerpivot add-in and editing the 'existing connection' (home tab), as also described in technet/internet articles. I can reference to the new server/dns_alias, enter name and table info, test, refresh successfully... the works. When I check the connection in Excel however, it remains unchanged. On closing and reopening the Excel file, powerpivot once again queries the old SQL server (existing connection changed back to the 'previously unchanged Excel definition')...

    --> Any advise on how to permanently change the connection info (server name) for power-pivot data? How can I push the new Powerpivot connection info back to Excel or edit the greyed out Excel connection definitions anyway?

    Thank you so much for any advise!

    Rgds,

    Sven

    Thursday, October 16, 2014 3:30 PM

Answers

  • Hi Sven,

    The method you are using to update the connection is definitely correct. What you can do that should force the connection to be updated is after making the change to the server name go ahead and pull in another table (any small table). After successfully loading this table, save and close the workbook and then re-open it. Following this, check the connection again under the Data tab to see if the connection string is updated and then remove the table that was added.


    Regards,

    Michael Amadi

    Please use the 'Mark as answer' link to mark a post that answers your question. If you find a reply helpful, please remember to vote it as helpful :)

    Website: http://www.nimblelearn.com
    Blog: http://www.nimblelearn.com/blog
    Twitter: @nimblelearn

    Friday, October 24, 2014 9:51 AM

All replies

  • Hi Sven,

    Is there any chance that you could provide some screenshots to help illustrate this issue. This will help members of the forum to reproduce the same issue and potentially propose a solution. 


    Regards,

    Michael Amadi

    Please use the 'Mark as answer' link to mark a post that answers your question. If you find a reply helpful, please remember to vote it as helpful :)

    Website: http://www.nimblelearn.com
    Blog: http://www.nimblelearn.com/blog
    Twitter: @nimblelearn

    Thursday, October 16, 2014 3:42 PM
  • Hi,

    Check this out...

    Thanks for any advise on how to change the server name in a way that it is stored/updated in the xlsx file!



    • Edited by Sven_vD Wednesday, October 22, 2014 3:31 PM
    Wednesday, October 22, 2014 3:30 PM
  • Hi Sven,

    The method you are using to update the connection is definitely correct. What you can do that should force the connection to be updated is after making the change to the server name go ahead and pull in another table (any small table). After successfully loading this table, save and close the workbook and then re-open it. Following this, check the connection again under the Data tab to see if the connection string is updated and then remove the table that was added.


    Regards,

    Michael Amadi

    Please use the 'Mark as answer' link to mark a post that answers your question. If you find a reply helpful, please remember to vote it as helpful :)

    Website: http://www.nimblelearn.com
    Blog: http://www.nimblelearn.com/blog
    Twitter: @nimblelearn

    Friday, October 24, 2014 9:51 AM
  • Hi Michael,

    For some reason the Excel just won't update the connection info. After some more trying and updating, the Excel still doesn't update the info, but Powerpivot seems to retain the info in its connection somehow, maybe the excel side just stops updating once 'you go powerpivot on the connection'... It will probably stay OK, and if not, maybe it will start nagging once the old sql server goes offline, maybe it will update more thoroughly at that time... Well see. To bad it doesn't just work instantly or does not allow me to edit the connection Excell side.

    Probably the best thing would be to delete all tables (belonging to that connection) in powerpivot, delete the connection, and reconnect and rebuild the data model... It would seem that -apart form some error messages relating to explicit calculations- the excel side stays ok and the pivottables stay in place. Then again... probably best to make the whole thing anew while you're at it... Meh.

    We'll see what happens when the old server goes offline, empyric IT at its best :)

    Kind rgds,

    Sven


    • Edited by Sven_vD Thursday, October 30, 2014 1:53 PM
    Thursday, October 30, 2014 1:52 PM
  • I'm having the same problem (using OData feeds), and adding another table did not solve the problem.   You can design the table, refresh the preview under Table Design, you can even add the table again using the same connection, but you cannot refresh the table still.  This rules out security, and I've had no luck with modify the connection in the file directly (renaming to .zip and editing the connections.xml).   It seems that once a PowerPivot based spreadsheet is setup, then any changes to it or the underlying datasource are tricky without recreating the whole sheet ... and this monster took me 2 days!    Any other suggestions would be gratefully received.

    Many thanks in advance.

    Tom.

    Tuesday, December 16, 2014 9:54 AM
  • Also need a solution for this. We moved our data to a different server, and we cannot update our Powerpivot based Worksheets.

    We can change the server in the powerpivot connection, but the Excel connection is never updated! Using excel 2013


    • Edited by Ghetz Wednesday, December 7, 2016 9:56 AM
    Wednesday, December 7, 2016 9:55 AM
  • This does not work for me.  I need a solution for this I can't recreate 20 reports.  This is a bug that Microsoft needs to fix or I'm going to start using Tableau.  I've spent days on the web looking for a solution.  This only happens with ODBC and Oracle connections.  PLEASE FIX THE PROBLEM!!!!!!!!!!!!!!!!!!!!!!!!!!!!
    Wednesday, January 18, 2017 2:10 PM
  • I got around this bug by copying all tabs into a new workbook.  The data model gets copied over as well with everything in sync.  Not sure if this would work for all of these cases but worked for me.  
    Tuesday, February 14, 2017 2:33 PM
  • That works!  THANK YOU!!!!!!!  OMG that saved me.  Wish I would have thought of that.
    Tuesday, February 14, 2017 7:07 PM