Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 398
  • Last Modified:

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.
0
flubbster
Asked:
flubbster
1 Solution
 
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

Featured Post

Vote for the Most Valuable Expert

It’s time to recognize experts that go above and beyond with helpful solutions and engagement on site. Choose from the top experts in the Hall of Fame or on the right rail of your favorite topic page. Look for the blue “Nominate” button on their profile to vote.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now