Refresh

[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?

[Ardus Petus]

Are your imports made thru Data>External Data ?

--
AP

"ceemo" <[email protected]> a écrit dans le
message de news: [email protected]...
>
> 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?
>
>
> --
> ceemo
> ------------------------------------------------------------------------
> ceemo's Profile:
> http://www.excelforum.com/member.php...o&userid=10650
> View this thread: http://www.excelforum.com/showthread...hreadid=550248
>

[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
--
AP

"ceemo" <[email protected]> a écrit dans le
message de news: [email protected]...
>
> 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?
>
>
> --
> ceemo
> ------------------------------------------------------------------------
> ceemo's Profile:
> http://www.excelforum.com/member.php...o&userid=10650
> View this thread: http://www.excelforum.com/showthread...hreadid=550248
>

[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 before refresh as id like it to happen auto as its o display on a plasma screen?


thank you for your help

[ceemo]

Please see the previous posts.

I couldnt get them to work but i did manae to modify the code to the below and it works for one refresh at 20 minutes past but doesnt refresh after that. want it to keep refresh over and over if 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 Sub

[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, or if it works for web queries, but it should recheck imported data too. Worth a try.


GoJo

[ceemo]

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

[ceemo]

Im still looking for some help on this one.

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