Excel VBA Script Crashes When Refreshing Power Queries Using Task Scheduler

I have an xlsm file with a macro that on open, it will launch a Master xlsx of my choice, refresh all the Power Queries (20+ queries), save the Master file and quit excel. The macro file is 20k and the master file is 2MB.

If I open the xlsm manually, the queries all refresh as normal in under 30 seconds. However if I use task scheduler or any windows automation software to open that xlsm, excel goes non responsive and i have to force quit.

Anyone experience anything like this before? This same code works on all other PQ files task scheduler refreshes and those range from 10MB-20MB, however have far less queries to refresh.

Here is the VBA code I'm using.

Please Login or Register  to view this content.

Hi,
Profile is old. I am using excel 2016 64 bit

Originally Posted by MarvinP

Because you are going to the OS to do stuff, I'd put a bunch of "DoEvents" in your code. The DoEvents wait until the OS comes back to Excel before sending more commands. See it that helps.

Im guessing the queries are causing the crash so I would need to separate every query call rather than refresh all? I know a little code but mostly pieced this code together from forums a year ago.