0

I have dozens of Excel workbooks that need to be updated daily so that our employees have up-to-date data for their day-to-day work. These workbooks keep our employees from having to spend all day clicking around in our ERP system trying to find the data. These workbooks consolidate the data needed on a day-to-day basis.

Each user workbook has a Power Query query in each one that pulls in the new data.

However, I have not yet been able to find a way to refresh each workbook programmatically, meaning without having to open each workbook.

The source for the Power Query queries is an Excel Workbook on a SharePoint site.

To refresh the query manually takes between 1-55 minutes depending on the workbook due to them using different data sources.

And, no, the end user cannot update the queries for themselves because they 1. won't have access to the underlying data and 2. won't have the patience to wait for the query to refresh.

Ideally, on a nightly basis each workbook will have its Power Query queries refreshed so that when the end user opens up the workbook in the morning, the workbook has the refreshed data already included.

I've tried VBA. The results are unreliable at best because I'm not able to get the script to wait until the query is fully refreshed before save/close.

I've tried Power Automate. The results are unreliable at best because I'm not able to get it to make sure and wait until the query is fully refreshed before save/close.

I've tried Microsoft Graph API. However, I cannot seem to find a refresh command that will refresh Power Query.

Other options I'm considering include:
Excel Javascript API
Office Online Server
Office Data Connection File
Excel Primary Interop Assembly
Excel Web Access Web Part

Note: I know little about these other options at the moment so please excuse any that are nonsensical for this use case.

Ben Bennett
  • 21
  • 3
  • From the VBA perspective, you could put the file-saving code in the [AfterRefresh](https://learn.microsoft.com/en-us/office/vba/api/excel.querytable.afterrefresh) event procedure, although you would first have to create a [class module](https://learn.microsoft.com/en-us/office/vba/excel/concepts/events-worksheetfunctions-shapes/using-events-with-the-querytable-object) in order to be able to access it. – Spectral Instance Mar 17 '23 at 23:06
  • I'd rethink your whole solution to be honest. Why not build something for your end uses in PowerBI and put that on a refresh schedule. Your users can connect Excel to the data model or use your PowerBi report to do their analysis. – Davide Bacci Mar 18 '23 at 13:42
  • @DavidBacci, I agree completely. However, the end users are not proficient when it comes to Excel and most have never heard of Power BI. So, I cannot ask them to use Excel to connect to the data model (or something similar). – Ben Bennett Mar 20 '23 at 04:10
  • To get VBA to "wait" until the refresh is finished on a report, make sure all of your queries have "Background Refresh" disabled – CHill60 Mar 20 '23 at 16:01
  • Thanks @SpectralInstance ! Will I have to store the class module IN each workbook, thus requiring each end-user workbook to be a .xlsm file? OR, can the class module live in my Personal Macro Workbook so that the end-user workbooks could just be regular .xlsx files? – Ben Bennett Mar 20 '23 at 16:19
  • 1
    @DavidBacci, I concur w/ you. Once things start getting THIS complicated, the process should be reconsidered. – Dolunaykiz Mar 20 '23 at 17:27
  • Power BI reports are published to the online service, you don't have to do anything as an end user, apart from maybe refreshing a visual. What you are describing is you forcibly trying to turn what should be a Power BI report or SQL database with custom views into an Excel workbook. I'd personally look into Power BI and connect that with [data flows](https://learn.microsoft.com/en-us/power-query/dataflows/overview-dataflows-across-power-platform-dynamics-365), if it's all on SharePoint. – Dolunaykiz Mar 20 '23 at 17:32
  • @BenBennett, not according to [this](https://stackoverflow.com/questions/68243390/excel-vba-how-to-use-early-binding-with-class-module-in-personal-workbook) post, but the end-user files would still need to be xlsm files. If I were in your situation then I'd simply have a shell script scheduled to launch a 'master updater' xlsm file, which loops through all the queries and, for each user, exports a simple (ie connection- and macro-free) xlsx file containing just the data appropriate for that user and nothing more. – Spectral Instance Mar 20 '23 at 18:07
  • How are the users ‘consuming’ the PQ data in their workbooks - Are you just loading the queries to tables and then they’re doing what they want with them or are you throwing pivot tables over the PQ tables (either directly or through PowerPivot/the Excel data model)? If it’s the latter (or even if they’re using PT’s themselves then loading the data into a PBI dataset (and dealing with the refreshes there) and having workbooks with PT’s connected to the PBI dataset would be an easy ‘fix’. – Ben Mar 21 '23 at 20:11
  • On the other hand, if your users aren’t comfortable with PT’s and are just looking at the data in the PQ tables and perhaps throwing some formulas off to the side then creating a PBI dataflow (to deal with the refreshing of data) and pointing your PQ tables at this as the source rather than the current file/db/whatever sources could be a smoother way to do things - depending on the size of your data set you could have the PQ tables set to refresh on open and they just pull the data from the PBI dataflow presumably much quicker than from all the disparate original sources – Ben Mar 21 '23 at 20:15

0 Answers0