Open excel power query refresh and save on a schedule using windows task scheduler

[Katyj]

I would like to open, refresh and save an excel power query that accesses my companies database using ODBC. I would also like to use windows task scheduler to do this every 4 hours.

Can anyone help with this? I have no experience with VBA and if that is what is needed then if someone could direct me to a source to learn VBA I would be most appreciative. Thanks

 

[JB2020]

You can set your queries to refresh on a set interval with power query, so you could use that to automatically refresh every 4 hours. This would require you leaving the workbook open in the background, I'm not sure how much of an issue this would be. If you were planning on using task scheduler then presumably your computer would need to remain on for that to work.

This still leaves the problem of saving it. You could use an event in VBA such as the change event to do the save, such as :

Private Sub Worksheet_Change(ByVal Target As Range)

    If Not Intersect(Target, Me.ListObjects("TableName").Range) Is Nothing Then ThisWorkbook.Save

End Sub

This code would go into the sheet object which the power query output table is in (and "TableName" is replaced with the name of the table). This checks to see whether that table has changed and saves if it has.
Alternatively, if you can use OneDrive, you could upload it to that and use the autosave feature to remove the need for VBA.
If you do use OneDrive, you could also look into using Power Automate and schedule it to refresh through there. This would mean you would no longer need to have the file open in the background.

Hope this helps.