Excel VBA Power Query Task Scheduler Open

Question

I have an excel workbook with Workbook_Open macro that opens other workbooks and refreshes connections created in power query and power pivot. This workbook is opened by Task scheduler every day at 7:30 AM.

The problem is that when the task scheduler opens the workbook it runs the macro before Power Query and Power Pivot can load properly and in some cases connection refresh raises an error saying "Microsoft.Mashup.Oledb.1 not registered".

When I open the workbook manually everything works just fine. And when I open Excel before the task runs, it also works, because the addins are already loaded.

Is there a way I can tell excel to load Power Query and Power Pivot first and then execute the remainder of the code?

I also tried to reaload all addins first:

For Each CurrAddin In Excel.Application.AddIns
    If CurrAddin.Installed Then
        CurrAddin.Installed = False
        CurrAddin.Installed = True
    End If
Next CurrAddin

But that doesn't seem to be working here.

Answer 1

Try launching Excel from Task Scheduler some time before opening this workbook in Task Scheduler

Answer 2

Add this at the top of your module:

Public Declare Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long)

Then, in the beginning of the Workbook_Open function add

Application.DoEvents 'optional
Sleep(5000) ' or 10000 for 10 seconds
Application.DoEvents 'optional