SOLVED

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

Copper Contributor

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

4 Replies
best response confirmed by Garry Alweyn (Copper Contributor)
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

Hi @Jan Karel Pieterse

 

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.

 

 

 

I guess the powerpivotpro option is a good one.

Please can  you share photos steps to be more cleared ,   thank you   @Jan Karel Pieterse 

1 best response

Accepted Solutions
best response confirmed by Garry Alweyn (Copper Contributor)
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

View solution in original post