Refresh Problem in Excel 2000 1

[Woodyuk]

I have a report, which once open runs a macro. I need this to produce the report to screen, and refesh it self every 10 minutes, with no human help.

Any ideas?

 

[JPMontreal]

Hi,

Go to Data > Import external data > Data Range Properties

And set the “Refresh every” to 10



Jean-Paul
Montreal
jp@solutionsvba.com
mtljp2@sympatico.ca

 

[Woodyuk]

Have already tried thaty, but there is no eternal data. It pulls in figures from another excel sheet/csv.

I tried doing the wait command in a macro, with a counter to repeat it, which didnt seem to work

 

[Bowers74]

This should Re-run the Macro every 10 min.:

Sub YourMacroName_Here()
Dim UpdateTime

' Your MacroCode Here

UpdateTime = Now + TimeValue("00:10:00")
Application.OnTime UpdateTime, "YourMacroName_Here_InQuotes"
End Sub

Place the following code into the Workbook_BeforeClose event:

Application.OnTime UpdateTime, "YourMacroName_Here_InQuotes", , False

I hope this helps!

It's FRIDAY!!!!!!



Peace!

Mike

Never say Never!!!
Nothing is impossible!!!

 

[Bowers74]

Oooops,

Don't forget to Dim UpdateTime in the Workbook_BeforeClose Event.

Sorry!





Peace!

Mike

Never say Never!!!
Nothing is impossible!!!

 

[Woodyuk]

I dont have a close event, just a open.
I need the report to stay up on a screen, so the report can be viewed.

Will this still work?!?!?!

 

[Bowers74]

This will work!

You just need to CREATE a Workbook_BoeforClose event. This is to ensure the OnTime function is cleared, so it doesn't try to Call your Macro after your workbook has been closed. Just a precaution, but it should really be done.



Peace!

Mike

Never say Never!!!
Nothing is impossible!!!

 

[Woodyuk]

IT worked, did a error in the macro.
I didnt use the before close event, and have left the report running.

How important is this to have in there????

 

[Bowers74]

I'm not sure,

Since the .OnTime function belongs to the Application collection, the Update might still be loaded into memory and will try to call the macro from the closed workbook, but then again, it might not.

Good Luck,

P.S. What did you mean with "did a error in the macro"?

Peace!

Mike

Never say Never!!!
Nothing is impossible!!!

 

[Woodyuk]

I pasted that bit, you mentioned to do in another macro, so I removed it and it worked

 

[xlbo]

WoodyUK - seems like Mike has helped you out there. The TT way to say thanks is to award a star. This also helps those users who search the archives as it allows them to home in on those threads which are helpful or contain useful info. To award a star, simply click on the "Mark this post as a helpful / expert post"

Rgds, Geoff
Quantum materiae materietur marmota monax si marmota monax materiam possit materiari?
Want the best answers to your questions ? faq222-2244

 

[Woodyuk]

Beat you too it, already did it.
Thanks for your help Mike

 

[xlbo]

ha ha ha ha ha ha ha ha ha - damn my slow internet connection

Rgds, Geoff
Quantum materiae materietur marmota monax si marmota monax materiam possit materiari?
Want the best answers to your questions ? faq222-2244

 

[Bowers74]

Thanks for the star WoodyUK, I'm glad I could help!

Geoff,

Confucius say: Man who is guru in ways of Excel, need not have fast internet connection. [smilejap]

Thanks for the support!

It's FRIDAY!!!





Peace!

Mike

Never say Never!!!
Nothing is impossible!!!