Option Explicit
Private Const m_NA As String = "N/A"
' Connection
' - OLE DB settings that enable Microsoft Excel to connect to an OLE DB data source
' - ODBC settings that enable Excel to connect to an ODBC data source
' - A URL that enables Excel to connect to a web data source
' - The path to and file name of a text file
' - The path to and file name of a file that specifies a database or web query
Sub ListPivotTables()
Dim wkb As Workbook
Dim wks As Worksheet
Dim pvt As PivotTable
Set wkb = ThisWorkbook
For Each wks In wkb.Worksheets
For Each pvt In wks.PivotTables
Debug.Print "Worksheet", wks.Name
Debug.Print "Pivot Name", pvt.Name
Debug.Print "Address", pvt.DataBodyRange.Address
Debug.Print "Source Data", SourceData(pvt)
Debug.Print "Command Text", CommandText(pvt)
'/Debug.Print "BackGr. Qry", pvt.PivotCache.BackgroundQuery
Debug.Print "Connection", connection(pvt)
Debug.Print "Cnn Source", ConnectionSource(connection(pvt))
'/Debug.Print "Calc Fields", pvt.CalculatedFields.Count
'/Debug.Print "Query Type", QueryType(pvt)
Debug.Print String(50, "-")
Next pvt
Next wks
'''Debug.Print "ADO Connection", ADOConnection(pvt)
Set wks = Nothing
Set wkb = Nothing
Set pvt = Nothing
End Sub
'''Private Function ADOConnection(ByRef pvt As PivotTable) As String
''' Dim cnn As String
''' On Error Resume Next
''' cnn = pvt.PivotCache.ADOConnection
''' If Len(cnn) = 0 Then
''' cnn = m_NA
''' End If
''' ADOConnection = cnn
'''End Function
Private Function connection(ByRef pvt As PivotTable) As String
Dim cnn As String
On Error Resume Next
cnn = pvt.PivotCache.connection
If Len(cnn) = 0 Then
cnn = m_NA
End If
connection = cnn
End Function
Private Function ConnectionSource(ByRef connection As String) As String
Dim value As String
Dim StartPos As Integer
Dim EndPos As Integer
value = m_NA
If connection = m_NA Then
GoTo ExitFunction
End If
StartPos = InStr(1, connection, "Source=", vbTextCompare)
If StartPos = 0 Then
GoTo ExitFunction
End If
StartPos = StartPos + Len("Source=")
EndPos = InStr(StartPos, connection, ";", vbTextCompare)
If EndPos = 0 Then
GoTo ExitFunction
End If
value = Mid$(connection, StartPos, EndPos - StartPos)
ExitFunction:
ConnectionSource = value
End Function
Private Function CommandText(ByRef pvt As PivotTable) As String
Dim cnn As String
On Error Resume Next
cnn = pvt.PivotCache.CommandText
If Len(cnn) = 0 Then
cnn = m_NA
End If
CommandText = cnn
End Function
Private Function QueryType(ByRef pvt As PivotTable) As String
Dim value As String
On Error Resume Next
value = QueryTypeToString(pvt.PivotCache.QueryType)
If Len(value) = 0 Then
value = m_NA
End If
QueryType = value
End Function
Private Function QueryTypeToString(ByVal QueryType As Integer) As String
Dim value As String
Select Case QueryType
Case xlADORecordset ' 7
value = "ADO Recordset"
Case xlDAORecordset ' 2
' query tables only
value = "DAO Recordset"
Case xlODBCQuery ' 1
value = "ODBC Dta Source"
Case xlOLEDBQuery ' 5
value = "OLE DB Query (including OLAP)"
Case xlTextImport ' 6
' query tables only
value = "Text File"
Case xlWebQuery ' 4
' query tables only
value = "Webpage"
Case Else
End Select
End Function
Private Function SourceData(ByRef pvt As PivotTable) As String
On Error Resume Next
SourceData = pvt.SourceData
If Len(SourceData) = 0 Then
SourceData = m_NA
End If
End Function
Examples
Worksheet Sheet Raw
Pivot Name PivotTable6
Address $B$5:$C$32
Source Data 'Data Raw'!R1C1:R259C8
Command Text N/A
Connection N/A
Cnn Source N/A
--------------------------------------------------
Worksheet Sheet Table
Pivot Name PivotTable1
Address $C$4:$P$31
Source Data Durations
Command Text N/A
Connection N/A
Cnn Source N/A
--------------------------------------------------
Worksheet Sheet Pvt
Pivot Name PivotTable5
Address $C$4:$D$17
Source Data Durations
Command Text N/A
Connection N/A
Cnn Source N/A
--------------------------------------------------
Worksheet CSV
Pivot Name PivotTable7
Address $C$4:$D$17
Source Data N/A
Command Text SELECT * FROM [data_file (2)]
Connection OLEDB;Provider=Microsoft.Mashup.OleDb.1;Data Source=$Workbook$;Location=data_file (2);Extended Properties=""
Cnn Source $Workbook$
--------------------------------------------------
Worksheet SQL Server
Pivot Name PivotTable2
Address $C$5:$D$48
Source Data N/A
Command Text "Firebird"."dbo"."prd_dqr_BenchCcyBreakdown"
Connection OLEDB;Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=True;Initial Catalog=Firebird;Data Source=ukfil6736win,51433;Use Procedure for Prepare=1;Auto Translate=True;Packet Size=4096;Workstation ID=GBDA251759;Use Encryption for Data=False;Tag with column collation when possible=False
Cnn Source ukfil6736win,51433
--------------------------------------------------
Worksheet Access
Pivot Name PivotTable4
Address $C$3:$C$207
Source Data N/A
Command Text tbl_MasterFund
Connection OLEDB;Provider=Microsoft.ACE.OLEDB.12.0;User ID=Admin;Data Source=J:\Stuff\Project Resources\PivotTables\funds summary.mdb;Mode=Share Deny Write;Extended Properties="";Jet OLEDB:System database="";Jet OLEDB:Registry Path="";Jet OLEDB:Engine Type=5;Jet OLEDB:Database Locking Mode=0;Jet OLEDB:Global Partial Bulk Ops=2;Jet OLEDB:Global Bulk Transactions=1;Jet OLEDB:New Database Password="";Jet OLEDB:Create System Database=False;Jet OLEDB:Encrypt Database=False;Jet OLEDB:Don't Copy Locale on Compact=False;Jet OLEDB:Compact Without Replica Repair=False;Jet OLEDB:SFP=False;Jet OLEDB:Support Complex Data=False;Jet OLEDB:Bypass UserInfo Validation=False;Jet OLEDB:Limited DB Caching=False;Jet OLEDB:Bypass ChoiceField Validation=False
Cnn Source J:\Stuff\Project Resources\PivotTables\funds summary.mdb
--------------------------------------------------
Worksheet Workbook
Pivot Name PivotTable3
Address $C$4:$D$41
Source Data N/A
Command Text RESULTS$
Connection OLEDB;Provider=Microsoft.ACE.OLEDB.12.0;User ID=Admin;Data Source=J:\Stuff\Project Resources\PivotTables\20190523__PSG_BLOCK.xls;Mode=Share Deny Write;Extended Properties="HDR=YES;";Jet OLEDB:System database="";Jet OLEDB:Registry Path="";Jet OLEDB:Engine Type=35;Jet OLEDB:Database Locking Mode=0;Jet OLEDB:Global Partial Bulk Ops=2;Jet OLEDB:Global Bulk Transactions=1;Jet OLEDB:New Database Password="";Jet OLEDB:Create System Database=False;Jet OLEDB:Encrypt Database=False;Jet OLEDB:Don't Copy Locale on Compact=False;Jet OLEDB:Compact Without Replica Repair=False;Jet OLEDB:SFP=False;Jet OLEDB:Support Complex Data=False;Jet OLEDB:Bypass UserInfo Validation=False;Jet OLEDB:Limited DB Caching=False;Jet OLEDB:Bypass ChoiceField Validation=False
Cnn Source J:\Stuff\Project Resources\PivotTables\20190523__PSG_BLOCK.xls
--------------------------------------------------