Troubleshooting Excel PowerPivot Update

Troubleshooting Excel PowerPivot Update

If you are using Excel 2010 and Power Query and have an issue during UI Automation, or if the task succeeds but the data doesn’t update please refer to the related article.

Otherwise please check the error messages in the C:\Users\[USER]\AppData\Local\Temp\PU.log file and turn on Options\Diagnostic Mode to see if Excel displays any popup messages during the update process and check for the symptoms below:


Opening file failed

This is usually either due to the file being locked or the file taking too long to open (large model or slow computer).


Resolutions

Try checking Options\Disable Parallel Tasks to make sure another task is not locking the file

If the file takes long to open because of a large model, try increasing the WaitAfterOpen, WaitBeforeRefresh, WaitBeforeProcess2010 (ms) settings in C:\Program Files\PowerPlanner\Power Update\PQRefresh.exe.config (copy the file to desktop, change setting in notepad, save, and copy it back), to 30000-200000 depending on how long the workbook takes to open.


Refreshing connection failed

This is usually due to the an issue with one of the connections (e.g. network error, query or data error, old password, password not supplied, etc).


Resolutions

Check if you can manually refresh the workbook and make sure all connections are working.

If using Excel 2010 check that the Power SQL Excel Addin is installed and enabled (both in Excel and Power Update Options)

If it’s a temporary / intermittent issue during refreshing connections, set the Retry Count and Retry wait settings in the Advanced Settings page of the task wizard (e.g. Retry Count: 5, Retry Wait: 5 min). If the Refresh hangs sometimes, also set up the following settings in Advanced Properties\Settings:

  • Stop the taks if runs longer than X minutes (based on the normal run time)
  • If the task fails restart every X minutes (if the refresh issue is caused by a network problem leave a few minutes for it to recover)

If Excel prompts for a password during refresh try:

– Use Options\Data source passwords. The DS Name should match the name of the connection (case sensitive) in PowerPivot

– Save the password in the workbook:

  • Go to Excel\Data\Connections\YOUR CONNECTION\Properties\Definition\Save password

  • Do a Refresh All in Excel (enter the password when prompted)

  • Save the workbook

– Use Power Query to import data, which stores the password on the computer not the workbook


Excel could not be started

This is usually due to an Excel installation issue (e.g. another version of Excel was recently installed uninstalled, or current installation is broken)


Resolutions

  • Try the following registry fix:
  1. Open the regedit editor.
  2. Open HKEY_CLASSES_ROOT >> TypeLib >> {00020813-0000-0000-C000-000000000046} (The Excel PIA key is {00020813-0000-0000-C000-000000000046})
  3. Delete the version folders that don’t refer to the current version of Excel (Excel 2010 is 1.7, Excel 2013 is 1.8, Excel 2016 is 1.9)
  4. Set the proper Version in HKEY_CLASSES_ROOT\Interface\{00020843-0000-0000-C000-000000000046}\TypeLib (Excel 2010 is 1.7, Excel 2013 is 1.8, Excel 2016 is 1.9)


  • Try to repair/reinstall Excel
  • Try on a different computer


RPC_E_DISCONNECTED or RPC_E_CALL_REJECTED

This is usually happens when Excel is busy and not responding due to a working on large model or multiple refreshes running at the same time.


Resolutions

  • Try installing .NET 4.6.2
  • Try checking Options\Disable Parallel Tasks
  • Try checking Options\Diagnostic mode to see if there are any popup messages displayed by Excel
  • If you are using Excel 2013 or 2016 try setting the RefreshEachConnection setting to False in C:\Program Files\PowerPlanner\Power Update\PQRefresh.exe.config (copy the file to desktop, change setting in notepad, save, and copy it back). This does a Refresh All instead of Refreshing connections individually (which could be faster too)
  • If the model is large, try increasing the WaitAfterOpen, WaitBeforeRefresh, WaitBeforeProcess2010, WaitBeforeSave, WaitAfterRefresh, WaitBeforeClose (ms) settings in C:\Program Files\PowerPlanner\Power Update\PQRefresh.exe.config (copy the file to desktop, change setting in notepad, save, and copy it back), to 10000-200000 respectively depending on how long the workbook takes to open, save, close.
  • Try changing IgnorePopups setting in PQRefresh.exe.config (or a combination of that and the IgnorePopupsWithEmptyTitle setting)


Other issues

If the above doesn’t resolve the issue, contact us and send us the:

             – C:\Users\[USER]\AppData\Local\Temp\PU.log, PU_UI.log files
             – C:\Users\[USER]\AppData\Local\Power Update\Settings.xml file
             – Power Update version in Control Panel\Programs\Power Update\Version
             – Excel version


    • Related Articles

    • Troubleshooting Excel 2010 UI Automation

      If you are using Excel 2010 and Power Query data sources Power Update needs to perform UI automation. If UI automation fails for some reason please try the common resolutions below: Check if you can manually refresh the workbook and make sure all ...
    • Power Update Maintenance

      Power Update is sold by a one-time fee that gives users a perpetual license to use the software forever. PowerON offers an optional maintenance contract in order to receive updates for the software. ​ Purpose of the document In this article, you will ...
    • Troubleshooting Power BI UI Automation

      To troubleshoot the update of pbix files using UI Automation, you first need to watch the process and identify the part where the UI automation fails: Issue at the Start of the update: If the update process fails at the start (before or shortly after ...
    • Power SQL Update not loading in Excel

      Search the registry under both Current user and Local Machine for: PPSQLConnector and set the LoadBehaviour to 3 at all occurances e.g.: If the above doesn't resolve the issue, search for Addins in the registry under both Current user and Local ...
    • Troubleshooting SharePoint upload issues

      The main causes of upload issues is due to the following: - A firewall or proxy blocking the upload - Authentication issues (make sure username and password authentication is enabled) - File size limits: ...