Run a macro/VBA every 5 seconds

1
votes

On an excel worksheet I have created several SQL Queries using Microsoft Power Query, These queries should be refreshed every 5 seconds so new information is retrieved from a SQ database.

To Refresh , I go to the Data Tab in Excel and hit the Refresh All button which refreshes all workbooks.This works good

I want to automate this request every 5 seconds Ideally,A macro should run the command Ctrl+Alt+F5 every 5 seconds in the background.

How can I achieve this

2
excelvba

2 Answers

2
votes

Simply add an Application.OnTime method to the end of a macro that reruns itself in 5 seconds.

Option Explicit

Public bWORKING As Boolean
Public bKEEPWORKING As Boolean

Sub deja_vu()
    'never let it run on top of itself
    If bWORKING Then Exit Sub
    bWORKING = True

    'do something here; refresh connections or whatever

    Debug.Print Now 'just to show it did something

    If bKEEPWORKING Then _
        Application.OnTime Now + TimeSerial(0, 0, 5), "deja_vu"
    bWORKING = False
End Sub

Assign bKEEPWORKING a value of True and run the Deja_vu sub procedure. It will keep running until you set bKEEPWORKING to False. Occasionally, I might skip the bKEEPWORKING and have it look up a value on a specific worksheet cell. If the value is 0 then the procedure does not reschedule itself.

1
votes

ThisWorkbook Module

Option Explicit
Private Sub Workbook_Open()
    Call StartTimer
End Sub

Private Sub Workbook_BeforeClose(Cancel As Boolean)
    Call StopTimer
End Sub

General Module

Option Explicit
Dim RunTime

Sub StartTimer()
    Application.ScreenUpdating = False
    ActiveWorkbook.RefreshAll
    RunTime = Now + TimeValue("00:00:15")
    Application.OnTime RunTime, "RefreshTime"
    Application.ScreenUpdating = True
End Sub

Sub StopTimer()
'  http://www.cpearson.com/excel/OnTime.aspx
    On Error Resume Next
    Application.OnTime RunTime, "RefreshTime", Schedule:=False
    On Error GoTo 0
End Sub

Sub RefreshTime()
    ActiveWorkbook.Save
    StartTimer
End Sub