I've tried to use the .Sourcedata as I've seen that as a possible solution
and pass the connection string and new sql as an array, but still get the
error. Is there a specific reference that I need? Strange I can change the
connection string but not the command text or sql... any ideas appreciated.

Cheers,
Job

"Job" wrote in message
...
The error is application-defined or object-defined error. Not sure why
this doesn't work. I can get the ptc.Connection to change, but not the
.SQL statement.

Sub ChangeSource()
'Declare our variables.
Dim ptc As PivotCache, oldSrv, newSrv,oldSQL ,newSQL As String

sFilename = Application.GetOpenFilename(, , "Input the name of the
new server or file path which you want the Pivot Table to point to.")
'They have cancelled.
If sFilename = "False" Then Exit Sub
newSrv = "ODBC;DSN=MS Access Database;DBQ=" & sFilename & ";DefaultDir=" &
sFilename & ";DriverId=25;FIL=MS Access;MaxBufferSize=2048;PageTimeout=5;"

For Each ws In ActiveWorkbook.Worksheets
For Each pt In ws.PivotTables
oldSrv = pt.PivotCache.Connection

'Replace the ODBC information of whatever PivotTable
'is currently active.
Set ptc = pt.PivotCache
oldSQL = Left(Mid(ptc.Sql, Application.Find("`", ptc.Sql) + 1, 255),
Application.Find("`", Mid(ptc.Sql, Application.Find("`", ptc.Sql) + 1,
255)) - 1)
newSQL = Application.Substitute(ptc.Sql, oldSQL, sFilename)
ptc.Connection = Application.Substitute(ptc.Connection, oldSrv, newSrv)
' ==== This is error line
ptc.Sql = Application.Substitute(ptc.Sql, ptc.Sql, newSQL)
'====
Next pt
Next ws
End Sub