Retrieving data from DataModel (Power Pivot) within VBA

Question

first of all, apologies. I am a relatively new VBA user, and I am a bit lost in the search for the answer to the question below.

I am creating a UserForm which will include some ComboBox to get user's input to query data. I would like the possible values of these ComboBoxes to be "pulled" from the DataModel of the workbook, where I loaded (as Connection Only) the Excel file containing the possible options. Such excel is organized by columns (one column per ComboBox I'll need to use), where each row is a possible value for the comboBox.

I would strongly prefer to load the file as connection only (rather than creating a PivotTable in one of the Workbook sheets).

I believe that, if I were able to "extract" from the columns in the DataModel the various values in each column, I could use the Add.Item method of the ComboBox.

The issue I am having: I cannot retrieve the values of the columns in the Power Pivot Data Model.

The data in the Power Pivot is organized as such:

Table name: src_FieldOptions Column1: Door Options (possible values in rows: Electric, Pneumatic, Manual) Column2: Finishing (Painted, Glossy, Matte)

I tried to use the code using the DataModel Object (see below), but I can't find a way to retrieve the column values (for each row), even if I am able to identify the columns.

Any help you can give? Thanks a lot

Sub Find_Values()

    Dim conn As WorkbookConnection
   
    Dim model_Table As modelTable
    Dim model_Column As ModelTableColumn

    ' Set the connection to the Power Pivot Data Model
    Set conn = ThisWorkbook.Connections("Query - src_FieldOptions")
    
    For Each model_Table In conn.ModelTables
       Debug.Print "Table Name: " & model_Table.Name. '<--this is working as expected
       For Each model_Column In model_Table.ModelTableColumns
           Debug.Print "Column Header: " & model_Column.Name & "; " & model_Column.DataType '<-- working as expected

        ' QUESTION FOR YOU ALL: what code should I use here to retrieve the values in the rows of the Data model? 

        Next model_Column
    Next model_Table
       
End Sub

Answer 1

• I couldn't locate any property of ModelTable object for retrieving data in Object Browser.
• There is a walkaround solution to retrieve data with data model ADOConnection.

Microsoft documentation:

ModelConnection.ADOConnection property (Excel)

Option Explicit

Sub Find_Value_Demo()
    Dim oConn As WorkbookConnection, oRS As Object ' Recordset
    Dim oCnn As Object, sTabName As String
    ' Set the connection to the Power Pivot Data Model
    Set oConn = ThisWorkbook.Connections("Query - src_FieldOptions")
    ' Set oConn = ThisWorkbook.Connections("Query - Table1") ' for testing
    sTabName = oConn.ModelTables(1).Name ' Assumes there is only a table
    Set oCnn = ThisWorkbook.Model.DataModelConnection.ModelConnection.ADOConnection
    Set oRS = CreateObject("ADODB.RecordSet")
    oRS.Open "SELECT * From $" & sTabName & ".$" & sTabName, oCnn
    ' modify as need, to manipulate the data
    Dim i As Long, c As Long
    Sheets.Add
    For i = 1 To oRS.Fields.Count - 1
        Cells(1, i).Value = oRS.Fields(i - 1).Name
    Next i
    oRS.MoveFirst
    i = 2
    Do While Not oRS.EOF
        For c = 1 To oRS.Fields.Count - 1
            Cells(i, c) = oRS.Fields(c - 1)
        Next
        i = i + 1
        oRS.MoveNext
    Loop
End Sub

---

Update:

Question: I don't understand the reason for referencing twice the sTabName (both with "$" and with ".$"

• It's a MSOLAP sql. The syntax is different with Microsoft.ACE.OLEDB.12.0.

?oCnn
Provider=MSOLAP.8;Persist Security Info=True;Initial Catalog=Microsoft_SQLServer_AnalysisServices;Data Source=$Embedded$;MDX Compatibility=1;Safety Options=2;MDX Missing Member Mode=Error;Subqueries=0;Optimize Response=7;Update Isolation Level=2

• A query to extract recordes with non-blank name

 sSql = "EVALUATE FILTER('Table1', NOT ISBLANK([name]))"

• Another sql option to extract all rows

sSql = "EVALUATE 'Table1'"