fbpx Skip to content

Troubleshooting Excel PowerPivot Update

insightsoftware -
August 4, 2023

insightsoftware is a global provider of reporting, analytics, and performance management solutions, empowering organizations to unlock business data and transform the way finance and data teams operate.

23 03 Wbn S4hana Undoimplementationmistakes Website

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 task 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 2013 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 2013 is 1.9)

154102 Thumb

  • 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