Using the Microsoft Data Analyzer ActiveX Control in Microsoft Excel 2002

  • Article

Click here to download sample - odc_DACtrlExcel.exe. (100.88 KB) This content is no longer actively maintained. It is provided as is, for anyone who may still be using these technologies, with no warranties or claims of accuracy with regard to the most recent product version or service release.

 

Frank C. Rice, Microsoft Corporation
John R. Durant, Architechs IT

April 2002

Applies to:
    Microsoft® Data Analyzer
    Microsoft Excel 2002

Summary: Learn how to embed and use the Microsoft Data Analyzer ActiveX control in Microsoft Excel 2002 to spot trends, opportunities, and potential problems with online analytical processing (OLAP) data. (29 printed pages)

Download Odc_dactrlexcel.exe.

Contents

Introduction
Why Embed the Microsoft Data Analyzer ActiveX Control in Excel?
Embedding the Microsoft Data Analyzer ActiveX Control in Excel
Adding Controls to the frmControl Form
Adding Code to the frmControl Form
Creating and Adding Code to the frmIntro Form
Creating the frmDetail Form
Adding Aspects and MetaData to the Tree
Adding Aspect Members
Adding Cube Information
Adding Measures Information
Adding Dimension Information
Adding Trait Information
Creating the frmDialogs Form
Running Code When the Workbook Opens
Putting It All Together
Conclusion

Introduction

Microsoft® Data Analyzer belongs to the Microsoft Office family of software applications. It provides a graphical analysis interface for users to apply business intelligence to their operational data. Data Analyzer is designed to work with online analytical processing (OLAP) data, based on Microsoft SQL Server™ 2000 with Analysis Services. By using Data Analyzer, users can spot trends, opportunities, and potential problems regardless of their level of technical expertise.

In addition to a standalone user interface, Data Analyzer also provides a Microsoft ActiveX® control that can be embedded in Microsoft Office application documents such as Microsoft Excel worksheets, Microsoft PowerPoint® slides, Microsoft Word documents, and manipulated with Microsoft Visual Basic® for Applications (VBA) code. The Data Analyzer ActiveX control can also be embedded in a UserForm in Component Object Model (COM)-based applications like Excel or Word. Additionally, the control can be embedded in Web pages in Microsoft FrontPage® and manipulated with Microsoft Visual Basic Scripting Edition (VBScript) code. Developers can use the Data Analyzer ActiveX control to access the Data Analyzer application-programming interface (API) in order to run most of the user-interface operations available in the standalone Data Analyzer. The Data Analyzer API contains a rich object model that can be used to programmatically control many features of Data Analyzer including the ability to:

  • Create, load, configure, and save views
  • Change the properties such as colors, visualization methods, dialogs, and functions
  • Run menu items

For additional information on the Microsoft Data Analyzer Object Model, see Exploring Microsoft Data Analyzer Programmability.

In this article, we will demonstrate creating the forms and sample code to do the following:

  • Embed the Data Analyzer ActiveX control into a UserForm in Excel 2002 and connect to a Data Analyzer view file
  • Open a dialog box where the user can select from a list of Data Analyzer operations normally available from the user interface
  • Open up another dialog box that displays a hierarchical view of the different components that make up a Data Analyzer view
  • Add code to a workbook so that the sample opens automatically when the workbook is opened

Why Embed the Microsoft Data Analyzer ActiveX Control in Excel?

Why would you want to embed the Microsoft Data Analyzer ActiveX control inside of an Excel 2002 worksheet? On its own, Excel provides a number of features to help you work with OLAP data and analyze your multidimensional data. For example, the PivotTable® report is a special type of table that you can use to summarize information from a data source. The data source can be a relational file, an OLAP cube, or an Excel list. After specifying information during the creation of the PivotTable such as the fields that you are interested in, the layout of the fields, and the types of calculations you want, you can rearrange the data to provide any number of alternative perspectives.

Additionally, you can connect to OLAP data sources in Excel just as you do to other external data sources. For example, you can work with databases created with Microsoft SQL Server 2000 Analysis Services, the Microsoft OLAP server product. Excel can also work with third-party OLAP products that are compatible with OLE DB for OLAP.

Excel also provides a large number of functions that you can use to perform complex calculations on your data. By combining the calculation features of Excel with the data analysis capabilities of Data Analyzer, you can create very powerful applications.

For example, you can embed the Data Analyzer ActiveX control inside of an Excel UserForm to enable your users to quickly view their data and then provide custom VBA code that users can execute to retrieve selected data from the Data Analyzer view and insert that data into cells on a worksheet for further analysis and calculations. Or you could create a macro that retrieves data from a Data Analyzer view, defines special print settings, and prints a document containing the data with custom formatting and border styles.

Combining the capabilities of Excel and the Data Analyzer allows you to provide an easy-to-use data analysis tool to your users with the powerful calculation engine of Excel.

Embedding the Microsoft Data Analyzer ActiveX Control in Excel

First, we will embed the Microsoft Data Analyzer ActiveX control into a UserForm in Excel 2002 and manipulate it with VBA code. To do so, on a computer with Data Analyzer installed:

  1. Start Excel 2002 and create a new, blank workbook.
  2. On the Tools menu, point to Macro, and then click Visual Basic Editor to switch to the Visual Basic Editor (VBE).
  3. On the Insert menu, click UserForm.
  4. In the Properties window for the UserForm, replace the text in the Name property with frmControl and the text in the Caption property with Microsoft Data Analyzer ActiveX control—Sample.
  5. In the Toolbox (View menu), right-click, and click Additional Controls.
  6. In the Additional Controls dialog box, check the Max3Ax Class box, and then click OK.
  7. Finally, drag the Max3ax Class control onto the frmControl form.

Note   At design time, the Data Analyzer ActiveX control's user interface will not be visible (see Figure 1). At run time, the Data Analyzer ActiveX control's user interface is visible and runs within the confines of the UserForm.

To program against the Data Analyzer ActiveX control, you must set a reference (on the Tools menu, click References) to the Max3API DLL (in a default installation of Data Analyzer, this dynamic-link library (DLL) can be found at C:\Program Files\Microsoft Data Analyzer\Data Analyzer 3.5\Max3API.dll). Additionally, in this example we will be using interfaces from the MaxODBO 1.0 Type Library to retrieve OLAP metadata so you will need to set a reference to the MaxODBO 1.0 Type Library DLL (in a default installation of Data Analyzer, this DLL can be found at C:\Program Files\Microsoft Data Analyzer\Data Analyzer 3.5\MaxODBO.dll). And finally, in order to use the TreeView ActiveX control in this example, you will need to set a reference to the Microsoft TreeView Control, version 5.0 (SP2) OCX (usually found at C:\WINNT\System32\Comctl32.ocx).

To get started, you will first need to declare some variables so that they are available to all of the forms and procedures. To do this, we will create a standard module and declare the variables with the Public keyword. On the Insert menu, click Module and type the following:

Public mobjMSDA As Max3API.Application
Public mobjView As Max3API.View
Public mintDialog As Integer
Public strFileName As String
Public intUniqueID As Integer

ThemobjMSDAvariable represents the Max3API Application object that gives us programmatic control over the Data Analyzer user interface. ThemobjViewvariable represents the Max3API View object, which gives us access to the methods and properties of a Data Analyzer view. ThemintDialogvariable will contain an Integer value that will be used to display a Data Analyzer dialog box. ThestrFileNameString variable will contain the filename of the view that appears in Data Analyzer. And finally, theintUniqueIDInteger variable will be used as a counter to append a unique identifier to a node name.

Adding Controls to the frmControl Form

We will now add three command buttons to thefrmControlform. The form should resemble Figure 1 when all of the controls have been added.

Aa155445.odc_dactrlexcel01(en-us,office.10).gif

Figure 1. The frmControl form in Design View

The Show Dialog button will display thefrmDialogsform from which the user can select from a list of actions. The Show Detail button will open a form that displays a tree view of the different objects (aspects, traits, and so forth) making up a Data Analyzer view. We will also add a Close button to the form.

  1. In the Toolbox, drag a CommandButton control onto the UserForm in the lower right hand corner.

  2. In the Properties window for the control, replace the text in the Name property with cmdClose and the text in the Caption property with Close.

  3. Double-click the control to open up the cmdClose_Click event procedure. Between the Sub and the End Sub statements, type the following:

    End

    Executing this command will close thefrmControlform.

  4. In the Toolbox, drag a CommandButton control onto the form to the left of the Close button.

  5. In the Properties window for the control, replace the text in the Name property with cmdDetail and the text in the Caption property with Show Detail.

  6. Double-click the control to open up thecmdDetail_Clickevent procedure. Between the Sub statement and the End Sub statement, type the following:

    frmDetail.Show

    Executing this command will display thefrmDetailform.

  7. In the Toolbox, drag a CommandButton control onto the form to the left of the Show Detail button.

  8. In the Properties window for the control, replace the text in the Name property with cmdDialog and the text in the Caption property with Show Dialog.

  9. Double-click the control to bring up thecmdDialog_Clickevent procedure. In the procedure, type the following:

    ' Displays a list of action dialogs.
'
frmDialogs.Show vbModal
mobjMSDA.ShowDialog mintDialog

    Clicking this button will display thefrmDialogsform and execute the ShowDialog method of the Data Analyzer Application object. This will be explained in more detail later in this article. Figure 2 shows how thefrmControlform will appear when opened to a view.

    Aa155445.odc_dactrlexcel02(en-us,office.10).gif

    Figure 2. The frmControl Form at Run Time

Adding Code to the frmControl Form

Now, we will add some additional code to thefrmControlform. First, we will add code that will allow our form to be resizable. This will allow users to change the size of the form to fit their screens. This code will also resize the Microsoft Data Analyzer ActiveX control and reposition the form's command buttons as the form is resized. To do this, we will need to make calls to the Microsoft Windows® API. Before we can use the procedures in the Windows API, we need to tell VBA where the DLLs containing those procedures can be found, what parameters they accept, and what information they return. We do this by using a Declare statement. In the Declarations section of thefrmControlform's code window, type the following:

' Windows API calls used to make the frmControl form resizable.

'Find the form's window handle
Private Declare Function FindWindow Lib "user32" _
    Alias "FindWindowA" ( _
    ByVal lpClassName As String, _
    ByVal lpWindowName As String) As Long

'Get the form's window style
Private Declare Function GetWindowLong Lib "user32" _
    Alias "GetWindowLongA" ( _
    ByVal hWnd As Long, _
    ByVal nIndex As Long) As Long

'Set the form's window style
Private Declare Function SetWindowLong Lib "user32" _
    Alias "SetWindowLongA" ( _
    ByVal hWnd As Long, _
    ByVal nIndex As Long, _
    ByVal dwNewLong As Long) As Long

'The offset of a window's style
Private Const GWL_STYLE As Long = (-16)

'Style to add a sizable frame
Private Const WS_THICKFRAME As Long = &H40000

A detailed discussion of these declarations is beyond the scope of this article. However, more information on Windows APIs can be found in the article Overview of the Windows API.

Next, in thefrmControlform's code window, select the Activate event from the right drop-down list box at the top of the code window. The Activate event occurs when the form becomes the active window. Type the following code into the procedure:

' Purpose: Open up a view file selected by the user
' in the frmIntro form.
'    

' Contains the form's Windows handle value.
Dim mhWndForm As Long
Dim iStyle As Long

On Error GoTo UserForm_Activate_Err

' Get the handle for the form (for Excel 2000 or later).
mhWndForm = FindWindow("ThunderDFrame", frmControl.Caption)

'Make the form resizable
iStyle = GetWindowLong(mhWndForm, GWL_STYLE)
iStyle = iStyle Or WS_THICKFRAME
SetWindowLong mhWndForm, GWL_STYLE, iStyle

' Set a reference to the Data Analyzer ActiveX control.
Set mobjMSDA = Max3Ax1.Application()
    
' Open up the view.
mobjMSDA.ActiveView.OpenView strFileName, vlocFileSystem

UserForm_Activate_End:
    Exit Sub

UserForm_Activate_Err:
    If Err.Number = -2147221394 Then
        MsgBox "Cannot open the View. Either the View file does not " & _
            "exist or it is not a valid View.", vbOKOnly
        GoTo UserForm_Activate_End
    Else
        MsgBox "Error " & Err.Number & ": " & _
            Err.Description, , "UserForm_Activate"
    End If

Examining this procedure, themhWndFormvariable is declared as a Long value and contains a handle (a unique number used by Windows to identify a window) to the active window (thefrmControlform). TheiStylevariable contains the Style property setting that Windows uses for this window. Then, we call the FindWindow method in the Windows API to get the handle for form. ThunderDFrame is the class name Windows uses to identify Excel 2002 and the Caption property contains the name of the form.

Next, we call the GetWindowsLong method in the Windows API to get the current Style property setting for the form. We perform an Or operation using that value with the WS_THICKFRAME constant and then use the result in a call to the SetWindowLong method in the Windows API to reset the Style property. This identifies the form as resizable.

We then create a reference to the Max3Ax Application object and open a view based on thestrFileNameString variable.

mobjMSDA.ActiveView.OpenView strFileName, vlocFileSystem

ThestrFileNameString variable gets its value from thefrmIntroform that we will discuss shortly.

We also include error-handling code in the event in case the view is not a valid Data Analyzer view.

Next, we will insert and examine the procedure that executes when the user resizes the form. In thefrmControlform's code window, select the Resize event from the right drop-down list at the top of the code window and insert the following:

' Purpose: Maintains the size of the Data Analyzer control
' and the position of the three command buttons relative
' to the size of the frmControl form.
'
Dim ctlControl As Msforms.Control

' Const for the size and positions of the controls
' on the frmControl form.
Const intDAControlHeightOffset As Integer = 60
Const intDAControlWidthOffset As Integer = 20
Const intCmdButtonHeightOffset As Integer = 48
Const intCloseBtnWidthOffset As Integer = 78
Const intDetailBtnWidthOffset As Integer = 156
Const intDialogBtnWidthOffset As Integer = 234

' Loop through each control on the form and set size or
' position.
For Each ctlControl In frmControl.Controls
    If ctlControl.Name = "Max3Ax1" Then
        ctlControl.Height = frmControl.Height - intDAControlHeightOffset
        ctlControl.Width = frmControl.Width - intDAControlWidthOffset
    Else
        ctlControl.Top = frmControl.Height - intCmdButtonHeightOffset
        Select Case ctlControl.Name
            Case "cmdClose"
                ctlControl.Left = frmControl.Width –
                  intCloseBtnWidthOffset
            Case "cmdShowDetail"
                ctlControl.Left = frmControl.Width –
                  intDetailBtnWidthOffset
            Case "cmdShowDialog"
                ctlControl.Left = frmControl.Width –
                  intDialogBtnWidthOffset
        End Select
    End If
Next

First, we declared a variable that can be used to represent each control on the form.

Dim ctlControl As Msforms.Control

Then, we declared a series of constants to define where the controls should be located on the form relative to the form's borders.

Const intDAControlHeightOffset As Integer = 60
Const intDAControlWidthOffset As Integer = 20
Const intCmdButtonHeightOffset As Integer = 48
Const intCloseBtnWidthOffset As Integer = 78
Const intDetailBtnWidthOffset As Integer = 156
Const intDialogBtnWidthOffset As Integer = 234

Next, we loop through the form's Controls collection. For each control on the form, we set the Width and Height properties to position the control to fit the form, based on an offset value.

For Each ctlControl In frmControl.Controls
    If ctlControl.Name = "Max3Ax1" Then
        ctlControl.Height = frmControl.Height - intDAControlHeightOffset
        ctlControl.Width = frmControl.Width - intDAControlWidthOffset
    Else
        ctlControl.Top = frmControl.Height - intCmdButtonHeightOffset
        Select Case ctlControl.Name
            Case "cmdClose"
                ctlControl.Left = frmControl.Width –
                  intCloseBtnWidthOffset
            Case "cmdShowDetail"
                ctlControl.Left = frmControl.Width –
                  intDetailBtnWidthOffset
            Case "cmdShowDialog"
                ctlControl.Left = frmControl.Width –
                  intDialogBtnWidthOffset
        End Select
    End If
Next

Now the controls will maintain size and position whenever the user changes the size of the form.

Creating and Adding Code to the frmIntro Form

ThefrmIntroform is the initial form displayed when Excel opens. The form contains text that instructs the user to open a Data Analyzer view file. When the user clicks OK, a FileOpen dialog box appears and the user can then navigate to and open a view file. Data Analyzer views can be contained in .max files or in .xml files. Figure 3 shows thefrmIntroform when it is completed.

Aa155445.odc_dactrlexcel03(en-us,office.10).gif

Figure 3. The frmIntro form when it is completed

Let's create the form and add code to it:

  1. From the Insert menu, click UserForm.

  2. In the Properties window for the UserForm, replace the text in the Name property with frmIntro and the text in the Caption property with Open a Data Analyzer View.

  3. In the Toolbox, click the Label control and drag the control to the form.

  4. In the Properties window for the control, replace the text in the Caption property with the following:

    **Please select a Data Analyzer view (.max) file or a valid XML view (.xml) file.

  5. In the Toolbox, click the TextBox control and drag the control to the form under the label, centered in the form.

  6. In the Properties window for the control, replace the text in the Name property with cmdOK and the text in the Caption property with OK.

  7. Double-click the control to bring up thecmdOK_Clickevent procedure. Between the Sub statement and the End Sub statement, type the following:

    ' Purpose: This procedure opens a file open dialog and
'          allows the user to choose a view or cube file.
'
Dim FDialog As FileDialog
Dim FFilters As FileDialogFilters

On Error GoTo cmdOK_Click_Err

' Set up the File | Open dialog.
Set FDialog = Application.FileDialog(msoFileDialogOpen)

' Set up the file filters.
With FDialog
    Set FFilters = .Filters
    With FFilters
        .Clear
        .Add "Data Analyzer Views", "*.max"
        .Add "XML Views", "*.xml"
    End With

    ' Allow user to select just one file.
    .AllowMultiSelect = False

    ' Exit if the user selects CANCEL.
    If .Show = False Then
        GoTo cmdOK_Click_End
    End If

    ' Assigned the file selection.
    strFileName = .SelectedItems(1)
End With

Unload frmIntro              
frmControl.Show

cmdOK_Click_End:
    Unload frmIntro
    Exit Sub

cmdOK_Click_Err:
    MsgBox "Error " & Err.Number & ": " & _
        Err.Description, , "cmdOK_Click"

This procedure uses the FileDialog object to display the FileOpen dialog box. First, we use the FileDialog property of the Application object to return a reference to the FileDialog object and then specify the type of dialog we want by using the msoFileDialogOpen constant. Next we use the Filters property of the FileDialog object to return a reference to the FileDialogFilters collection. The FileDialogFilters collection contains a number of preset filters. The Clear method of the FileDialogFilters collection removes any pre-existing filters. We then add our own filters to show only .max and .xml files. The Show method of the FileDialog object displays the FileOpen dialog box. If the user clicks the Open button, the Show method returns a value of True. If the user clicks the Cancel button, the Show method returns False and we exit the procedure.

Creating the frmDetail Form

ThefrmDetailsform is used to display a hierarchical view of the current Data Analyzer view. It does this using the TreeView ActiveX control, which is available in the Microsoft TreeView Control, version 5.0 (SP2) OCX (Comctl32.ocx). The VBA code behind the form populates the TreeView control by using the collections, objects, and methods of the Data Analyzer API object model and the interfaces in the MaxODBO 1.0 Type Library. The Data Analyzer API object model provides access to the components that make up a view. This includes aspects (similar to dimensions in OLAP terminology), aspect members, qualities (similar to measures in OLAP terminology), and traits (how qualities are displayed in a view such as by length or by color). The interfaces in the MaxODBO 1.0 Type Library provide access to the objects, methods, and properties that can be used manipulate and gather information (metadata) on the OLAP objects (catalogs, cubes, dimensions, measures, hierarchies) from which the view is derived.

Figure 4 shows thefrmDetailform when it is completed.

Aa155445.odc_dactrlexcel04(en-us,office.10).gif

Figure 4. The frmDetail form when it is completed

Let's create the form and add code to it:

  1. On the Insert menu, click UserForm.

  2. In the Properties window for the UserForm, replace the text in the Name property with frmDetail and the text in the Caption property with Structure of a Data Analyzer View.

  3. In the Toolbox, right-click, and click Additional Controls.

  4. In the Additional Controls dialog box, check the Microsoft TreeView Control, version 5.0 (SP2) box, and then click OK.

  5. Drag the TreeView control onto thefrmDetailform.

  6. Next, add a command button to the form. In the Toolbox, drag a CommandButton control onto the lower right hand corner of thefrmDetailform.

  7. In the Properties window for the control, replace the text in the Name property with cmdClose and the text in the Caption property with Close.

  8. Double-click the control to open up thecmdClose_Clickevent procedure. Between the Sub and the End Sub statements, enter the following:

    Unload frmDetail

  9. Next, select the Activate event from the right drop-down list at the top of the code window. Type the following code into thefrmDetail_Activateevent procedure:

    ' The LoadView procedure contains routines that populate
' the TreeView control.

' Initialize the unique identifier that will be
' appended to the node name when needed.
intUniqueID = 0
LoadView

' Show first node of the TreeView control expanded.
TreeView1.Nodes.Item(1).Expanded = True

    This code calls theLoadViewprocedure and expands the TreeView control to display the first node, once it is populated.

  10. Type the following procedure into the form's code window:

    Private Sub LoadView()
    ' Purpose: Calls routines that populate the TreeView
    '          control with information on Aspects
    '          (dimensions) and Traits (measures).
    '
    Set mobjView = mobjMSDA.ActiveView

    ' Add initial "View" level to the tree.
    TreeView1.Nodes.Add , , "Root", "View"

    LoadAspects
    LoadTraits
End Sub

TheLoadViewprocedure creates a reference to the active Data Analyzer view by using the ActiveView property of the Data Analyzer Application object. The code then appends the initial node (named View) to the tree by using the TreeView control's Add method. Next, theLoadAspectsandLoadTraitssubroutines are called to populate the tree.

Adding Aspects and MetaData to the Tree

An aspect is a dimension in an OLAP cube. Examples of aspects include time, customers, products, and regions. As we will see in the next procedure, the Aspects collection of the Data Analyzer object model provides the Count property and Item method. These members are used to determine the number of Aspect objects in the collection and access individual Aspect objects.

Type the following procedure into thefrmDetailform code window:

Private Sub LoadAspects()
    ' Purpose: Appends the Aspects label to tree and then
    '          calls routines to append individual aspect
    '          members. Also calls routines that append
    '          the cube metadata.
    '
    Dim objAspects As Aspects
    Dim objAspect As Aspect
    Dim objHierarchy As IMdhHierarchy
    Dim i As Integer

    Set objAspects = mobjView.Aspects()

    ' Add an Aspects label node to the tree.
    TreeView1.Nodes.Add "Root", tvwChild, _
        "Aspects", "Aspects"

    ' Add information about each Aspect object to the tree.
    ' Then call the routines that add the aspect members to
    ' the tree.
    For i = 0 To objAspects.Count - 1
        Set objAspect = objAspects.Item(i)
        TreeView1.Nodes.Add "Aspects", tvwChild, _
            objAspect.ID, objAspect.ID
        ' Get the aspect members.
        GetAspectMembers objAspect.Members, _
            objAspect.ID
    Next i

    ' Add an MetaData label node to the tree and then calls
    ' the routines to get the cube metadata.
    TreeView1.Nodes.Add "Root", tvwChild, _
        "MetaData", "MetaData"
    Set objAspect = objAspects.Item(0)
    Set objHierarchy = objAspect.MDHHierarchy
    GetCube objHierarchy.Dimension.Cube, "MetaData"
End Sub

This procedure appends an Aspects label to the tree and then iterates through the Aspects collection to add each Aspect object (dimension) to the tree. Then, theGetAspectMemberssubroutine is called to retrieve the members of the Aspect object. And finally, aMetaDatalabel is appended to the tree and theGetCubesubroutine is called to retrieve information from the cube. If you are unsure about how the nodes relate to each other in the tree, refer to Figure 4.

Adding Aspect Members

Next we will examine theGetAspectMemberssubroutine. This procedure appends anAspectMemberslabel to the tree view and then iterates through the AspectMembers collection, and adds each Aspect object to the tree. Type the following code into the code window of thefrmDetailform:

Private Sub GetAspectMembers(ByVal asptMembers _
    As AspectMembers, ByVal Node As String)
    ' Purpose: Appends the aspect members to the tree.
    '
    ' Accepts: asptMembers - Collection of aspect members.
    '          Node -        Branch ID of node where this
    '                        information will be appended.
    '
    Dim keytext As String
    Dim i As Integer
    Dim objMember As AspectMember
    Dim immediateparent As String
    Const NODE_NAME_NOT_UNIQUE = 35602

    On Error GoTo GetAspectMembers_Err

    ' Add an AspectsMembers label node and then appends
    ' each aspect member to the tree.
    keytext = "AspectMembers"
    TreeView1.Nodes.Add Node, tvwChild, _
        keytext, "AspectMembers"
    immediateparent = keytext
    For i = 0 To asptMembers.Count - 1
        Set objMember = asptMembers.Item(i)
        TreeView1.Nodes.Add immediateparent, _
            tvwChild, keytext, "Aspect:" _
                & objMember.ID
    Next i

GetAspectMembers_End:
    Exit Sub

GetAspectMembers_Err:
    If Err.Number = NODE_NAME_NOT_UNIQUE Then
        keytext = keytext & intUniqueID + 1
        Err.Clear
        Resume
    End If
End Sub

This procedure accepts the following arguments: asptMembers, which represents the AspectMembers collection, and theNodeString variable that contains the ID of the parent node.

Notice thekeytextString variable in the procedure. This variable is assigned the ID value of the node we are working with. The node ID must be unique so that the subroutine knows which node to append the information to. However, because the value for the node ID of the parent node may be the same as the value of the node ID for the child node, an error may occur when we try to append the node information to the tree. To overcome this, we've added some code to the error handling routine. TheintUniqueID + 1expression adds a value of 1 to the public counter variable intUniqueID. The value of the counter is then appended to the node ID (the value of thekeytextvariable) in order to make the node ID unique. The error is then cleared and the procedure resumes execution from where the error was generated. This procedure is repeated in other procedures in the form.

Adding Cube Information

The next procedure is called from theLoadAspectssubroutine and adds cube information to the tree.

Type the following procedure into thefrmDetailform code window:

Private Sub GetCube(ByVal mtdCube As IMdhCube, ByVal Node As String)
    ' Purpose: Appends the Cube label to the tree and then calls
    '          routines that append dimension and level information.
    '
    ' Accepts: mtdCube - Contain information on the
    '                    cube.
    '          Node -    Branch ID of node where this
    '                    information will be appended.
    '
    Dim keytext As String
    Const NODE_NAME_NOT_UNIQUE = 35602

    On Error GoTo GetCube_Err

    keytext = "Cube:" & mtdCube.Name
    ' Adds label node and then calls the routines that
    ' append dimension and measure information.
    TreeView1.Nodes.Add Node, tvwChild, keytext, "Cube:" & mtdCube.Name
    GetMeasuresDimension mtdCube.MeasuresDimension, keytext
    GetDimensions mtdCube.Dimensions, keytext

GetCube_End:
    Exit Sub

GetCube_Err:
    If Err.Number = NODE_NAME_NOT_UNIQUE Then
        keytext = keytext & intUniqueID + 1
        Err.Clear
        Resume
    End If
End Sub

This procedure accepts the following arguments: mtdCube, which is an IMdhCube object that contains metadata for the Cube object, and the NodeString which contains the ID of the parent node. The procedure appends a Cube label to the tree view and then calls theGetMeasuresDimensionsubroutine to get information on the measures associated with the cube. Then, theGetDimensionssubroutine is called to retrieve information on the dimensions associated with the cube.

Adding Measures Information

The next procedure adds information on the Measures object to the tree. Type the following procedure into thefrmDetailform code window:

Private Sub GetMeasuresDimension(ByVal mtdMeasuresDim As IMdhMeasuresDimension, _
                ByVal Node As String)
    ' Purpose: Appends measures information to the tree.
    '
    ' Accepts: mtdMeasureDim - Collection containing information
    '                          on the measures in the cube.
    '          Node -          Branch ID of node where this
    '                          information will be appended.
    '
    Dim keytext As String
    Dim i As Integer
    Dim objMeasure As IMdhMeasure
    Const NODE_NAME_NOT_UNIQUE = 35602

    On Error GoTo GetMeasuresDimension_Err

    ' Adds label node and then appends measures information
    ' to the tree.
    TreeView1.Nodes.Add Node, tvwChild, "Measures", _
        "Measures"
    For i = 0 To mtdMeasuresDim.Measures.Count - 1
        Set objMeasure = mtdMeasuresDim.Measures.Item(i)
        keytext = objMeasure.Caption
        TreeView1.Nodes.Add "Measures", tvwChild, keytext, keytext
    Next i

GetMeasuresDimension_End:
    Exit Sub

GetMeasuresDimension_Err:
    If Err.Number = NODE_NAME_NOT_UNIQUE Then
        keytext = objMeasure.Caption & intUniqueID + 1
        Err.Clear
        Resume
    End If
End Sub

This procedure accepts the following arguments: mtdMeasuresDim, which represents an IMdhMeasuresDimension collection, which contains the Measures objects, and theNodeString, which contains the ID of the parent node. The procedure appends a MeasuresDimension label to the tree and then iterates through the IMdhMeasuresDimension collection and adds information on each measure to the tree.

Adding Dimension Information

The following procedure appends aDimensionslabel node to the tree and then iterates through the IMdhDimensions collection to add information on each Dimension object to the tree.

Type the following code to the frmDetail form code window:

Private Sub GetDimensions(ByVal mtdDimensions As IMdhDimensions, _
                ByVal Node As String)
    ' Purpose: Appends the dimension information to the tree.
    '
    ' Accepts: mtdDimension - Collection containing information
    '                         on the dimensions in the cube.
    '          Node -         Branch ID of node where this
    '                         information will be appended.
    '
    Dim keytext As String
    Dim i As Integer
    Dim objDim As IMdhDimension
    Dim immediateparent As String
    Const NODE_NAME_NOT_UNIQUE = 35602

    On Error GoTo GetDimensions_Err

    keytext = "Dimensions"
    ' Adds label node and then appends dimension information
    ' to the tree.
    TreeView1.Nodes.Add Node, tvwChild, keytext, "Dimensions"
    immediateparent = keytext
    For i = 0 To mtdDimensions.Count - 1
        Set objDim = mtdDimensions.Item(i)
        keytext = objDim.Caption
        TreeView1.Nodes.Add immediateparent, tvwChild, keytext, _
            "Dimension:" & objDim.Caption
    Next i

GetDimensions_End:
    Exit Sub

GetDimensions_Err:
    If Err.Number = NODE_NAME_NOT_UNIQUE Then
        keytext = keytext & intUniqueID + 1
        Err.Clear
        Resume
    End If
End Sub

This procedure accepts the following arguments: mtdDimensions, which represents the IMdhDimensions collection, and theNodeString, which contains the ID of the parent node.

Adding Trait Information

Traits in a Data Analyzer view are a way to graphically represent qualities to the user. A trait may be single (like length or color) and contain one quality value or multiple (like a grid) and contain a list of qualities.

Qualities are essentially the same as measures in OLAP terminology. Each view has a list of qualities, which are common to all aspects (for example, the measures shown in the Grid view are the same in all aspects).

**Note   **The term quality is used instead of measure because a quality is not necessarily an OLAP measure. Currently, other quality types are supported: template measures and member properties (caption) for example.

In Data Analyzer, the TraitManager object manages the traits of a view. The following procedure adds aTraitslabel node to the tree and then calls theGetTraitQualitiessubroutine, once for each type of trait.

Type the following procedure into thefrmDetailform code window:

Private Sub LoadTraits()
    ' Purpose: Populates the TreeView control with Trait
    ' information; single traits (length, color) and
    ' multiple traits (grid).
    '
    Dim objTM As TraitsManager
    Set objTM = mobjView.TraitsManager()

    ' Add Traits label to the tree.
    TreeView1.Nodes.Add "Root", tvwChild, "Traits", "Traits"

    ' Routines which append the various traits.
    GetTraitQualities objTM.Trait(trtLength), "Traits", "Length"
    GetTraitQualities objTM.Trait(trtColor), "Traits", "Color"
    GetTraitQualities objTM.Trait(trtGrid), "Traits", "Grid"
End Sub

Next, we will examine theGetTraitQualitiessubroutine. This procedure appends theTraitQualitieslabel node to the tree and then iterates through the Qualities collection of the Trait object to add information on each Quality object to the tree. Type the following procedure into thefrmDetailform code window:

Private Sub GetTraitQualities(ByVal trtTrait As Trait, _
                ByVal Node As String, ByVal trtType As String)
    ' Purpose: Retrieves the trait qualities such as
    '          profitability, total revenue, and so forth
    '          and appends them to the tree.
    '
    ' Accepts: trtTrait - Trait such as length or color.
    '          Node -     Branch ID of node where this
    '                     information will be appended.
    '          trtType -  Type of trait such as length,
    '                     color, or grid.
    '
    Dim objQuals As Qualities
    Dim i As Integer
    Dim keytext As String
    Dim immediateparent As String
    Const NODE_NAME_NOT_UNIQUE = 35602

    On Error GoTo GetTraitQualities_Err

    keytext = "TraitQualities:" & trtType

    ' Qualities is a collection of Quality measures for each
    ' aspect such as Profitability and Total Revenue.
    Set objQuals = trtTrait.Qualities

    ' Add trait container (parent) node to tree.
    TreeView1.Nodes.Add Node, tvwChild, keytext, "TraitQualities:" & _
        trtType
    immediateparent = keytext

    ' Add individual quality child node(s) to the tree.
    For i = 0 To objQuals.Count
        TreeView1.Nodes.Add immediateparent, tvwChild, keytext, _
            "Quality:" & objQuals.QualityID(i)
    Next i

GetTraitQualities_End:
    Exit Sub

GetTraitQualities_Err:
    If Err.Number = NODE_NAME_NOT_UNIQUE Then
        keytext = keytext & intUniqueID + 1
        Err.Clear
        Resume
    End If
End Sub

This procedure accepts the following arguments: trtTrait, which represents the Trait object, and theNodeString, which contains the ID of the parent node.

Creating the frmDialogs Form

The final form that we will create is thefrmDialogsform. This form is used to present a list of operations that perform tasks also available from the user interface. For example, there is an Open File operation that displays the Open File dialog box, a Save As File dialog box, a New View dialog box, and a Change View dialog box. When the user selects an operation from the list, the appropriate dialog box is displayed. Figure 5 shows thefrmDialogsform when it is completed.

Aa155445.odc_dactrlexcel05(en-us,office.10).gif

Figure 5. The frmDialogs form when it is completed

Let's create the form and add code to it:

  1. On the Insert menu, click UserForm.

  2. In the Properties window for the UserForm, replace the text in the Name property with frmDialogs and the text in the Caption property with Data Analyzer User Interface Options.

  3. In the Toolbox, drag a ComboBox control to the form.

  4. In the Properties window for the control, replace the text in the Name property with cboDialogs and click 2—fmStyleDropDownList in the Style property to prevent users from typing in the drop-down list box.

  5. In the Toolbox, drag a CommandButton control onto the lower right hand corner of the form.

  6. In the Properties window for the control, replace the text in the Name property with cmdOK and the text in the Caption property with OK.

  7. Double-click the control to bring up thecmdOK_Clickevent procedure. Between the Sub statement and the End Sub statement, add the following:

    Unload frmDialogs

  8. Click cboDialogs from the left drop-down list box at the top of thefrmDialogsform code window. This will create thecboDialogs_Clickevent procedure. Type the following in that procedure:

    mintDialog = cboDialogs.ListIndex + 1

    In the left drop-down list box at the top of thefrmDialogsform code window, click UserForm. This will create thefrmDialogs_Activateevent procedure. Type the following code in that procedure:

    ' Purpose: Populates the cboDialog combo box control.
With cboDialogs
    .AddItem "About Dialog"
    .AddItem "Open File Dialog"
    .AddItem "Save As File Dialog"
    .AddItem "HTML Report Dialog"
    .AddItem "New View Dialog"
    .AddItem "Change View Dialog"
    .AddItem "Drill Through Dialog"
    .AddItem "Export to XL Pivot Table Dialog"
    .AddItem "Open Using Connection Dialog"
    .AddItem "Business Center Dialog"
    .AddItem "Export to XL Static Dialog"
    .ListIndex = 1
End With

Now let's examine how this form works. When the user clicks Show Dialog on thefrmControlform, thecmdDialog_Clickevent procedure executes and opens thefrmDialogsform. ThefrmDialogs_Activateevent procedure executes which populates thecboDialogscombo box. The user then clicks thecboDialogscombo box and selects one of the available options. This selection assigns the value of thecboDialogsListIndex property to themintDialogInteger variable. When the user closes thefrmDialogsform, programmatic control returns to thecmdDialog_Clickevent procedure. The next statement executes the ShowDialog method of the Data Analyzer Application object and passes the ID of the user's selection (as contained in themintDialogvariable). The requested dialog box appears.

Running Code When the Workbook Opens

The only thing left is to add the code that will open thefrmIntroform when the workbook is opened. To do this, we need to add a statement to the Workbook_Open subroutine:

  1. From the Project Explorer window, double-click the Workbook icon.

  2. In the left drop-down list box at the top of the code window, click Workbook. This will create the Workbook_Open event procedure.

  3. Between the Sub statement and the End Sub statement, type the following statement:

    frmIntro.Show

    Now, when the workbook is opened, thefrmIntroform will be displayed.

  4. Save the workbook and close Excel.

Putting It All Together

Now that we created the forms we need and added the code, let's try out the sample.

  1. Open the sample workbook file you just created in Excel.

  2. Click OK in the Open a View dialog box. The File Open dialog box is displayed (see Figure 6).

  3. In the File Open dialog box, navigate to a Data Analyzer view file and click Open. The Microsoft Data Analyzer ActiveX Control—Sample dialog box is displayed.

    Hint   In a default installation of Data Analyzer, the Airline.max file is located at C:\Program Files\Microsoft Data Analyzer\Data Analyzer 3.5\Airline.max.

    Aa155445.odc_dactrlexcel06(en-us,office.10).gif

    Figure 6. The File Open dialog box

    You can now use and explore the features of the Data Analyzer just as if you were in the Data Analyzer application.

  4. Click Show Dialog. In the Data Analyzer User Interface Options dialog box, click About Dialog in the drop-down list box. The Data Analyzer About Dialog dialog box appears (see Figure 7).

  5. Click OK. The Microsoft Data Analyzer ActiveX Control—Sample dialog box appears.

    Aa155445.odc_dactrlexcel07(en-us,office.10).gif

    **Figure 7. The Data Analyzer About Dialog box

  6. Click Show Detail. The Structure of a Data Analyzer View dialog box appears. Click on the + icons to explore the different nodes of the tree. Click Close. The Microsoft Data Analyzer ActiveX Control—Sample dialog box appears.

  7. Click Close to close the Microsoft Data Analyzer ActiveX Control—Sample dialog box.

Conclusion

In this article, we demonstrated embedding the Microsoft Data Analyzer ActiveX control into a UserForm in Excel 2002. We created the forms and sample code to connect the control to a Data Analyzer view file and display a dialog that allows the user to select from a list of actions normally available from the user interface. We also created a form and sample code that allows the user to display and populate a tree view of the different components that make up a Data Analyzer View. And lastly, we added code to the workbook so that a form is displayed when the workbook is opened.

By using the sample code and procedures in this article, you can embed the Data Analyzer ActiveX control into your applications and explore the structure of your data using the Data Analyzer object models.