Thread: Creating a Pivot Table from MDX

We are converting an existing BI application to Excel pivot tables. Does anyone know of a way to create a pivot table from an MDX statement? I tried this macro:

Sub SetPivotMDX()

Dim sourceData As Worksheet
Set sourceData = Worksheets("TestData")

Dim strName As String
strName = Left(sourceData.Range("B2").Value, 5)

Dim strFormula As String

' get the MDX for the pivot table
strFormula = sourceData.Range("B3").Value

Dim WS As Worksheet
Set WS = Sheets.Add
WS.Activate
WS.Name = strName

ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, sourceData:= _
"TestData!R2C1:R3C3", Version:=xlPivotTableVersion10).CreatePivotTable _
TableDestination:="JI026!R3C1", TableName:="PivotTable1", DefaultVersion _
:=xlPivotTableVersion10

WS.Select

WS.PivotTables("PivotTable1").MDX = strFormula

End Sub

But I've since found that the pivotTables.MDX property is read-only.

Anyone have any ideas on how to do this?