VBA 操作 PowerPivot 模型数据

注意VBA中引用Activex Data Object(ADO???)

Sub 链接回表()
Sheets(3).Select
Set ML = Sheets(3).ListObjects.Add(SourceType:=4, Source:=ThisWorkbook.Connections(2), Destination:=Range("A1"))
With ML
    .Name = "测试"
    With .TableObject.WorkbookConnection.OLEDBConnection
        .CommandText = "Evaluate Filter('表1',[班级]=""A"")"
        .CommandType = xlCmdDAX
    End With
    .Refresh
End With
ThisWorkbook.Connections(2).Refresh
End Sub

ActiveSheet.Range("F5").Formula = "=CUBEVALUE(""ThisWorkbookDataModel"", CUBEMEMBER(""ThisWorkbookDataModel"", ""[Measures].[学生]""))"

注意：不会写cube函数自己搞个透视表拉几下后转olap自己看，看几下你就会了，中间字符串可以拼接你看着办，，，

骚操作马上回来，，，哈哈
PowerPivot模型打开后会在本地类似power bi有个缓存空间:

————————————————————————————————————————————————
我不是真的要造病毒啊，，，

道高一尺 魔高一丈
https://pbihub.cn/users/44