VBA to trigger drill through

[telula]

Hello,
Instead of right-click drill through process, is there a way to initiate this drill through process via VBA?

[paulsimon]

Hello,
Instead of right-click drill through process, is there a way to initiate this drill through process via VBA?

Telula,

The following does the trick:

Code: Select all

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
    If bCellIsInsideRange(Target, Range("DrillThruPoss")) Then
        If [b]Not (Application.CommandBars("cell").Controls("Drill").Enabled)[/b] Then
            MsgBox ("Sorry, you cannot drill down on this cell")
        Else
            Call StartOfLongUpdate
            [b]Application.CommandBars("cell").Controls("Drill").Execute[/b]
            If ActiveSheet.Range("A1").Value = "FirstSightDrillThru" Then
                Call DrillThruFormatSheet
            End If
            Call FinishedLongUpdate
        End If
    End If
End Sub

The key bits are the ones in bold.

The Call to DrillThruFormatSheet works on the ActiveSheet ie the first sheet in the new workbook that the DrillThru generates, and it does all the formatting to make the Drill Thru result look prettier before the user sees it.

Regards

Paul Simon

[David Usherwood]

Paul, that's seriously good stuff. I've been after formatted relational drill output for .... years.

[telula]

Thanks Paul, that is fantastic.
Is "FirstSightDrillThru" the name of the TI drill through process?

Telula

[lotsaram]

No.

I don't think there would be any way of finding that out (Paul correct me if I'm wrong). The drill through is being harnessed by calling the command from the right-click popup menu.

Application.CommandBars("Cell").Controls("Drill").Execute

This will just call whatever drill(s) have been assigned to the active cell.

[paulsimon]

Code: Select all

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
    If bCellIsInsideRange(Target, Range("DrillThruPoss")) Then
        If [b]Not (Application.CommandBars("cell").Controls("Drill").Enabled)[/b] Then
            MsgBox ("Sorry, you cannot drill down on this cell")
        Else
            Call StartOfLongUpdate
            [b]Application.CommandBars("cell").Controls("Drill").Execute[/b]
            If ActiveSheet.Range("A1").Value = "FirstSightDrillThru" Then
                Call DrillThruFormatSheet
            End If
            Call FinishedLongUpdate
        End If
    End If

This is the background to this bit of code.

When someone double clicks on a cell on the sheet, it calls this event. What gets passed in as Target will be the cell that they clicked on.

It calls a function I wrote to see if the cell that they clicked on is inside the Range called DrillThruPoss. This is just a range I predefined on the sheet of all cells where Drill Thru is possible. This is just a quick way of finding out whether they are double clicking in the write area.

It then sees whether the Drill menu option is enabled on the context sensitive menu for that cell. This is the menu that would pop up if you right clicked on the cell. It will always have a mix of Excel and TM1 options. If the Drill option is enabled then you know that there is a Drill Thru Rule applying to that cell.

If there isn't then it puts up a message box

If there is then it runs Controls("Drill").Execute. To avoid any confusion, this doesn't run a Drill Thru process, instead it pops up a menu to allow them to choose from the available Drill Thrus. We generally have two processes. However, from what I can remember, if there is only one Drill Thru process then I think it will run that Drill Thru process straight away, without prompting.

After running the Drill Thru, it will have opened another workbook and the active sheet will give the results of the Drill Thru.

What I do in the SQL is to make the first column a hard coded literal, ie

SELECT "FirstSightDrillThru" as FirstSightDrillThru, SEGNUMB1 as Entity, ...

Then by checking to see whether the value in Cell A1 is FirstSightDrillThru I can guard against the case where something went wrong with the DrillThru and avoid trying to reformat the calling report, instead of the Drill Thru Sheet.

As David Usherwood points out, the main point of doing all this is that it allows you to format the result of the Drill Thru, to bold headings, format numbers, set Freeze Panes, etc. Unfortunately the annoying thing about the normal Right Click Drill, is that it doesn't trigger any sort of event that would allow you to detect that Drill Thru has taken place. There is no Workbook added or any other sort of Event. This method allows you to do that, and double clicking is arguably more natural for most users than right clicking. They also don't have to contend with finding Drill on a menu that has 12 or so other options.

Regards


Paul Simon

[Steve Rowe]

Just a quick update on this as I've just implemented Paul's code.
First thanks to Paul for the tip.

Second I had to use the visible property rather than the enabled property to test if I should run a drill.
This is on Excel 2003 Sp2 and TM1 9.0.3 U9
Not sure what the cause for the difference is but on my system if no drill is available the option is not on the menu (visible property) rather than greyed out (enabled property).
If I use enabled I always get true even though the option is not visible on the list.

Just to complicate further I'm working on a French system so the menu option is called something else too!

Code: Select all

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
        If Not (Application.CommandBars("cell").Controls("Détailler").Visible) Then
            Exit Sub
        Else
            Application.CommandBars("cell").Controls("Détailler").Execute
            Call DrillThruFormatSheet
        End If
End Sub

Anyway though I'd update the thread to avoid someone else hitting the same issue.
Cheers

[Steve Rowe]

Ok another two things.

Paul can you remember which version your script was for?

I'm finding this interacts very badly with the subnm function. Once I double click on a subnm the double click for drill no longer works since the double click event seems to be totally disabled. I seem to remember that there are TM1 versions of many of the Excel VBA events but I can't find them documented anywhere...Can anyone help me out by providing a list/reference?

Cheers

[paulsimon]

Steve

We are running a 9.1.3 client and a 9.1.4 server. We don't get the problem that you mentioned. We have SUBNM's on the sheet and they still work fine after using the Drill option, and vice-versa. Are you sure that you have a check in the double click event handler so that it lets things behave as normal if the target of the double click is not in the range of drill thru cells (which would normally exclude any SUBNMs at the top of the sheet)?

Sorry, I can't find anything on intercepting TM1 events at the moment.

Regards


Paul