Running macro over different sheets for Essbase

[Jen]

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

 

[JLatham]

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]+ 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.

 

[tissot.emmanuel]

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/

 

[JLatham]

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.

 

[Jen]

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

 

[Jen]

Hi JLatham, Manu,

I must admit tat I was impressed by the simplicity of your solutions. I
thought that the code would be far more complex. Testing whether the data
has been dumped etc ...but hey:

I've been able to test it ...
Manu your code performs brilliantly!

Jlatham, your code works fine as well ... on my second sheet "Test2". The
Sub does not run on "Test1"?!

I combined your SendKEys method with Manu's run-through the sheets
Sub TestRetrieves()
Dim Wsh As Worksheet
For Each Wsh In Worksheets
Wsh.Select
Range("A1").Select
Application.SendKeys "%sr", 1
Next
End Sub

But the result remains the same. Could it be that SendKeys' method is less
robust?

I am soo happy though,
Gonna try to download my data ... hopefully it does not time-out!!
Jen

 

[Jen]

Hi,
There is a message box popping up each time for each new sheet ...
I hope I can suppress this with?

Sub MyMacro()
Application.DisplayAlerts = False
'Code
Application.DisplayAlerts = False
End Sub

 

[tissot.emmanuel]

Hi Jen,

Sub MyMacro()
Application.DisplayAlerts = False
'Code
Application.DisplayAlerts = True <<<<!!!
End Sub

What kind of message do you have? If it's from Excel,
Application.DisplayAlerts should work, but if it's from the add-in...

Regards,

Manu

 

[JLatham]

What you have encountered is part of what I wanted to test for and the reason
that I set things up to only work with 2 sheets. I think it is safe to say
that the SendKeys is less "robust" than the .Execute method that
tissot.emmanuel (t.e) provided.

The " ,1" parameter for the SendKeys is supposed to cause the macro to pause
until the keys sent have been processed. With such a long process to finish
after the keys have been received and dealt with, it's not waiting for the
actual data retrieval to finish before again sending same keys. The loop
completes and exits before even the first data group is retrieved. So
basically, t.e has provided you with the complete solution.

The
Application.DisplayAlerts False
....code
Application.DisplayAlerts True
usage should inhibit any Excel generated warnings. If those aren't just
warnings, but are dialogs requesting additional information to give to the
add-in, then we may have to resort to SendKeys to provide the needed input
for them.

 

[Ron Coderre]

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]

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]

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