All,

I have many pivot tables on many sheets in a single workbook. Each of
these may have a diffrent data source pointing to MS SQL.

What I would like is a Macro to run which tells me something like this:

SheetName PivotTable Name Data Souce

Sheet1 PT1 DB.my.mytable

Is there an easy way to loop through this?

I do not want to go through over 50 Pivot Tables and the wizard to get
this info. Too time consuming.

Thanks in advance for any help or suggestions.

Doug Fast

In a General Code Module:
(This will create a sheet called "Pivot Listing". It will also delete it if
it already exists. If this would cause a problem in your workbook, change
the name.)

Public Sub ListPivotTables()
On Error GoTo ErrHandler

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") = "Source Data"
wksOutput.Range("A1:C1").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.SourceData
r = r + 1
Next pvt
Next wks
Columns("A:C").EntireColumn.AutoFit

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

HTH,
--
George Nicholson

Remove 'Junk' from return address.


wrote in message
ups.com...
All,

I have many pivot tables on many sheets in a single workbook. Each of
these may have a diffrent data source pointing to MS SQL.

What I would like is a Macro to run which tells me something like this:

SheetName PivotTable Name Data Souce

Sheet1 PT1 DB.my.mytable

Is there an easy way to loop through this?

I do not want to go through over 50 Pivot Tables and the wizard to get
this info. Too time consuming.

Thanks in advance for any help or suggestions.

Doug Fast

George,

This is great, thanks so much. Are other things that I could bring
back as well? I figure why not go all the way. Like Row Count, Memory
usage, ect.

Doug

George and all,

Sorry to be a pest my data source is showing this:
DRIVER=SQL Server;SERVER=AMLBR06;UID=u777064;APP=Microsoft®
Query;WSID=NYCTO1X401169;DATABASE=BPA;Network=DBMS SOCN;QueryLogFile=Yes;Trusted_Connection=Yes

Vs what I was hoping for was this BPA.DF.My_Base_Table

This is connecting to a MS SQL Database.

Doug