Hourly Automated refresh

Question

I would like to have my spreadsheet receive an automated update/refresh.  I have operations based on the time of day

and they are to activate on the hour.

I can send "Range("A1").Activate  which performs the update but it is manually triggered and not based on the time.

VB uses a timer but I see no timer in VBA or Excel.

Answer 1

It absolutely has to be on the hour.

Here is what I have developed and am testing.

I calculate the minutes remaining until the next hour.

Add that to the present time to pass the value of the next hour to the macro.

It seems to be working.

I will post the result in a few hours....

Private Sub Workbook_Open()

    TimeLeft = 60 - Minute(Now)

    AlertTime = Now + TimeValue("00:" & TimeLeft & ":00")

    MsgBox ("In " & TimeLeft & " minutes it will be " & AlertTime)

    Application.OnTime AlertTime, "EventMacro"

End Sub

And in the EventMacro in a module:

Public Sub EventMacro()

    'MsgBox ("In " & TimeLeft & " minutes it will be " & AlertTime)

    Range("B1:B1").Value = 42

    AlertTime = Now + TimeValue("01:00:00")

    Application.OnTime AlertTime, "EventMacro"

End Sub

Theory is that no matter when I run the program, it will seek out the nearest hour and update the spreadsheet and continue to refresh every hour and on the hour.

By placing 42 into B1, the spread sheet updates and every 60 minutes from then the spread sheet updates again.  I imagine some time-error can happen because of CPU time but I figure the error eill be insignificant for what I am doing.

Once again, I am up until after 3am programming.....

Answer 2

Hey, IT'S WORKING!!!  Yeah....

I answered my own question.

The code is there for all to enjoy....

Answer 3

Hi

Trap a workbook event (Workbook_Open is a good one) to trigger some code, and then use that code to do what you want at timed intervals.

e.g In the workbook events

Private Sub Workbook_Open()

   UpdateMySheet

End Sub

And then in a module:

Sub UpdateMySheet()

   '

   ' Place your code here to do what is required

   '

   Application.OnTime Now + TimeValue("01:00:00"), "UpdateMySheet"

End Sub

This won't update 'on the hour' but rather every hour from when the workbook is opened, but you could manipulate  it with some maths if it must be on the hour.

Regards

Murray