Yes, you'd need the basic structure already set up for your reports, but
that's no different from your current situation.

I'm thinking you might be migrate your current worksheets into the file you
downloaded......or....put my code into your current workbook and park a
ControlPanel page in the workbook.

If you need help......just ask.

(Regarding PivotPlay......I'll email you and we'll can see if we can isolate
what the issue might be.)

***********
Regards,
Ron

XL2002, WinXP


"Jen" wrote:

Hi Ron,

Thank you so much. I have downloaded your file and succeeded to get it
connected!
Although I am very impressed with all the professional coding!!! I am still
a bit puzzled in the advantage of working this way..?
I would still need to set up all the parameters-fields for retrieving the
proper data, no? The same way I would need to "prepare" the different sheets
.... to download the data with the Essbase Add-in?

I will give your code a deeper look though!
Jen

PS: I found your PivotPlay.xla this week and I am really impressed with it!
(But knowing now that you are writing the Essbase-Add-ins ...that one must
have been an easy one ;) just kidding)
Thanks for sharing such a great add-in with us!

I have had an issue with it though this week -see on
excel.worksheetfunctions -Maximum filesize? on 2nd jun2007- I was retrieving
external data from an Excel file (40MB if I remember well) to load my pivot
table.. I was using your Add-in to change the Query info. That worked great!
Untill I added a couple more data sheets to my source data-file and the file
size was 60MB. Then your add-in didn't want to update or connect anymore. I
had to change the query in MSQuery to get it to work.



"Ron Coderre" wrote in message
...
Hi, Jen

The most efficient way to pull EB data into each sheet is to directly
access
the Essbae API. Using the API, you do not need to select any of the
sheets
in the workbook before pulling the data.

Figuring out how to work with the API can be a bit frustrating, so I
stripped down one of my production applications for you and uploaded it to
a
free file sharing website. Here's the link:

http://www.savefile.com/files/777342

That file does contain macros. The workbook, all sheets, and the VBA
project
are all unprotected. No macros run upon opening the workbook.

There is a button on the ControlPanel sheet that begins the EB data pull
process to all sheets EXCEPT the ControlPanel sheet. The code prompts for
an
EB username/pwd and uses those settings to connect to each sheet.

The ControlPanel sheet contains some basic instructions.

Is that something you can work with?
(Post back with any questions.)
***********
Regards,
Ron

XL2002, WinXP


"Jen" wrote:

Hi There,

I am retrieving data from Hyperion via an Essbase-add-in in a worksheet.

The "Retrieve" function in this add-in will evaluate whether it has all
the
parameters on the UsedRange and then dump the data.
Would it be possible to do this retrieve on every UsedRange on each
worksheet in my workbook?
It takes like 30 min a sheet which is extremely long to run during the
day.
It would be extremely useful if that macro could run on my 12 worksheets
during the night...

I have no idea on where to start with this one:
-I was wondering whether you can call this retrieve function from the
Add-in
(I cannot see which function it is as the addd-in is protected) just
based
on the caption? The Add-in's caption in the Menubar is "Essbase" (with
the
first "s" underlined), the retrieve function can be found at the first
position straight under "Essbase" and is called "Retrieve" (with the "R"
underlined) ((in the same way as "Microsoft Excel Help" resides under the
"Help" in the menubar))
-The retrieve-function evaluates the Usedrange (which is one every sheet:
A1:M_lastrow) and dumps the data in there
-When the macro has found and written it's data to the first sheet ...it
should go to the next one. Probably you'll need to tell the macro to go
to
the next sheet when it has dumped the data. I have no idea on how to
check
this.... The data gets dumped in the Range on every sheet on range
(H2:M_lastrow), so maybe the approach could be "IF counta(H2:M65000)0
then
loop to 2nd sheet and start all over again" ?

Maybe I am just dreaming away and is this just not possible without
having
proper access to the add-in, but for my 2nd day on VBA I still have my
hopes
high :)
Jen