98.8% Of all customers recommend us, we're so confident about our results we publish all reviews and stats
View Live Stats View Reviews
Forum home » Delegate support and help forum » Microsoft VBA Training and help » Accessing Hyperion add-in
Accessing Hyperion add-in
Resolved · Urgent Priority · Version 2003
Thu 2 Jul 2009, 15:00Delegate Trevor said...
Trevor has attended:
Excel Advanced course
Excel VBA Intro Intermediate course
Access Intermediate course
Excel VBA Advanced course
Accessing Hyperion add-in
Hi
I am using a Hyperion add in within excel that has an extra item within the command bar called RHXL. Having hit RHXL one of the options is Refresh. I simply want to write a bit of code to Refresh. I have been given the code below which apparantly worked in a earlier life
Application.CommandBars("Worksheet Menu Bar").Controls(" RHXL").Controls(" Refresh").Execute
The sub routine is falling over saying it is an invalid procedure or agument
Please could you tell me what I am doing wrong - as cannot see Worksheet menu Bar anywhere in help
Thanks
Fri 3 Jul 2009, 09:43Trainer Stephen said...
RE: Accessing Hyperion add-in
Hi Trevor
Thanks for your question
This one is hard to fix without seeing your application with the add in. A few things to try however.
1. The first thing is that the code you posted appears to have a rogue blank space in controls(" RHXL), which would probably cause the thing to fall over.
2. The structure of the code seems odd in that I would have expected something like
Application.Commandbars("RHXL").Controls("Refresh").ExcecuteThis is because the RHXL toolbar is the command bar and the refresh button is the control you wish to use
If this doesn't work please get back to me and we can try something else
Regards
Stephen
Fri 3 Jul 2009, 13:26Delegate Trevor said...
RE: Accessing Hyperion add-in
Stephen
Your observation of the rogue spaces did the trick - thanks. The following code now works in one worksheet. However if I copy the code to another worksheet and run the code I get a 400 error message and it doesn't like it at all - any ideas ?
Sub refresh()
'this macro refreshes all worksheets
Dim WS As Worksheet
For Each WS In Worksheets
Application.CommandBars("Worksheet Menu Bar").Controls("RHXL").Controls("Refresh").Execute
Next WS
Sheets("master").Select
MsgBox ("All worksheets have been refreshed")
End Sub
Mon 20 Jul 2009, 09:34Trainer Stephen said...
RE: Accessing Hyperion add-in
Hi Trevor
Thanks for you follow up
I apologise for the delay in replying. I have been away from work for the last few weeks due to illness. I realise that you may have already solved the problem, if this is not the case could you please advise me so that I can giver it my urgent attention
Regards
Stephen
Mon 20 Jul 2009, 10:17Delegate Trevor said...
RE: Accessing Hyperion add-in
Solved - problem was in naming the macro 'refesh' a word reserved for Hyperion - thanks for your efforts anyway . Trevor
Mon 20 Jul 2009, 10:41Trainer Stephen said...
RE: Accessing Hyperion add-in
Thanks Trevor
Regards
Stephen
Training information:
|
Welcome. Please choose your application (eg. Excel) and then post your question. Our Microsoft Qualified trainers will then respond within 24 hours (working days). Frequently Asked Questions
Any suggestions, questions or comments? Please post in the Improve the forum thread. |
VBA tip:Use VbNullString instead ofWhen needing to default a String variable back to it's default of "" use vbNullString as in the following example: |
