Have you ever tried to reverse engineer an Excel pivot table? It’s not as easy as you would think! Whether you just want to find out the datasource details, or identify the query that was used, there is just no simple way of getting Excel to tell you.
The macro below will loop through every sheet in a workbook, and will document the datasources, SQL or MDX queries as well as the page, row, column and data fields.
To use it, add it into your macros, then select a starting cell where you want te report to be placed and run the macro. It’s pretty raw, and may need some tweaks to suit your requirements but it should give you a good starting point. I use it on Excel 2003 MDX pivots from SQL Server Analysis Services 2005, but I presume it will work on other versions of Excel as well.
Public Sub PivotDetails()
Dim ws As Worksheet
Dim qt As QueryTable
Dim pt As PivotTable
Dim pc As PivotCache
Dim pf As PivotField
For Each ws In ActiveWorkbook.Sheets
For Each qt In ws.QueryTables
ActiveCell.Value = "Sheet"
ActiveCell.Offset(0, 1).Value = ws.Name
ActiveCell.Offset(1, 0).Select
ActiveCell.Value = "Data Source"
ActiveCell.Offset(0, 1).Value = qt.Connection
ActiveCell.Offset(1, 0).Select
ActiveCell.Value = "Query"
ActiveCell.Offset(0, 1).Value = qt.CommandText
Next qt
ActiveCell.Offset(2, 0).Select
For Each pt In ws.PivotTables
ActiveCell.Offset(1, 0).Select
ActiveCell.Value = "Pivot Table"
ActiveCell.Offset(0, 1).Value = pt.Name
ActiveCell.Offset(1, 0).Select
ActiveCell.Value = "Connection"
ActiveCell.Offset(0, 1).Value = pt.PivotCache.Connection
ActiveCell.Offset(1, 0).Select
ActiveCell.Value = "SQL"
ActiveCell.Offset(0, 1).Value = pt.PivotCache.CommandText
ActiveCell.Offset(1, 0).Select
ActiveCell.Value = "MDX"
ActiveCell.Offset(0, 1).Value = pt.MDX
For Each pf In pt.PageFields
ActiveCell.Offset(1, 0).Select
ActiveCell.Value = "Page"
ActiveCell.Offset(0, 1).Value = pf.Name
ActiveCell.Offset(0, 2).Value = pf.CurrentPageName
Next pf
For Each pf In pt.ColumnFields
ActiveCell.Offset(1, 0).Select
ActiveCell.Value = "Column"
ActiveCell.Offset(0, 1).Value = pf.Name
Next pf
For Each pf In pt.RowFields
ActiveCell.Offset(1, 0).Select
ActiveCell.Value = "Row"
ActiveCell.Offset(0, 1).Value = pf.Name
Next pf
For Each pf In pt.DataFields
ActiveCell.Offset(1, 0).Select
ActiveCell.Value = "Data"
Next pf
Next pt
Next ws
End Sub
Thanks a lot for this post!
By the way the code works with Office 2007 SP1.
Awesome post, exactly what I was looking for!
btw, the line:
ActiveCell.Offset(0, 1).Value = pt.MDX
Errors out in excel 2000 (I commented it out and everything else ran fiine)
Nice work!