Anyone help with this error in changing data source of pivots

[Job]

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

 

[Job]

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]

Ok I've read a few things that let me understand the 255 character
limitation on passing strings, therefore you have to pass an array. I've
used both of these;

Public Function StringToArray(Query As String) As Variant

Const StrLen = 127
Dim NumElems As Integer
Dim Temp() As String
Dim i As Integer

On Error GoTo Err_handle

NumElems = (Len(Query) / StrLen) + 1
ReDim Temp(1 To NumElems) As String
For i = 1 To NumElems

Temp(i) = Mid(Query, ((i - 1) * StrLen) + 1, StrLen)
Next i
StringToArray = Temp
Exit Function
Err_handle:

MsgBox "error"
Resume
End Function
Public Function SplitString(ByVal strCommandText As String) As Variant
Dim varCommandText() As Variant
Dim i As Long

ReDim varCommandText(0 To Len(strCommandText) \ 255) ' note: it is not 256
For i = 0 To UBound(varCommandText)
varCommandText(i) = Mid(strCommandText, i * 255 + 1, 255)
Next
SplitString = varCommandText
End Function

And still I get the application error as if the variable is not set. the
pt.pivotcache.commandtext (original) does return the variable which is 307
char long the new one I want to pass is also in the 300's in terms of
length, however, even passing the variable as an array i.e. ptc.CommandText
=StringToArray(newSQL) or ptc.CommandText =SplitString(newSQL) gives the
same error.

Has anyone ever had this issue? Any ideas? Will I need to recreate all of
the pivots on the fly?

 

[Job]

I still have not figured this out. Is my question confusing? If anyone has
any ideas I would appreciate any feedback.

Cheers,

Job