PDA

View Full Version : [SOLVED] Data Source validation



TMacorke
07-28-2004, 06:36 AM
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
07-29-2004, 04:14 PM
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
08-19-2004, 08:22 AM
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
08-19-2004, 09:33 AM
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
08-19-2004, 01:51 PM
THAT IS WONDERFUL!!
Thank you so very much!!