Good afternoon all,



For the past few weeks I have been creating some real time dash boards for my company. The data is pulled in via a query to an oracle database. That data drives several pivot tables and pviot charts. I have set options that refresh the data from the oracle database every 5 minutes. I have been trying all day to find something that will refresh the pivot tables every 6 minutes.

is this possible?

I don't know from pivot tables, bremen22—I never use them—but if we're talking about a general capability to execute some action every so often, there are several ways to go about it.

1) If the workbook need be updated only if someone's actually looking at it (which seems pretty likely), I should think you could create a macro that runs automatically upon certain events. For instance, here's one (the Workbook.RowSetComplete event) that is "raised when the user either drills through the recordset or invokes the rowset action on an OLAP PivotTable". If the user might be typing stuff in there, you could use a Change event. If he's not typing anything but just moving the cursor around, there's a SheetSelectionChange event. Whatever you like, the point is that you can write a macro that runs whenever this event occurs; the macro would check the time to see whether it should get fresh data from Oracle.

I've never done this, I'm just hypothesizing. There may be better ways.

2) If the user opens the workbooks for read-only, you could write the refresh in VBScript (it's all the same objects so the code would be almost identical, it just runs from outside Excel) and then have that script be triggered by a Windows scheduler of some sort. But that works only if no one's got the workbook open for editing.

3) If you write a worksheet function and make it volatile, it'll run whenever any recalc at all is done in the workbook. (I think workbook; maybe only in the active worksheet.) Mostly Excel can tell whether a formula needs to be recalculated, based on whether any of its arguments refer to other cells; but certain functions such as Now() and Rand() change every time anything else is recalculated, and you can tell Excel to treat your home-grown function the same way. Then the function would be able to refresh the data. ...Or maybe not; functions operate under severe limitations as to what they can do outside the cell they're called from. You might have to experiment with that.

Any of those sound interesting? And to anyone else out there who writes VBA: I expect you're already champing at the bit to explain to bremen and me why all these ideas are terrible. Sound off without worrying over-much about being polite; I've never had to write a routine like this, so there are probably better ways than the first three ideas I got off the top of my head.

Perhaps using the Windows Timer in VBA as described here under " Using Windows Timer" could get you on track

__________________
Did you know you can thank someone who helped you? Click on the tiny scale in the right upper hand corner of your helper's post