Run a macro every xx seconds

Greetings all. I have a macro that I developed that goes out and reads a database and populates a spreadsheet with the information. The database consists of over 50 test systems and I have Labview code that reads a status from the test systems and populates the database with it. The spreadsheet gets updated every time the user presses a hotkey combination. It all works the way I want.

My question here is:

Once the macro executes when the hot key is pressed, how can I have the macro repeat every xx seconds?

Below is the actual code that populates the spreadsheet:


Sub Test1()
''This code retrieves all records
    GetDataFromAccess "Z:\Database\Test Node Status Database.mdb", "TestNodeStatus", _
                      "", "=", "", _
                      "", "=", "", _
                      "", "=", "", _
                      "", "=", "", _
                      "", "=", "", _
                      "", "=", "", _
                      "", "=", "", _
                      Sheets("Test Node Status").Range("A1"), _
                      "*", True, True
End Sub

This macro calls a larger one which goes out and retrieves the data. I hope I am correct in my thinking that I only need to keep repeating this piece to actually update the spreadsheet.
LVL 30
flubbsterAsked:
Who is Participating?
 
sbaughanCommented:
Try to add this at the end of the run:
Application.OnTime Now + TimeValue("00:00:30"), "Test_1"

Change the time offset in the string to match your need

Yes, if I understand well, you call the main function and it updates your data
0
 
Don ThomsonCommented:
Simplest way is the Right Mouse Click on the Macro and Create A shortcut - Put it somewhere safe.

Then use the Task Scheduler to run the macro every XX seconds
0
 
flubbsterAuthor Commented:
Should have thought to ask this before closing the question. Once the macro is running (I have a hotkey, ctrl-r, to start it), is there a way to stop it? Assume that it is running every 30 seconds as you answered, is the only way to stop it updating by shutting it down?
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.