Power Query refresh in Task Scheduler job not working

[Hans Troost]

Dear all,

I developed an application with 6 Powerqueryes (4 from an Access database, 1 from a textfile, and 1 from another XL).
These queries result in a table and runs a macro 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).

Works perfectly from the desktop, runned 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 the whole day figuring it out - yes google, google and google and try, try 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.

Option Explicit

Private Sub Workbook_Open()
Dim Connection As Variant

    For Each Connection In ThisWorkbook.Connections
        Connection.OLEDBConnection.BackgroundQuery = False
    Next Connection

    With Application
        .WindowState = xlMinimized
        .ScreenUpdating = False
        .DisplayAlerts = False
        .ThisWorkbook.RefreshAll
'       .Run "'" & ThisWorkbook.Name & "'!Uitdeellijst"
        .ScreenUpdating = True
        .DisplayAlerts = True
    End With
    
        Application.ThisWorkbook.Close False
        Application.Quit
End Sub

Any help appreciated,
kind regards

Hans Troost

 

[Fluff]

Cross-posting (posting the same question in more than one forum) is not against our rules, but the method of doing so is covered by #13 of the Forum Rules.

Be sure to follow & read the link at the end of the rule too!

Cross posted at: vba Power Query refresh in Task Scheduler job not working
If you have posted the question at more places, please provide links to those as well.

If you do cross-post in the future and also provide links, then there shouldn’t be a problem.

 

[Hans Troost]

Cross-posting (posting the same question in more than one forum) is not against our rules, but the method of doing so is covered by #13 of the Forum Rules.

Be sure to follow & read the link at the end of the rule too!

Cross posted at: vba Power Query refresh in Task Scheduler job not working
If you have posted the question at more places, please provide links to those as well.

If you do cross-post in the future and also provide links, then there shouldn’t be a problem.

Apologies for this very late reaction - due to personal circumstances. I cross-posted hoping to get suggestions from other communitities.

I posted nearly the same text - small modifictation - here: vba Power Query refresh in Task Scheduler job not working