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 refresh the data and resave the workbook at a regular interval?


Thanks for any advice in advance!

Cheers

Garry

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