List PivotCache.Commandtext

[douglascfast]

All,

I want to list details about my pivot tables (connected to a SQL
server)

The code below was posted for me, I want to add one line:


wksOutput.Cells(r, 5) = pvc.CommandText

What do I need to do?

Doug
Jax


Sub Whatever()

Dim wks As Worksheet
Dim pvt As PivotTable
Dim wksOutput As Worksheet
Dim r As Long
Dim strOutputSheet As String

strOutputSheet = "Pivot Listing"


On Error Resume Next
Application.DisplayAlerts = False
ThisWorkbook.Worksheets(strOutputSheet).Delete
Application.DisplayAlerts = True
On Error GoTo ErrHandler

Set wksOutput = ThisWorkbook.Sheets.Add
wksOutput.Name = strOutputSheet
wksOutput.Range("A1") = "Sheet Name"
wksOutput.Range("B1") = "Pivot Name"
wksOutput.Range("C1") = "Refresh Date"
wksOutput.Range("D1") = "Source Data"
wksOutput.Range("E1") = "My Commandtext"
wksOutput.Range("A1:E1").Font.Bold = True

r = 2
For Each wks In ThisWorkbook.Worksheets
For Each pvt In wks.PivotTables

wksOutput.Cells(r, 1) = wks.Name
wksOutput.Cells(r, 2) = pvt.Name
wksOutput.Cells(r, 3) = pvt.RefreshDate
wksOutput.Cells(r, 4) = pvt.SourceData
r = r + 1

Next pvt
Next wks
Columns("A:C").EntireColumn.AutoFit


ExitHere:
Exit Sub
ErrHandler:
MsgBox "Cancelling: " & Err.Number & " " & Err.Description,
vbOKOnly, "ERROR!"
Resume ExitHere

End Sub

 

[Tom Ogilvy]

wksOutput.Cells(r, 1) = wks.Name
wksOutput.Cells(r, 2) = pvt.Name
wksOutput.Cells(r, 3) = pvt.RefreshDate
wksOutput.Cells(r, 4) = pvt.SourceData
wksOutput.Cells(r,5) = pvt.PivotCache.CommandText

make necessary header adjustments as well.

 

[Tom Ogilvy]

Just an added caution:

If the datasource is not external, the pivotcache won't have a commandtext
and you will get an error so you might want to add error handling

wksOutput.Cells(r, 1) = wks.Name
wksOutput.Cells(r, 2) = pvt.Name
wksOutput.Cells(r, 3) = pvt.RefreshDate
wksOutput.Cells(r, 4) = pvt.SourceData
On Error Resume Next
wksOutput.Cells(r, 5) = pvt.PivotCache.CommandText
On Error goto ErrHandler

 

[douglascfast]

Tom,

Perfect!

Thanks

Doug