Neil,
I think I've been working on the same problem. I'm connecting to SQL
server - but the following may provide the base for something that will work.
In a new sheet in a new workbook, I filled in the cells as follows:
A B
1 Old Object String DSN=olddb
2 New Object String DSN=newdb
In this workbook, I created the following macro (and ran it with the
workbook I wanted to change open). (Don't ask me to explain - I pilfered the
base of it from somewhere on the net for changing query tables, then spend a
couple of hours trying to work out what pivot table object allowed me to
manipulate a connection).
'------------------------------------------------------------
Sub ChangeConn()
Dim pt As PivotTable
Dim Wsh As Worksheet
Dim Wbk As Workbook
Dim Base
Dim oldString As String
Dim newString As String
Set Base = Range("A1")
oldString = Base.Offset(0, 1).Value 'B1
newString = Base.Offset(1, 1).Value 'B2
For Each Wbk In Workbooks
For Each Wsh In Wbk.Worksheets
For Each pt In Wsh.PivotTables
pt.PivotCache.Connection _
= Replace(pt.PivotCache.Connection, _
oldString, _
newString)
Next pt
Next Wsh
Next Wbk
End Sub
'-------------------------------------------------------------
Hope this helps.
Kim.
