Forum Discussion

Garry Alweyn's avatar
Garry Alweyn
Copper Contributor
Oct 14, 2017
Solved

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

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 refres...
excel
Macros and VBA
  • JKPieterse's avatar
    JKPieterse
    Oct 16, 2017
    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
JKPieterse's avatar
JKPieterse
Silver Contributor
Oct 16, 2017
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
Garry Alweyn's avatar
Garry Alweyn
Copper Contributor
Oct 17, 2017

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.

 

 

 

  • JKPieterse's avatar
    JKPieterse
    Silver Contributor
    Oct 18, 2017
    I guess the powerpivotpro option is a good one.

Resources