Perpetual Macro in Excel

[zsfhaergta]

Hi,

I'd like to have a macro perform real-time operations to an excel file while it is open. It would be similar to a forever loop running on a separate thread. It would perform operations upon data updates or at certain times. Is something like this possible?

Thanks,
Allen

 

[BobRodes]

Since this is a VB forum and not a VBA forum, I'll tell you how to do this in VB rather than VB for Excel.

Use Excel Automation to get a reference to your open excel file (use GetObject; there are plenty of threads on this forum that will tell you how to do this). Create a procedure (or multiple ones if appropriate) that accepts your reference as an argument, and performs the real-time operations you mention. Finally, use either a timer control on a hidden form, or the Timer function in an endless loop, to fire off your procedure on a periodic basis.

As for doing it on data updates, you'll want to look into events in the Excel object library. Find the one that fires every time you update data and use it.

HTH

Bob

 

[strongm]

In Excel you'd probably want to look at the Application.OnTime method for operations at certain times.

As for refreshing on a data change, assuming that your data is represented by a QueryTable then you have the BeforeRefresh and AfterRefresh events that you can respond to.

But, as BobRodes says, your question is really a VBA question, and would be best pursued in forum707