VBA Open Workbook, Refresh Query, Save and Close

[paddyk]

Hi,

I am completely new to VBA and to be honest I don't really know what I am doing. I've pieced the below code together but It doesn't run when the workbook is opened, and if I start it manually it saves and closes before the query refresh is completed. Where am I going wrong? Aim is to open the excel file using a batch file and windows task scheduler, refresh the query, save the file and close.

Sub Workbook_Open()
    Call RefreshQuery
    ActiveWorkbook.Save
    If Application.Workbooks.Count = 1 Then
        Application.Quit
    Else
        ActiveWorkbook.Close
    End If
End Sub

Sub RefreshQuery()
'
' RefreshQuery Macro
'

'
    ActiveWorkbook.Connections("Query").Refresh
    
End Sub

 

[EXCEL MAX]

I suppose that query have no time to be uploaded before macro close workbook.
Try to use OnTime method.
Application.OnTime Now + TimeValue("00:00:05"), "my_Procedure"
Try to create procedure in Module1 called "RefreshQuery"

Sub Workbook_Open()

    ActiveWorkbook.Connections("Query").Refresh
    ActiveWorkbook.Save
    Application.OnTime Now + TimeValue("00:00:05"), "RefreshQuery"

End Sub

Sub RefreshQuery()

    If Application.Workbooks.Count = 1 Then
        Application.Quit
    Else
        ActiveWorkbook.Close
   End If

End Sub

 

[Hans Troost]

Hi,

I am completely new to VBA and to be honest I don't really know what I am doing. I've pieced the below code together but It doesn't run when the workbook is opened, and if I start it manually it saves and closes before the query refresh is completed. Where am I going wrong? Aim is to open the excel file using a batch file and windows task scheduler, refresh the query, save the file and close.

Sub Workbook_Open()
    Call RefreshQuery
    ActiveWorkbook.Save
    If Application.Workbooks.Count = 1 Then
        Application.Quit
    Else
        ActiveWorkbook.Close
    End If
End Sub

Sub RefreshQuery()
'
' RefreshQuery Macro
'

'
    ActiveWorkbook.Connections("Query").Refresh
   
End Sub

I suggest to take a look here: https://www.myonlinetraininghub.com...macro-until-power-queries-finished-refreshing, post n4. 4 the citation of an answer somewhere else from Gregory Regan. I used this in my workbook with 6 queries of which the last the suggested "dummy" query ZZ_dummy. It simply works for me: it seems that Gregory is right that workbook.connections.refreshall is refreshing the queries in alphabetical order. Be sure to have Background Refresh set to False.
See my code here: Power Query refresh in Task Scheduler job not working

Regards, Hans Troost