Limitation of PivotCache.CommandText property

[DudMc3]

The following discussion pertains to Microsoft Excel 2000.

There appears to be an undocumented limitation on the Let procedure behind
the CommandText property on the Excel.PivotCache class.

When getting the property, a String value is returned, not a Variant(String)
type as noted in the documentation. This string contains the SQL text in the
PivotCache. The string is not limited to 255 characters in length.

When setting (letting) the property, you can pass it a string of length less
than or equal to 255 characters. If you pass a longer string, VBA raises an
runtime error 1004.

There is a workaround: instead of passing a String type value, break apart
the String into an array of Variant(String). Excel will piece together the
elements of the array to assemble the SQL statement.

For example, assign the result of the following function to the CommandText
property, passing your SQL text as the argument to the 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

 

[bocachai]

Hi,

I've tried to use the function you mentioned but I still get the 1004
error if I do it through VB6

pt.PivotCache.CommandText = SplitString(NewSQL)



*** Sent via Developersdex http://www.developersdex.com ***

 

[keepITcool]

What's the problem with assigning a string to commandtext,
afaik it doesn't need to be an array.

When it is an array you must make sure that all parts of the
array CAN be 'joined' with a vbNullString.

Assuming the SplitString is a custom function (it definetly is not a
vb6 keyword) I think the problem lies in that function.

You must make sure that the elements of the array end or start with
spaces.


A quick test in VBA (xlXP):

Sub foo()
Dim pc As Excel.PivotCache
Set pc = ActiveWorkbook.PivotCaches(1)

Dim sCmd As String
Dim vCmd as variant

sCmd = pc.CommandText

'This works
pc.CommandText = sCmd

'This works because Join(vCmd," ") s/b equal to sCmd
vCmd = Split(sCmd, " ")
pc.CommandText = Join(vCmd, " ")

'This may very well give an error..
'as internally it will be joined as join(vcmd,vbnullstring)
pc.CommandText = vCmd



End Sub


--
keepITcool
| www.XLsupport.com | keepITcool chello nl | amsterdam


bocachai wrote :

 

[bocachai]

Using either functions SplitString or StringToArray (defined bellow)
works fine in VBA. And definetly the problem is not there. It returns an
1004 error in VB6 even if I just assign the query as a very short
string.

pt.PivotCache.sql = "SELECT * from Orders"
or
pt.PivotCache.CommandText = "SELECT * from Orders"


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

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

*** Sent via Developersdex http://www.developersdex.com ***

 

[keepITcool]

ok..

1004 indicates a variable is not set...

using pt.pivotcache should work provided the pt was recently refreshed
and yields a a valid pivotcache object

IF "data is not saved with the pivottable"
then the pivotcache is (re)created when the workbook is opened.

it could be that when you open it through automation
you need to refresh the cache first. (or create the pivottable from
scratch)

Note:
per vba help using SQL is deprecated, you should use commandtext.

the format of commandtext is related to CommandType
Have you set that prior to assigning the commandstring?

Also the commandtext should not conflict with the connection
and the connection must exist, e.g. the table must exist in the
connected database.

during testing you could set a variable of type Excel.PivotCache
and perhaps see what's wrong in the Locals Window in VBE
by examining the properties of the variable.

BTW and FWIW:
SplitString makes a 0 based array of type VARIANT
with 255 char strings...

StringToArray creates a 1 based array of type STRING
with 127 char strings...


if you email me I'll help you look.
sender uses ROT13, else contruct email from sig below.


--
keepITcool
| www.XLsupport.com | keepITcool chello nl | amsterdam


bocachai wrote :