Excel, VBA, Pivot Tables, and MDX Oh My!

Marco Russo on SQL Blog.com details a really simple way to pull the MDX for a pivot table using VBA when you are connected to an Analysis Services cube. Below is the cut down version that I added to my data analysis toolbox in Excel (the full version can be found on Marco's blog here):

Sub ShowPvtMDX()

Dim mdxQuery As String

Dim pvt As PivotTable

Dim ws As Worksheet

Dim x As Integer

Set pvt = ActiveCell.PivotTable

mdxQuery = pvt.MDX

Set ws = Worksheets.Add

ws.Range("A1") = mdxQuery

End Sub

That's it. If you connect this code to a key command in Excel all you have to do is select the pivot table and execute the key command and it will automatically create a new tab and put the MDX in cell A1 of that tab. I have tested this code in Excel 2010 and it does work, but it only works with pivot tables linked directly to a cube. Also, while it will work as long as you have a cell within the pivot table selected it returns the MDX used to create the entire pivot table, not just the cell you have selected.