Home Forums Power Update New users cant get excel workbook to refresh data

Tagged: 

This topic contains 4 replies, has 3 voices, and was last updated by  PentaGalDad 4 days, 16 hours ago.

Viewing 5 posts - 1 through 5 (of 5 total)
  • Author
    Posts
  • #9139

    mikeasplin
    Participant
    • Started: 1
    • Replies: 2
    • Total: 3

    I have a large Power BI model running on a 2 hour refresh in service.  I have an excel workbook pulling down various tables using BI Publisher. i’d like power update to refresh the data in my BI publisher workbook and save a copy.

    I though I had set this up correctly using the 1 hour indefinitely settings and sure enough it is running and saving a copy of the workbook. The problem is it is just the same workbook everytime i.e. it doesn’t actually refresh any data.  I can open the workbook and hit refresh after the service has run and seems fine. I assumed refreshing the workbook as the default action?

    1. do I need to keep the workbook open as it is closed?
    2. Do i need to write a macro to refresh the data and then add the macro to the power update settings (seems weird to have to)
    3. Does power update work refreshing a bi publisher workbook or some reason it wont?

    Thanks

    Mike

     

     

    #9144

    Charles
    Moderator
    • Started: 2
    • Replies: 187
    • Total: 189

    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.

    #9146

    mikeasplin
    Participant
    • Started: 1
    • Replies: 2
    • Total: 3

    great i’ll try that. Seems odd refresh all isnt the default

    #9150

    mikeasplin
    Participant
    • Started: 1
    • Replies: 2
    • Total: 3

    Unfortunately just errors. i opened the workbook and hit refresh and refreshes fine no pop ups.  Is it relevant that it is a BI publisher connection?

     

    Refreshing All failed in workbook CRM Datasource.xlsx. Please check that all connection details are correct and all connections can be manually refreshed, without any popup dialogs that require user intervention. You can also enable Options\Diagnostic Mode and re-run the task to see actual operation. Detailed error code:
    Exception from HRESULT: 0x800A03EC Trace: at Microsoft.Office.Interop.Excel._Workbook.RefreshAll()
    at PQRefresh.Program.RefreshWb(Workbook wbk, String fileName) in S:\dev\Projects\PP\PQRefreshService\PQRefresh\Program.cs:line 4883

    #9806

    PentaGalDad
    Participant
    • Started: 4
    • Replies: 7
    • Total: 11

    I see this is an older thread, but I have the same problem. I am trying to use Power Update to refresh an Excel workbook that is based off OLAP connection to the Power BI data model (aka “Analyze in Excel”). I can manually refresh the Excel workbook just fine. Power Update will run just fine and say the task was successful. However, the workbook did not refresh. I can tell this because the data is old AND the Power Update task executes in less than a minute and a manual refresh takes 5 minutes. So this tells me Power Update isn’t even trying to refresh the OLAP connection. Last week I stumbled on this forum entry and thought that as suggested above setting “RefreshEachConnection” to False fixed the issue as the Power Update task took 5 minutes to complete (my source data hadn’t changed so I couldn’t confirm it actually refreshed). However today my source data is updated and Power Update fails to refresh the connection (no new data in the report) and the task is completing in under a minute. Other suggestions???

Viewing 5 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic.