Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Anyone help with this error in changing data source of pivots
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Anyone please?
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" 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Anyone please?
I still have not figured this out. Is my question confusing? If anyone has
any ideas I would appreciate any feedback. Cheers, Job "Job" wrote in message ... 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" 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Changing Source Data in a Formula | Excel Discussion (Misc queries) | |||
Changing Data Source | Excel Worksheet Functions | |||
Same Data source for Multiple Pivots | Excel Discussion (Misc queries) | |||
How do stop Excel changing column widths when doing pivots? | Excel Discussion (Misc queries) | |||
Changing Data Source | Excel Programming |