如何在VBA中从Power Pivot DataModel提取列值填充ComboBox

核心思路 #

直接通过DAX查询从Power Pivot DataModel中提取目标列的数值，将结果转换为数组后填充ComboBox。这种方法无需把数据加载到工作表，完全符合你「仅连接」的需求。

完整代码示例 #

1. 通用函数：从DataModel表列提取值 #

Function GetDataModelColumnValues(modelTableName As String, columnName As String) As Variant
    Dim daxQuery As String
    Dim conn As WorkbookConnection
    Dim rs As Object ' ADODB.Recordset
    
    ' 构建DAX查询：提取列的唯一值（若需保留重复值，将VALUES替换为ALL）
    daxQuery = "EVALUATE VALUES('" & modelTableName & "'[" & columnName & "])"
    
    ' 连接到Power Pivot DataModel的默认连接
    Set conn = ThisWorkbook.Connections("ThisWorkbookDataModel")
    
    ' 执行DAX查询并获取记录集
    Set rs = CreateObject("ADODB.Recordset")
    rs.Open daxQuery, conn.OLEDBConnection
    
    ' 将记录集转换为行优先数组（空结果则返回空数组）
    If Not rs.EOF Then
        GetDataModelColumnValues = Application.Transpose(rs.GetRows)
    Else
        GetDataModelColumnValues = Array()
    End If
    
    rs.Close
    Set rs = Nothing
    Set conn = Nothing
End Function

2. 用户窗体初始化时填充ComboBox #

假设你的用户窗体名为UserForm1，包含两个ComboBox：ComboBox_DoorOptions对应Door Options列，ComboBox_Finishing对应Finishing列，可在窗体初始化事件中调用上述函数：

Private Sub UserForm_Initialize()
    Dim doorValues As Variant
    Dim finishingValues As Variant
    Dim i As Integer
    
    ' 获取Door Options列的唯一值并填充ComboBox
    doorValues = GetDataModelColumnValues("src_FieldOptions", "Door Options")
    If UBound(doorValues) >= 0 Then
        For i = LBound(doorValues) To UBound(doorValues)
            ComboBox_DoorOptions.AddItem doorValues(i, 1)
        Next i
    End If
    
    ' 获取Finishing列的唯一值并填充ComboBox
    finishingValues = GetDataModelColumnValues("src_FieldOptions", "Finishing")
    If UBound(finishingValues) >= 0 Then
        For i = LBound(finishingValues) To UBound(finishingValues)
            ComboBox_Finishing.AddItem finishingValues(i, 1)
        Next i
    End If
End Sub

3. 原有调试代码的补充（输出行值） #

如果你只是想在原有代码中调试输出列的行值，可修改如下：

Sub Find_Values()
    Dim conn As WorkbookConnection
    Dim model_Table As ModelTable
    Dim model_Column As ModelTableColumn
    Dim daxQuery As String
    Dim rs As Object
    
    ' 连接到Power Pivot DataModel
    Set conn = ThisWorkbook.Connections("ThisWorkbookDataModel")
    
    For Each model_Table In conn.ModelTables
        Debug.Print "Table Name: " & model_Table.Name
        For Each model_Column In model_Table.ModelTableColumns
            Debug.Print "Column Header: " & model_Column.Name & "; " & model_Column.DataType
            
            ' 构建DAX查询获取当前列的所有值
            daxQuery = "EVALUATE VALUES('" & model_Table.Name & "'[" & model_Column.Name & "])"
            Set rs = CreateObject("ADODB.Recordset")
            rs.Open daxQuery, conn.OLEDBConnection
            
            ' 遍历记录集输出行值
            Debug.Print "Row Values:"
            Do While Not rs.EOF
                Debug.Print "  " & rs.Fields(0).Value
                rs.MoveNext
            Loop
            
            rs.Close
            Set rs = Nothing
        Next model_Column
    Next model_Table
       
    Set conn = Nothing
End Sub

关键说明 #

• DAX函数选择：VALUES()返回列的唯一值，若需要保留所有重复行值，替换为ALL('表名'[列名])即可。
• 连接对象注意：使用ThisWorkbookDataModel作为连接名，这是Power Pivot DataModel的默认内部连接，而非外部数据查询的Query - src_FieldOptions。
• 数组转置：ADODB.Recordset.GetRows()返回的是列优先的二维数组，通过Application.Transpose()转置为行优先后，更便于遍历填充ComboBox。

内容的提问来源于stack exchange，提问作者pablo_bg