Hi JLatham, Manu,

Thanks for your help already!
I will try your solutions asap. It seems I cannot connect to the server for
a little while. It will/must be resolved soon though...

I'll be back soon with some test results.
Jen


"JLatham" <HelpFrom @ Jlathamsite.com.(removethis) wrote in message
...
Very good - gives Jen another option to try and shows how to wrap it up in
a
loop that will work through all sheets in the workbook.

"tissot.emmanuel" wrote:

Hi Jen,

The following procedure make a click on the first item of the menu called
"Essbase" on the Worksheet Menu Bar for each worksheet in the workbook.

Sub RunEssbase()
Dim Wsh As Worksheet
For Each Wsh In Worksheets
Wsh.Activate
Application.CommandBars("Worksheet Menu
Bar").Controls("Essbase").Controls(1).Execute
Next
End Sub

Best regards from France,

Manu/

"Jen" a écrit dans le message de news:
...
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