Board index » cppbuilder » Variant array, GetElement, PutElement, Excel

Variant array, GetElement, PutElement, Excel


2005-01-11 10:46:34 AM
cppbuilder67
Hello:
The following code is not working in BCB 3.0 but BigStew has confirmed that
it works in BCB6.0.
Here I am trying to get value of Excel Range Object in an Variant Array and
then modify element values and then write back. But didn't work.
So I tried to just get the range value and write without modifying element
values. Still I get the following error.
raised exception class EOleSysError with message 'Bad variable type'.
The code is as below. (I have commented the array value modifying part of
the code.)
XLRange=XLWorkSheet.OlePropertyGet((Variant)"Range",(Variant)"A1",(Variant)"H10");
Variant V = XLRange.OlePropertyGet("Value");
/*
int XLRows = V.ArrayHighBound(1);
int XLCols = V.ArrayHighBound(2);
for(int i=1;i<=XLRows;i++)
for(int j=1;j<=XLCols;j++)
V.PutElement( StringGrid1->Cells[j][i-1],i,j);
*/
XLRange.OlePropertySet("Value",V);
V= Unassigned;
Any idea what is happeing?
Or is this a bug fixed in Later versions?
Please Help.
Thanks,
Sudesh
 
 

Re:Variant array, GetElement, PutElement, Excel

"Sudesh" < XXXX@XXXXX.COM >wrote in message
Quote

XLRange=XLWorkSheet.OlePropertyGet((Variant)"Range",(Variant)"A1",(Variant)"
H10");
You are castint char* strings as Variants. I have noticed in some versions
that Variant can't handle that conversion properly. It interprets the
pointers as booleans instead, thus the type of the Variant is VT_BOOL when
it should be VT_BSTR instead. As such, you should cast the strings to
either AnsiString or wchar_t* before casting to Variant:
XLRange = XLWorkSheet.OlePropertyGet("Range", (Variant)AnsiString("A1"),
(Variant)AnsiString("H10"));
or:
XLRange = XLWorkSheet.OlePropertyGet("Range", (Variant) L"A1", (Variant)
L"H10");
Gambit
 

Re:Variant array, GetElement, PutElement, Excel

Thanks Remy,
Let me try this now. Hope it works.
Sudesh
"Remy Lebeau (TeamB)" < XXXX@XXXXX.COM >wrote in message
Quote

"Sudesh" < XXXX@XXXXX.COM >wrote in message
news: XXXX@XXXXX.COM ...

>
XLRange=XLWorkSheet.OlePropertyGet((Variant)"Range",(Variant)"A1",(Variant)"
H10");

You are castint char* strings as Variants. I have noticed in some
versions
that Variant can't handle that conversion properly. It interprets the
pointers as booleans instead, thus the type of the Variant is VT_BOOL when
it should be VT_BSTR instead. As such, you should cast the strings to
either AnsiString or wchar_t* before casting to Variant:

XLRange = XLWorkSheet.OlePropertyGet("Range",
(Variant)AnsiString("A1"),
(Variant)AnsiString("H10"));

or:

XLRange = XLWorkSheet.OlePropertyGet("Range", (Variant) L"A1",
(Variant)
L"H10");


Gambit


 

{smallsort}

Re:Variant array, GetElement, PutElement, Excel

No Luck. Gives same error.
What to do?
Thanks.
Sudesh
 

Re:Variant array, GetElement, PutElement, Excel

"Sudesh" < XXXX@XXXXX.COM >wrote in message
Quote
No Luck. Gives same error.
What to do?
First, you need to identify which line is actually producing the error. You
did not do that earlier.
Gambit
 

Re:Variant array, GetElement, PutElement, Excel

Sorry about it. I am able to get range, read cells into array V and use
V.PutElement to modify values.
But at next line I get error. The following line:
XLRange.OlePropertySet("Value",V);
Thanks,
Sudesh
Quote

First, you need to identify which line is actually producing the error.
You
did not do that earlier.
Gambit


 

Re:Variant array, GetElement, PutElement, Excel

No furter posts?
Sudesh
 

Re:Variant array, GetElement, PutElement, Excel

I'm clutching at straws a bit here, but what about wrapping a SAFEARRAY in a
TVariant?
#include "safearry.h"
TSafeArrayDim2 dim(9, 5);
TSafeArrayInt2 intArray(dim);
for (int i=0; i < intArray.BoundsLength[0]; ++i)
for (int j=0; j < intArray.BoundsLength[1]; ++j)
intArray[i][j] = i*10 + j;
TVariant cells;
ExcelWorksheetPtr WS = MyXL->Sheets->get_Item(TVariant(1));
RangePtr TempRange = WS->get_Range(TVariant("A1"), TVariant("E9"));
cells.SetSAFEARRAY(&intArray);
TempRange->set_Value(cells);
This works on Builder6, but who knows if BCB3 will be happy?
Stewart
"Sudesh" < XXXX@XXXXX.COM >wrote in message
Quote
No furter posts?
Sudesh


 

Re:Variant array, GetElement, PutElement, Excel

Thanks BigStew,
Do you know where is safearray declaration in BCB3? "safearray.h" is not
present anywhere.
is it in any other header file in BCB 3?
Sudesh
"BigStew" < XXXX@XXXXX.COM >wrote in message
Quote
I'm clutching at straws a bit here, but what about wrapping a SAFEARRAY in
a
TVariant?

#include "safearry.h"
 

Re:Variant array, GetElement, PutElement, Excel

"Sudesh" < XXXX@XXXXX.COM >wrote in message
Quote
Do you know where is safearray declaration in BCB3?
"safearray.h" is not present anywhere.
There is no SAFEARRAY wrapper class in BCB3. That was not added until BCB4.
Gambit
 

Re:Variant array, GetElement, PutElement, Excel

So now how do I go about my problem (discussed earlier in this thread)
Any suggestions??
Thanks,
Sudesh
Quote
There is no SAFEARRAY wrapper class in BCB3. That was not added until
BCB4.
Gambit


 

Re:Variant array, GetElement, PutElement, Excel

Sudesh, I've installed BCB3 and I can re-produce the problem.
The Variant returned in BCB6 when get_Value() is called is an array.
But in BCB3, the Variant returned is not an array, so something is clearly
wrong.
I'll have a play and try to find a solution.
Stewart
"Sudesh" < XXXX@XXXXX.COM >wrote in message
Quote
So now how do I go about my problem (discussed earlier in this thread)
Any suggestions??
Thanks,
Sudesh

>There is no SAFEARRAY wrapper class in BCB3. That was not added until
>BCB4.


>Gambit
>
>


 

Re:Variant array, GetElement, PutElement, Excel

"BigStew" < XXXX@XXXXX.COM >wrote in message
Quote
The Variant returned in BCB6 when get_Value() is called is
an array. But in BCB3, the Variant returned is not an array,
so something is clearly wrong.
Did you look at the VARIANT's vt member to find out exactly what type of
data is being returned?
Gambit
 

Re:Variant array, GetElement, PutElement, Excel

Yes. BCB3 returns an IDispatch pointer. BCB6 returns an array of Variants.
Stewart
"Remy Lebeau (TeamB)" < XXXX@XXXXX.COM >wrote in message
Quote
Did you look at the VARIANT's vt member to find out exactly what type of
data is being returned?
 

Re:Variant array, GetElement, PutElement, Excel

"BigStew" < XXXX@XXXXX.COM >wrote in message
Quote
Yes. BCB3 returns an IDispatch pointer. BCB6 returns an array of Variants.
Then you need to query the IDispatch for the data. That is easier said then
done, though. One thing you can try is to use the undocumented
DISPID_GET_SAFEARRAY property:
const DISPID DISPID_GET_SAFEARRAY = -2700L;
VARIANT V = ...;
if( (V.vt & VT_TYPEMASK) == VT_DISPATCH )
{
IDispatch *pDisp;
if( V_ISBYREF(&V) )
pDisp = * V_DISPATCHREF(&V);
else
pDisp = V_DISPATCH(&V);
VARIANT vTmp;
DISPPARAMS dispparams = {NULL, NULL, 0, 0};
if( SUCCEEDED(pDisp->Invoke(DISPID_GET_SAFEARRAY, IID_NULL,
LOCALE_SYSTEM_DEFAULT, DISPATCH_PROPERTYGET,
&dispparams, &vTmp, NULL, NULL)) )
{
// use tVmp as needed...
}
}
If that does not work, then you can try invoking the IDispatch's "Value"
property, which will return any array data as VARIANT containing a
comma-delimited BSTR value. But that has a major problem - the individual
items are not quoted, so commas inside the items will be interpreted as item
delimiters. In other words:
"Testing"
"12345"
Would come through as "Testing,12345". However:
"Testing"
"12345"
"Hello, There"
Would come through as "Testing,12345,Hello,There"
Gambit