Anyone help with this error in changing data source of pivots

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

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" <[email protected]> wrote in message
news:%[email protected]...
> 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
>

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" <[email protected]> wrote in message
news:%[email protected]...
> 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
>

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

Cheers,

Job

"Job" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
> news:%[email protected]...
>> 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
>>
>
>

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

Cheers,

Job

"Job" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
> news:%[email protected]...
>> 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
>>
>
>