February 9, 2018 at 12:59 pm #9139
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?
- do I need to keep the workbook open as it is closed?
- 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)
- Does power update work refreshing a bi publisher workbook or some reason it wont?
MikeFebruary 9, 2018 at 3:12 pm #9144
- 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.February 9, 2018 at 5:02 pm #9146
great i’ll try that. Seems odd refresh all isnt the defaultFebruary 9, 2018 at 10:17 pm #9150
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
You must be logged in to reply to this topic.