Attribute VB_Name = "PublishPivotInfo"
Sub Run_PPI()
'Remember, file will be saved in the C: root directory as PPI.log in text format.
    Call PPI(True, True)
End Sub
Sub Run_PQI()
'Remember, file will be saved in the C: root directory as PPI.log in text format.
    Call PQI(True, True, True)
End Sub
Sub PPI(Optional ShowConnection As Boolean = False, Optional ShowMeAll As Boolean = False)
'PublishPivotInfo
'by Toby Erkson
'Lists each worksheet (even Hidden ones), the pivot table(s) name in it, and the pivot table(s) CommandText.
'Optionally, the Source Type and Connection can be output.

Dim WSheet As Worksheet, iIndex As Integer, TableCount As Integer, strVisible As String, strConnection
Dim iSourceData As Integer, strSourceData As String, strSource As String
Dim TotalPivots As Integer, TotalSheets As Integer, WorkBookName As String

TotalPivots = 0: TotalSheets = 0
WorkBookName = ThisWorkbook.Name  'Get the name of the workbook

Open "C:\PPI.log" For Output As #1

On Error Resume Next
Debug.Print "-- START --"
Print #1, "-- START --"
Print #1, "Workbook name is: " & WorkBookName
For Each WSheet In Worksheets  'Get each worksheet in the Worksheets collection (in the current workbook)
    TotalSheets = TotalSheets + 1
    Debug.Print "s";  'Informational: processing sheet.
    TableCount = WSheet.PivotTables.Count  'Count the number of pivot tables in worksheet
    strVisible = ""
    If Not (WSheet.Visible) Then strVisible = " {Hidden}"  'If the worksheet is NOT visible let user know
    For iIndex = 1 To TableCount  'Loop thru each pivot table
        TotalPivots = TotalPivots + 1
        Debug.Print "p";  'Informational: processing a pivot table
        strConnection = "": strSourceData = ""
        If ShowConnection Then
            strConnection = WSheet.PivotTables(iIndex).PivotCache.Connection  'Get connection string
            iSourceData = WSheet.PivotTables(iIndex).PivotCache.SourceType  'Determine data source type
            Select Case iSourceData  'Translates the number to Excel constant
                Case 1
                    strSourceData = "xlDatabase"
                Case 2
                    strSourceData = "xlExternal"
                Case 3
                    strSourceData = "xlConsolidation"
                Case 4
                    strSourceData = "xlScenario"
                Case -4148
                    strSourceData = "xlPivotTable"
            End Select
            strSource = "SourceType = " & strSourceData & " """ & strConnection & """"
        End If
        'Prints tab name and pivot table name in Immediate window in the VBA Editor
        Print #1, "Worksheet name is: " & WSheet.Name & strVisible
        Print #1, "Pivot table name is: " & WSheet.PivotTables(iIndex).Name
        If ShowConnection Then Print #1, strSource
        Print #1, WSheet.PivotTables(iIndex).PivotCache.CommandText  'String used to pull data
        Print #1, "Cache Index= " & WSheet.PivotTables(iIndex).CacheIndex
        If ShowMeAll Then
            With WSheet.PivotTables(iIndex).PivotCache
                Print #1, "ADOConnection= " & .ADOConnection
                Print #1, "BackgroundQuery= " & .BackgroundQuery
                Print #1, "IsConnected= " & .IsConnected
                Print #1, "LocalConnection= " & .LocalConnection
                Print #1, "MaintainConnection= " & .MaintainConnection
                Print #1, "MemoryUsed= " & .MemoryUsed
                Print #1, "Parent= " & .Parent
                Print #1, "Recordset= " & .Recordset
                Print #1, "OLAP= " & .OLAP
                Print #1, "OptimizeCache= " & .OptimizeCache
                Print #1, "QueryType= " & .QueryType
                Print #1, "RecordCount= " & .RecordCount
                Print #1, "RefreshDate= " & .RefreshDate
                Print #1, "RefreshName= " & .RefreshName
                Print #1, "RefreshOnFileOpen= " & .RefreshOnFileOpen
                Print #1, "RefreshPeriod= " & .RefreshPeriod
                Print #1, "RobustConnect= " & .RobustConnect
                Print #1, "SavePassword= " & .SavePassword
                Print #1, "SourceConnectionFile= " & .SourceConnectionFile
                Print #1, "SourceData= " & .SourceData
                Print #1, "SourceDataFile= " & .SourceDataFile
                Print #1, "UseLocalConnection= " & .UseLocalConnection
            End With
            Else
                Print #1, Chr(13)
        End If
        Print #1, Chr(13)  'Carrige return to put spacing between paragraphs
    Next
Next
On Error GoTo 0
Print #1, "-- END --"
Close #1
Debug.Print ""
Debug.Print TotalSheets & " sheets with " & TotalPivots & " pivot table(s)."
Debug.Print "-- END --"
End Sub

Sub PQI(ToFile As Boolean, Optional ShowConnection As Boolean = False, Optional ShowMeAll As Boolean = False)
'PublishQuerytableInfo
'by Toby Erkson
'Lists each worksheet (even Hidden ones), the query table(s) name in it, and the query table(s) CommandText.
'Optionally, the Source Type and Connection can be output.

Dim WSheet As Worksheet, iIndex As Integer, TableCount As Integer, strVisible As String, strConnection
Dim strSource As String, tempval As Long, TotalQueries As Integer, TotalSheets As Integer, WorkBookName As String

Open "C:\PQI.log" For Output As #1
TotalQueries = 0: TotalSheets = 0
WorkBookName = ThisWorkbook.Name  'Get the name of the workbook

On Error Resume Next

Debug.Print "-- START --"
Print #1, "-- START --"
Print #1, "Workbook name is: " & WorkBookName
For Each WSheet In Worksheets  'Get eash worksheet in the Worksheets collection (in the current workbook)
    TotalSheets = TotalSheets + 1
    Debug.Print "s";  'Informational: processing sheet.
    TableCount = WSheet.QueryTables.Count  'Count the number of pivot tables in worksheet
    strVisible = ""
    If Not (WSheet.Visible) Then strVisible = " {Hidden}"  'If the worksheet is NOT visible let user know
    For iIndex = 1 To TableCount  'Loop thru each pivot table
        TotalQueries = TotalQueries + 1
        Debug.Print "q";  'Informational: processing a query table.
        strConnection = ""
        If ShowConnection Then
            strConnection = WSheet.QueryTables(iIndex).Connection  'Get connection string
        End If
        'Prints tab name and pivot table name in Immediate window in the VBA Editor
        'Print #1, WSheet.Name & strVisible & " -> " & WSheet.QueryTables(iIndex).Name
        Print #1, "Worksheet name is: " & WSheet.Name & strVisible
        Print #1, "Query table name is: " & WSheet.QueryTables(iIndex).Name
        If ShowConnection Then Print #1, strConnection
        Print #1, "CommandText= " & WSheet.QueryTables(iIndex).CommandText  'String used to pull data
        If ShowMeAll Then
            With WSheet.QueryTables(iIndex)
                Print #1, "AdjustColumnWidth= " & .AdjustColumnWidth
                Print #1, "ADOConnection= " & .ADOConnection
                Print #1, "BackgroundQuery= " & .BackgroundQuery
                tempval = .CommandType
                Select Case tempval
                    Case 1
                        Print #1, "CommandType= 1=xlCmdCube"
                    Case 2
                        Print #1, "CommandType= 2=xlCmdSql"
                    Case 3
                        Print #1, "CommandType= 3=xlCmdTable"
                    Case 4
                        Print #1, "CommandType= 4=xlCmdDefault"
                    Case Else
                        Print #1, "QueryType= " & tempval
                End Select
                Print #1, "Destination= " & .Destination
                Print #1, "EnableEditing= " & .EnableEditing
                Print #1, "EnableRefresh= " & .EnableRefresh
                Print #1, "FieldNames= " & .FieldNames
                Print #1, "FillAdjacentFormulas= " & .FillAdjacentFormulas
                Print #1, "IsConnected= " & .IsConnected
                Print #1, "LocalConnection= " & .LocalConnection
                Print #1, "MaintainConnection= " & .MaintainConnection
                Print #1, "Parameters= " & .Parameters.Count
                Print #1, "PreserveColumnInfo= " & .PreserveColumnInfo
                Print #1, "Recordset= " & .Recordset
                Print #1, "OLAP= " & .OLAP
                Print #1, "OptimizeCache= " & .OptimizeCache
                tempval = .QueryType
                Select Case tempval
                    Case 1
                        Print #1, "QueryType= 1=xlODBCQuery"
                    Case 2
                        Print #1, "QueryType= 2=xlDAORecordSet"
                    Case 4
                        Print #1, "QueryType= 4=xlWebQuery"
                    Case 5
                        Print #1, "QueryType= 5=xlOLEDBQuery"
                    Case 6
                        Print #1, "QueryType= 6=xlTextImport"
                    Case 7
                        Print #1, "QueryType= 3=xlADORecordset"
                    Case Else
                        Print #1, "QueryType= " & tempval
                End Select
                Print #1, "RecordCount= " & .RecordCount
                Print #1, "RefreshDate= " & .RefreshDate
                Print #1, "RefreshName= " & .RefreshName
                Print #1, "RefreshOnFileOpen= " & .RefreshOnFileOpen
                Print #1, "RefreshPeriod= " & .RefreshPeriod
                Print #1, "RobustConnect= " & .RobustConnect
                Print #1, "SavePassword= " & .SavePassword
                Print #1, "SourceConnectionFile= " & .SourceConnectionFile
                Print #1, "SourceData= " & .SourceData
                Print #1, "SourceDataFile= " & .SourceDataFile
                Print #1, "UseLocalConnection= " & .UseLocalConnection
            End With
            Else
                Print #1, Chr(13)
        End If
        Print #1, Chr(13)  'Carrige return to put spacing between paragraphs
    Next
Next
On Error GoTo 0
Print #1, "-- END --"
Close #1
Debug.Print ""
Debug.Print TotalSheets & " sheets with " & TotalQueries & " query table(s)."
Debug.Print "-- END --"
End Sub