vba Power Query refresh in Task Scheduler job not working

First of all this is a cross-posting: after 2.5 days without reply I don't expect that anymore. Link to original:

- since this is my first post I'm not allowed to post the link. It isfrom MrExcel forum with this title: "Power Query refresh in Task Scheduler job not working";

Modified the text a bit from the original:

Dear all,

I developed an application with 6 Power Queries (4 from same Access database, 1 from a textfile, and 1 from another XL). (Excel 2016, Get & Transform)
These queries combine data and result in one table. Then a macro is runned to copy the table to a new workbook, reformat and save it, while not saving the spreadsheet containing the PQ's, macro and the PQ-result-table).

The code to refresh and run the macro is the the Workbook_Open to get is automated.

Works perfectly from the desktop - just double click and wait till it is closed, I ran it very often during development of the formatting etc. -macro.
I however need it to run over night in a scheduled task. That is where the problem starts: it keeps running (hangs) and the PQ's are not refreshed.

I spent nearly a whole working day figuring it out - yes google, google and google and try, try and try, but did not succeed. Please take a look at my workbook_open macro. Any suggestion is very welcome. I commented out the macro: when the macro starts, it gets stuck due to lack of proper data - happens irregularly, now and then, but most of the times the macro does not start at all: program keeps hanging. I see it when I cancel the running job.

VBA Code:

Please Login or Register  to view this content.

Any help appreciated,
kind regards

Hans Troost

Thanks, for your reply - I got the error message that I couldn’t post the link, but here it is:

https://www.mrexcel.com/board/thread...rking.1168112/

Thanks again, Hans