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