Need help automatically refreshing a worksheet

[Richard7]

I'm sure that this has been posted, or variations thereof, ad nauseam, but I could r-e-a-l-l-y use someone's help. Also, I apologise if this is a duplicate, but I'm at wits end as to how to get this thing to work.

I'm a v-e-r-y beginner programmer at VB programming and I don't have a clue as to what I'm doing. I've tried looking through some threads but can't find the basics of what I want. So, here goes....

I am trying to write a script that simply refreshes a worksheet. I don't need it to launch a rocket, but refresh a worksheet. Since I don't have a clue as to what I'm doing (I'm a beginner, remember?), I thought I'd try to piece code together that I've found on this, and other, websites.

From what I gather, my code should go into "ThisWorkbook" on the lefthandside, under "VBAProject(DOS filename goes here).

That's all that I've managed to accomplish.

Here's "my" code (actually, it belongs to some brainchild who wrote it):

Dim ThisTime As Date


Sub StartDoingIt()
DoItAgain
End Sub


Sub DoItAgain()
ThisTime = Now + TimeValue("0:00:01")
Application.OnTime ThisTime, "DoItAgain"
DoIt
End Sub


Sub DoIt()


'insert your macro here


End Sub


Sub StopDoingIt()
On Error Resume Next
Application.OnTime ThisTime, "DoItAgain", schedule:=False
End Sub


Sub Auto_Close()
StopDoingIt
End Sub




Private Sub Workbook_Open()


End Sub


All that I want to have happen is for my worksheet (called: "TESTING DATA") to be refreshed automatically. That is, I want it to refresh as though I'm sitting at my keyboard and pressing F9 all day long, but I don't want to sit here all day.

So, can someone tell me where to put the code, and how to get it to run? Also, if the above-mentioned code won't work, can someone (if you can get it to work, you will forever be known as "THE" Brainchild!), provide the proper code and provide me with the instructions as to how to get the new code to run automatically?

I would pay you, but I have legal obligations to pay my ex (=SATAN/BEELZEBUB/LUCIFER) before you. I will, however, put you on my Christmas Card list. Fair enough?

Many thanks!

 

[pbornemeier]

My first thought was "Why do you want to refresh your workbook every second?"

One instance of the loop will start automatically when the workbook is opened.
The code will close one instance of the loop when the workbook is closed.
If you run the StartDoingIt sub again, a second loop will be started.
If you run the StartDoingIt sub again, a third loop will be started, etc.

If you have started multiple loops, you must use StopDoingIt to close those loops. Due to timing issues, you may have to run StopDoingIt more times that you ran StartDoingIt to get all loops to terminate.

As long as Excel is open the loop(s) will continue to run unless you manually stop them, even if the workbook is closed because the OnTime function will reopen the workbook at the scheduled time...which will then start another loop.

This code will do what you ask if you change the time seconds back to 01 :

'All code above the ========== row should be in a standard module
Option Explicit

Dim ThisTime As Date

Sub StartDoingIt()
   'Running this sub multiple times will cause the
   'DoItAgain code loop to run multiple times
   DoItAgain
End Sub

Sub DoItAgain()
   ThisTime = Now + TimeValue("0:00:10") 'I recommend against 1 second, changed it to 10
   Application.OnTime ThisTime, "DoItAgain"
   DoIt
End Sub

Sub DoIt()
   DoEvents
   Calculate 'This is the same as pressing the F9 key
   DoEvents
   Application.StatusBar = "Doing it at: " & Format(Now, "m/d/yyyy hh:mm:ss")  'Displays last update time on statusbar
                                                   'Not needed, but it shows you when code triggers
End Sub

Sub StopDoingIt()
   On Error Resume Next
   Application.OnTime ThisTime, "DoItAgain", schedule:=False
   Application.StatusBar = False 'Returns the statusbar control to Excel
End Sub

'==============================================================
'All code below the ========== row should be in the ThisWorkbook module
Option Explicit
Private Sub Workbook_Open()
   'This sub should be in the ThisWorkbook code page
   'It will start the auto refresh when the workbook is opened
   DoItAgain
End Sub

Private Sub Workbook_BeforeClose(Cancel As Boolean)
    'This sub should be in the ThisWorkbook code page
    'It will stop the autofresh when the workbook is closed
    StopDoingIt
End Sub