Data Source validation

[TMacorke]

I have a spreadsheet with 14 (or so) tabs that contain pivot tables from MSAccess database.
I need to make certain that these are all pulling data from the same database, hopefully without rebuilding them. The only way that I have found to determine the datasource is to go into each tab, into the Pivot Table wizard and view the SQL to determine the path and file name of the source data.
Is there any other way to determine what datasources are being used? Is it possible to do this for the entire document instead of each tab?

[tommy bak]

Hi TMacorke

If your pivottables all are directly from access datases you could try the following code.

Sub GetPivotConnection()
Dim ws As Worksheet
Dim pt As PivotTable
Dim pc As PivotCache
Dim temp As Variant
Dim msg As String
'go through each worksheet in the active workbook
For Each ws In ActiveWorkbook.Worksheets
'for each pivottable, get the pivotcache's connectionstring
For Each pt In ws.PivotTables
'take the databasestring out of the connectionstring
temp = Split(pt.PivotCache.Connection, ";")
msg = msg & ws.Name & "---" & pt.Name & "---" & temp(2) & vbCrLf
Next
Next
'display the worksheets, pivottables and their databases
MsgBox msg
End Sub

BR
Tommy Bak

[TMacorke]

THANK YOU!
That works GREAT....for the first 11 pivot table data sources...but stops there.
There are 7 more pivot tables in this file....?

[tommy bak]

Ok, I think you reached the max. number of characters i a messagebox.
This one makes a new sheet and places the information there.

Sub GetPivotConnection()
    Dim ws As Worksheet
    Dim wsNew As Worksheet
    Dim pt As PivotTable
    Dim pc As PivotCache
    Dim vtemp As Variant
    Dim lCnt As Long
'add a new worksheet
    On Error Resume Next
    Set wsNew = Worksheets("Connectionstrings")
    If Not wsNew Is Nothing Then
        wsNew.Cells.ClearContents
    Else
        Set wsNew = Worksheets.Add
        wsNew.Name = "Connectionstrings"
    End If
    On Error GoTo 0
'set header
    wsNew.Range("A1:C1") = Array("Worksheet", "Pivot name", "Connection")
    wsNew.Range("A1:C1").Font.Bold = True
     'loop through each worksheet in the active workbook
    For Each ws In ActiveWorkbook.Worksheets
         'for each pivottable, get the pivotcache's connectionstring
        For Each pt In ws.PivotTables
             'take the databasestring out of the connectionstring
            vtemp = Split(pt.PivotCache.Connection, ";")
             'display the worksheets, pivottables and their databases in new worksheet
            lCnt = lCnt + 1
            wsNew.Range("A1:C1").Offset(lCnt, 0) = Array(ws.Name, pt.Name, vtemp(2))
        Next
    Next
    wsNew.Columns("A:C").AutoFit
End Sub

BR
Tommy Bak

[TMacorke]

THAT IS WONDERFUL!!
Thank you so very much!!