Jen, I'm thinking you should be able to call the functions of the add-in via
a macro that uses the SendKeys function. A little testing should tell
whether or not this will work. You could wrap the process up in some code to
work from sheet to sheet sending the key combination for each sheet. We
could start out simply ... testing on 2 sheets to see if there any problems
such as timing because of the length of time it takes to retrieve the data.
Code would look like this. And just to cover ourselves,
#1 - make a copy of the workbook to test with.
#2 - start this phase by adding 2 sheets to the workbook, name one "Test1"
and name the other "Test2"
#3 - put this macro into a regular code module: [Alt]+[F11] ... Insert |
Module and copy the code and paste it into the module, close the VB editor
and run the macro from Tools | Macro | Macros.

Sub Test2Retrieves()
Dim LC As Integer
Dim wsName As String

For LC = 1 To 2
wsName = "Test" & Trim(Str(LC))
Worksheets(wsName).Select
Range("A1").Select
Application.SendKeys "%sr", 1
Next
End Sub

I could have written the code in line, but I want to try it in a loop,
because if this works, then we will rebuild the loop to work through all the
worksheets (without worrying about the sheet names).

What this should do is choose each of those new sheets one at a time and
then it should activate the add-in menu items: %s is the same as pressing
[Alt]+[s] from the keyboard, followed by pressing the [r] key. the " ,1"
says wait for the keys to be processed before continuing with the rest of the
code.

I figured we'd start with 2 sheets to see if the looping portion is going to
work without eating up hours and hours of waiting, since you said it takes
about 30 minutes to process each of them.



"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