Can I Automate an ODBC connection Refresh AND then save workbook by a scheduled task?

[Garry Alweyn]

Hi Community,

I am using an excel workbook to grab data from an accounting system via an ODBC32 bit connection.

Is there a way to schedule these ODBC connections inside the workbook to refresh the data and resave the workbook at a regular interval?

Thanks for any advice in advance!

Cheers

Garry

[#2 · Accepted solution]

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

[#3]

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.

[#4]

I guess the powerpivotpro option is a good one.

[#5]

Please can  you share photos steps to be more cleared ,   thank you   @JKPieterse