I have two worksheets accessing two different AS cubes on the server.I
have enabled drillthrough on both the cubes. Excel add-in for analysis
services has been installed as well.

Right click --> OLAP Drillthrough works on one worksheet while the
other generates a visual basic error:

"Runtime Error : 1004:

Application-defined or object-defined error.".

any pointers would be helpful.

the Excel add-in for analysis services was an unnecessary part. I
should've left that out.

anyway I used the code that was posted in "Extending Excel OLAP
Functionality" :

http://msdn.microsoft.com/library/de...extendolap.asp


and pasted it in the Visual Basic Editor inside Excel. "OLAP
Drillthrough" is added as an additional menu item when you right click
on a cell.

for both the worksheets, however, a "Macro not found" comes up and when
I execute the macro individually, it works for one worksheet while it
doesn't for the other.

I don't understand this..it is the same piece of code pasted in both
the worksheets. Could there possibly be a bug in the code that msdn has
provided or am I doing something silly?

WS

weirdscientist78 (AT) gmail (DOT) com wrote:

ok I got it working.

here's the code I used:

http://www.webservertalk.com/archive...-3-981284.html

there apparently is an error when looping through the page items, this
guy has fixed that.he did not have the Workbook_Open() macro that would
add "OLAP Drillthrough" to the context menu.
I added that. in case, anyone doesn't have it, here's the code:

Private Sub Auto_Open()
Dim ptcon As CommandBar
Dim cmdDrill As CommandBarControl
Dim btn
Set ptcon = Application.CommandBars("PivotTable context menu")

For Each btn In ptcon.Controls

' Exit the procedure if the mnue item already exists.
If btn.Caption = "OLAP Drillthrough" Then GoTo noadd

Next btn

' Add an item to the PivotTable context menu.
Set cmdDrill = ptcon.Controls.Add( _
Type:=msoControlButton, temporary:=True)

' Set the properties of the menu item.
cmdDrill.Caption = "OLAP Drillthrough"
cmdDrill.OnAction = "DrillThrough"
noadd:
End Sub

moreover, the Workbook_open() did not seem to fire. so I used the older
Auto_Open instead.

now it works perfectly.

WS

weirdscientist78 (AT) gmail (DOT) com wrote: