Forum Discussion
Can I Automate an ODBC connection Refresh AND then save workbook by a scheduled task?
- In the queries properties you can set a scheduled refresh time. But a scheduled saving time must be done using a bit of VBA code. Best to combine this with the refresh rather than scheduling that refresh. In a normal module:
Option Explicit
Dim mdNextTime As Double
Sub Unschedule()
'Prevent error in case nothing was scheduled
On Error Resume Next
'Unschedule pending ontime proc
Application.OnTime mdNextTime, "UpdateRoutine", , False
End Sub
Sub UpdateRoutine()
'Update routine goes here
ThisWorkbook.Worksheets("Sheet1").ListObjects(1).QueryTable.Refresh False
ThisWorkbook.Save
mdNextTime = Now + TimeValue("00:10:00")
Application.OnTime mdNextTime, "UpdateRoutine"
End Sub
In your ThisWorkbook module:
Option Explicit
Private Sub Workbook_BeforeClose(Cancel As Boolean)
Unschedule
End Sub
Private Sub Workbook_Open()
UpdateRoutine
End Sub
Hi JKPieterse
thanks for the reply. I saw your VB code but my skills in this area aint that good. I probably need to study more ..lol
I have a lot of SQL queries in the work book to update each one's properties would take a bit of time. Each queries connects to an accounting data table like accounts, jobs, journals, items, itemsales etc,
I ended up doing a vbscript below which i scheduled in the task manager to each time period.
Set xl = CreateObject("Excel.Application")
Set wb = xl.Workbooks.open("C:\Users\Garry\Desktop\Test\PRIMETEST.xlsx")
xl.DisplayAlerts= False
WScript.Sleep 1000
wb.RefreshAll
WScript.Sleep 10000
wb.Save
wb.Close
xl.Quit
This works okay with a file stored on a local disc.
Are there better ways to do this and access the refresh all and save on an excel file stored on say:
1. One Drive Personal or Business
2. Share Point Online
I am considering Power Update software which also works on Power Bi desktop files as it looks like it handles all the scenarios.
https://powerpivotpro.com/2015/02/introducing-power-update/
Its free for a single file.