 |
BorlandTalk.com
Borland discussion newsgroups
|
| View previous topic :: View next topic |
| Author |
Message |
Sudesh
Guest
|
 Posted: Tue Jan 11, 2005 2:46 am Post subject: Variant array, GetElement, PutElement, Excel |
 |
|
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
|
|
| Back to top |
|
 |
Remy Lebeau (TeamB)
Guest
|
| Posted: Tue Jan 11, 2005 4:28 am Post subject: Re: Variant array, GetElement, PutElement, Excel |
|
|
"Sudesh" <sudeshc (AT) vfemail (DOT) net> wrote
| 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
|
|
| Back to top |
|
|
Sudesh
Guest
|
| Posted: Tue Jan 11, 2005 5:54 am Post subject: Re: Variant array, GetElement, PutElement, Excel |
|
|
Thanks Remy,
Let me try this now. Hope it works.
Sudesh
"Remy Lebeau (TeamB)" <no.spam (AT) no (DOT) spam.com> wrote
| Quote: |
"Sudesh" <sudeshc (AT) vfemail (DOT) net> wrote in message
news:41e33e3a (AT) newsgroups (DOT) borland.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
|
|
|
| Back to top |
|
|
Sudesh
Guest
|
| Posted: Tue Jan 11, 2005 6:06 am Post subject: Re: Variant array, GetElement, PutElement, Excel |
|
|
No Luck. Gives same error.
What to do?
Thanks.
Sudesh
|
|
| Back to top |
|
|
Remy Lebeau (TeamB)
Guest
|
| Posted: Tue Jan 11, 2005 8:51 am Post subject: Re: Variant array, GetElement, PutElement, Excel |
|
|
"Sudesh" <sudeshc (AT) vfemail (DOT) net> wrote
| 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
|
|
| Back to top |
|
|
Sudesh
Guest
|
| Posted: Tue Jan 11, 2005 9:17 am Post subject: 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
|
|
|
| Back to top |
|
|
Sudesh
Guest
|
| Posted: Wed Jan 12, 2005 8:53 am Post subject: Re: Variant array, GetElement, PutElement, Excel |
|
|
No furter posts?
Sudesh
|
|
| Back to top |
|
|
BigStew
Guest
|
| Posted: Wed Jan 12, 2005 4:07 pm Post subject: 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" <sudeshc (AT) vfemail (DOT) net> wrote
| Quote: | No furter posts?
Sudesh
|
|
|
| Back to top |
|
|
Sudesh
Guest
|
| Posted: Thu Jan 13, 2005 2:27 am Post subject: 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" <Big_Stew (AT) talk21 (DOT) com> wrote
| Quote: | I'm clutching at straws a bit here, but what about wrapping a SAFEARRAY in
a
TVariant?
#include "safearry.h"
|
|
|
| Back to top |
|
|
Remy Lebeau (TeamB)
Guest
|
| Posted: Thu Jan 13, 2005 8:46 am Post subject: Re: Variant array, GetElement, PutElement, Excel |
|
|
"Sudesh" <sudeshc (AT) vfemail (DOT) net> wrote
| 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
|
|
| Back to top |
|
|
Sudesh
Guest
|
| Posted: Thu Jan 13, 2005 9:56 am Post subject: 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.
|
|
|
| Back to top |
|
|
BigStew
Guest
|
| Posted: Thu Jan 13, 2005 9:21 pm Post subject: 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" <sudeshc (AT) vfemail (DOT) net> wrote
| 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
|
|
|
| Back to top |
|
|
Remy Lebeau (TeamB)
Guest
|
| Posted: Thu Jan 13, 2005 10:26 pm Post subject: Re: Variant array, GetElement, PutElement, Excel |
|
|
"BigStew" <big_stew (AT) talk21 (DOT) com> wrote
| 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
|
|
| Back to top |
|
|
BigStew
Guest
|
| Posted: Thu Jan 13, 2005 10:45 pm Post subject: Re: Variant array, GetElement, PutElement, Excel |
|
|
Yes. BCB3 returns an IDispatch pointer. BCB6 returns an array of Variants.
Stewart
"Remy Lebeau (TeamB)" <no.spam (AT) no (DOT) spam.com> wrote
| Quote: | Did you look at the VARIANT's vt member to find out exactly what type of
data is being returned?
|
|
|
| Back to top |
|
|
Remy Lebeau (TeamB)
Guest
|
| Posted: Thu Jan 13, 2005 10:57 pm Post subject: Re: Variant array, GetElement, PutElement, Excel |
|
|
"BigStew" <big_stew (AT) talk21 (DOT) com> wrote
| 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
|
|
| Back to top |
|
|
|
|
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum
|
|
FAQ
Search
Memberlist
Usergroups
Register
Profile
Log in to check your private messages
Log in
