Forum Discussion
PowerQuery fail when run via VBA
Hello everyone! My scenario is like this: I must schedule an xlsm file to run on a remote machine, every night, which basically opens 30+ excel files with power queries to be updated on specific wo...
I ended up solving this and will share the solution in case anyone faces a similar issue in the future:
The problem was running the macro on auto open, because Excel starts running your code before loading the add ins, so it was running a power query refresh without the power query addin being loaded.
The solution was to open excel, wait for 10 seconds, and only then open the workbook. If automation is required, some code must be written to open Excel, wait 10 seconds, then open the workbook by sending shortcut keystrokes (ALT, O, O, etc).
- Aha! I think you can also solve this by not calling the refresh macro from Auto_Open directly, but by scheduling it from Auto_Open using Application.OnTime. This is an often used solution because indeed Excel triggers Workbook_Open or Auto_Open even before it has finished all of its startup tasks. Using Application.OnTime solves this as -even if you schedule it 0 seconds from now- this is not processed before Excel has finished with all of that.
