Refresh

C

ceemo

I have a workbook with approx 5 imports on different worksheets and i
would like to refresh all them at precisely 18 minutes past every
hour.

Can this be done?
 
A

Ardus Petus

Option Explicit

Dim dNext As Date

Sub Auto_Open()
dNext = TimeSerial(Hour(Now) + IIf(Minute(Now) < 18, 0, 1), 18, 0)
Application.OnTime dNext, "refreshdata"
End Sub

Sub refreshdata()
MsgBox Now
dNext = TimeSerial(Hour(dNext) + 1, 18, 0)
Application.OnTime dNext, "refreshdata"
End Sub

Sub cancelTimer()
Application.OnTime dNext, "refreshdata", , False
End Sub

HTH
 
C

ceemo

yes my data is imported via a web page.

Will the code provided meet my requirement?

i see the code includes a msg box will it prompt each time befor
refresh as id like it to happen auto as its o display on a plasm
screen?


thank you for your hel
 
C

ceemo

Please see the previous posts.

I couldnt get them to work but i did manae to modify the code to th
below and it works for one refresh at 20 minutes past but doesn
refresh after that. want it to keep refresh over and over i
possible?

Please can you help?

Option Explicit

Dim dNext As Date

Sub Auto_Open()

dNext = TimeSerial(Hour(Now) + IIf(Minute(Now) < 20, 0, 1), 20, 0)
Application.OnTime dNext, "refreshdata"
Workbooks(ActiveWorkbook.Name).RefreshAll
End Sub

Sub refreshdata()
dNext = TimeSerial(Hour(dNext) + 1, 20, 0)
Application.OnTime dNext, "refreshdata"
Workbooks(ActiveWorkbook.Name).RefreshAll
End Sub

Sub cancelTimer()
Application.OnTime dNext, "refreshdata", , False
End Su
 
G

gareth93

If you go into the workbook options, and set calculation to manual on
the "Calc" tab, and tick, "recalculate before save".

Then go into the tools menu and select autosave (if its not there, you
can install it from the add-ins option, or download). Then just set the
autosave time to 60minutes, and it will recalculate all formulas before
saving and it won't prompt you if you uncheck the box.

Simple solution, dont know what your running on your sheet, but it
should recheck imported data too. Worth a try.


GoJo
 
C

ceemo

Thanks for the info but i would like to code this in VB.
Anyone have any suggestions?
 
C

ceemo

Im still looking for some help on this one.

Can anyone please take a look as im half way there?
 
Top