Power Query refresh in Task Scheduler job not working

Hans Troost

New Member
Joined
Jan 6, 2015
Messages
24
Office Version
  1. 2019
  2. 2016
Platform
  1. Windows
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.

VBA Code:
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
 

Excel Facts

Save Often
If you start asking yourself if now is a good time to save your Excel workbook, the answer is Yes

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
62,383
Office Version
  1. 365
Platform
  1. Windows
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

New Member
Joined
Jan 6, 2015
Messages
24
Office Version
  1. 2019
  2. 2016
Platform
  1. Windows
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
 

Forum statistics

Threads
1,141,299
Messages
5,705,574
Members
421,399
Latest member
hjweiss00

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top