Dynamically load data into a pivot table
 

I have a pivot table that I need to populate from an external source
(the source is an external propriatary system - not my idea).

There's a pivot field (let's call it "index") that has about 1,000
values. Getting the data for 1 value takes about a second which isn't
a problem. Getting the data for all 1,000 takes 1,000 seconds (big
problem).

I know what all 1,000 values are in advance.

Is there a way to create a pivot table, have "index" as a pivot field,
and when the user chooses a new one my VBA receives an event message
and retrieves the data?

There's no way I can get this info into a flat file or database.

I appreciate this is a bit off beat but any help would be greatly
appreciated.

Tom.