Change to SQL of Pivot Table on shared data
 

If I write a couple of line of code to change pivot table datasources (we
are changing backends) it works perfectly UNTIL more than one pivot table
looks at the same data source.

This is easily recreatable by adding a pivot table on, say, northwind.mdb.
Run the code below (ignore the connection) then adding a second pivot table
and running it again.

In fact, the error is recreateable just by setting it to the existing SQL!

Has anybody got round this successfully or knows what I am doing wrong?

Cheers!
Simon


'Change pivot table data connections
Dim stSQL as String
Dim
Dim PC As PivotCache
For Each PC In ActiveWorkbook.PivotCaches
'PC.Connection = SplitToArray(PC.Connection) 'No need to change for
testing
stSQL = PC.Sql
stSQL = stSQL 'No need to even change the SQL for testing
PC.Sql = stSQL
MsgBox "Done"
Next

Function Replacestr(TextIn, SearchStr, Replacement, CompMode As Integer)
'Trad - poss Getz
Dim WorkText As String, Pointer As Integer
If IsNull(TextIn) Then
Replacestr = Null
Else
WorkText = TextIn
Pointer = InStr(1, WorkText, SearchStr, CompMode)
Do While Pointer 0
WorkText = Left(WorkText, Pointer - 1) & Replacement & Mid(WorkText,
Pointer + Len(SearchStr))
Pointer = InStr(Pointer + Len(Replacement), WorkText, SearchStr,
CompMode)
Loop
Replacestr = WorkText
End If
End Function

Function SplitToArray(ST As String, Lump As Integer)
'Stolen from newsgroup by way of google
' break a long string up into an array with each element of size Lump
' don't bother if string is not longer than Lump
Dim A()
Dim I As Integer
If Len(ST) <= Lump Then
SplitToArray = ST
Else
ReDim A(1 To Len(ST) \ Lump + 1)
For I = 1 To Len(ST) \ Lump + 1
A(I) = Mid(ST, 1 + (I - 1) * Lump, Lump)
Next
SplitToArray = A()
End If
End Function

Change to SQL of Pivot Table on shared data
 

Simon,

I use Define Name to give the data range a name. Than I have my Pivot
Table refer to the name as the data source.

It is easy to have code recreate name and change the Refers to: portion .
(In my use the names are self-expanding so that if the ranges increase or
decrease, the Pivot Tables adjust accordingly.)

If you are changing the actual source of the data, be sure that the headers
are the same.

--
sb
"Simon" wrote in message
...
If I write a couple of line of code to change pivot table datasources (we
are changing backends) it works perfectly UNTIL more than one pivot table
looks at the same data source.

This is easily recreatable by adding a pivot table on, say, northwind.mdb.
Run the code below (ignore the connection) then adding a second pivot
table
and running it again.

In fact, the error is recreateable just by setting it to the existing SQL!

Has anybody got round this successfully or knows what I am doing wrong?

Cheers!
Simon


'Change pivot table data connections
Dim stSQL as String
Dim
Dim PC As PivotCache
For Each PC In ActiveWorkbook.PivotCaches
'PC.Connection = SplitToArray(PC.Connection) 'No need to change
for
testing
stSQL = PC.Sql
stSQL = stSQL 'No need to even change the SQL for testing
PC.Sql = stSQL
MsgBox "Done"
Next

Function Replacestr(TextIn, SearchStr, Replacement, CompMode As Integer)
'Trad - poss Getz
Dim WorkText As String, Pointer As Integer
If IsNull(TextIn) Then
Replacestr = Null
Else
WorkText = TextIn
Pointer = InStr(1, WorkText, SearchStr, CompMode)
Do While Pointer 0
WorkText = Left(WorkText, Pointer - 1) & Replacement & Mid(WorkText,
Pointer + Len(SearchStr))
Pointer = InStr(Pointer + Len(Replacement), WorkText, SearchStr,
CompMode)
Loop
Replacestr = WorkText
End If
End Function

Function SplitToArray(ST As String, Lump As Integer)
'Stolen from newsgroup by way of google
' break a long string up into an array with each element of size Lump
' don't bother if string is not longer than Lump
Dim A()
Dim I As Integer
If Len(ST) <= Lump Then
SplitToArray = ST
Else
ReDim A(1 To Len(ST) \ Lump + 1)
For I = 1 To Len(ST) \ Lump + 1
A(I) = Mid(ST, 1 + (I - 1) * Lump, Lump)
Next
SplitToArray = A()
End If
End Function