-
March 21st, 2019, 05:06 PM
#1
How to send an array of data to Excel using early binding?
I've been trying to write "early binding" code to control Excel with Automation. I've made considerable progress but can't figure out how to write a SAFEARRAY of numbers to Excel. Here is some simplified code:
Code:
CComPtr<_Application> m_pXLApp;
CComPtr<Workbooks> m_pWorkBooks;
CComPtr<_Workbook> pBook;
CComPtr<Sheets> m_pSheets;
CComPtr<_Worksheet> pSheet;
CComPtr<Range> pRange;
CComPtr<IDispatch> pDisp;
COleSafeArray saRet; //holds the data
BSTR filename;
m_pXLApp.CoCreateInstance(L"Excel.Application", nullptr, CLSCTX_LOCAL_SERVER);
m_pXLApp->get_Workbooks(&m_pWorkBooks);
m_pWorkBooks->Open(filename, ...);
m_pWorkBooks->Add(vOptional, LOCALE_SYSTEM_DEFAULT, &pBook)
pBook->get_Sheets(&m_pSheets);
m_pSheets->get_Item(CComVariant(nXLWorksheetNo), &pDisp);
pDisp->QueryInterface(&pSheet);
pSheet->get_Range(vVar1, vVar2, &pRange);
pRange->SetValue(COleVariant(saRet));
This simplified code looks good except there is no SetValue function for pRange. In fact, pRange is very limited in the number of functions available. How do you send an array of data to Excel using early binding?
-
March 24th, 2019, 04:22 AM
#2
Re: How to send an array of data to Excel using early binding?
Victor Nijegorodov
-
March 25th, 2019, 02:12 PM
#3
Re: How to send an array of data to Excel using early binding?
Thank you, Victor for your quick reply. You have sent me an excellent reference on how to send an array to Excel using late binding. If you can find a similar reference for early binding, I would be very happy. Note the reference here on the difference between early and late binding: https://support.microsoft.com/en-us/...-in-automation
The reason I want to do early binding is that I need to get better performance, which is one of the advantages of early binding. With early binding I have to specify the object with an import statement:
Code:
#import "C:\Program Files (x86)\Microsoft Office\root\Office16\Excel.exe" /*named_guids*/ no_smart_pointers raw_interfaces_only raw_native_types auto_search
The helper on an MSDN site gave me a sample program which only writes one string to Excel not numbers:
CComPtr<_Worksheet> pSheet;
CComPtr<Range> pRange;
CComDispatchDriver pd;
hr = pSheet->get_Range(CComVariant(L"A1"), vOptional, &pRange);
pd = pRange;
CComVariant vA1(x);
hr = pd.PutPropertyByName(L"Value2", &vA1);
It writes "Value2" to the A1 cell. But unlike your reference, which shows how to send a SAFEARRAY "saRet" with the "range" function put_Value(covOptional,COleVariant(saRet)), neither pRange nor pd have a put_Value function. Unfortunately, the helper on MSDN quit responding to my queries.
I did notice that IRange has a put_Value function so I made an attempt with this code:
Code:
CComPtr<IRange> pIRange;
LPDISPATCH pDisp;
pRange->QueryInterface(&pDisp);
pDisp->QueryInterface(&pIRange);
pIRange->put_Value(COleVariant(saRet));
This code compiled! Unfortunately an exception was thrown at the put_Value function that cannot be ignored (causes a crash).
Anyway, to repeat, if you have any information on how to use early binding to send an array, I'd love to see it!
-
March 25th, 2019, 03:08 PM
#4
Re: How to send an array of data to Excel using early binding?
Originally Posted by garyflet1
Thank you, Victor for your quick reply. You have sent me an excellent reference on how to send an array to Excel using late binding. ...
You are welcome!
Originally Posted by garyflet1
Thank you, Victor for your quick reply. You have sent me an excellent reference on how to send an array to Excel using late binding. I
I did notice that IRange has a put_Value function so I made an attempt with this code:
Code:
CComPtr<IRange> pIRange;
LPDISPATCH pDisp;
pRange->QueryInterface(&pDisp);
pDisp->QueryInterface(&pIRange);
pIRange->put_Value(COleVariant(saRet));
This code compiled! Unfortunately an exception was thrown at the put_Value function that cannot be ignored (causes a crash).
Did you catch the exception? What was the code/description?
Victor Nijegorodov
-
March 26th, 2019, 01:29 PM
#5
Re: How to send an array of data to Excel using early binding?
Intellisense tells me that the put_Value parameters are like this:
Code:
IRange::put_Value(VARIANT RangeValueDataType, long lcid = 0L, VARIANT RHS = vtMissing)
So I'm assuming that the data goes in the last parameter.
Here is the new code:
Code:
CComPtr<IRange> pIRange;
LPDISPATCH pDisp;
pRange->QueryInterface(&pDisp);
pDisp->QueryInterface(&pIRange);
VARIANT vRangeValueDataType;
VariantInit(&vRangeValueDataType);
V_I8(&vRangeValueDataType) = xlRangeValueDefault;
pIRange->put_Value(vRangeValueDataType, 0, COleVariant(saRet));
When I run it, the assert is at the put_value line in my code, which reaches atlcomcli.h and the class CComPtrBase:
Code:
_NoAddRefReleaseOnCComPtr<T>* operator->() const throw()
{
ATLASSERT(p!=NULL);
return (_NoAddRefReleaseOnCComPtr<T>*)p;
}
So it ASSERTS at the ATLASSERT and crashes if I continue. It says: Access violation reading location 0x0000000000000000. I don't know how to make this work...Thanks for your efforts so far.
-
March 26th, 2019, 02:59 PM
#6
Re: How to send an array of data to Excel using early binding?
Originally Posted by garyflet1
Here is the new code:
Code:
CComPtr<IRange> pIRange;
LPDISPATCH pDisp;
pRange->QueryInterface(&pDisp);
pDisp->QueryInterface(&pIRange);
VARIANT vRangeValueDataType;
VariantInit(&vRangeValueDataType);
V_I8(&vRangeValueDataType) = xlRangeValueDefault;
pIRange->put_Value(vRangeValueDataType, 0, COleVariant(saRet));
When I run it, the assert is at the put_value line in my code, which reaches atlcomcli.h and the class CComPtrBase:
Code:
_NoAddRefReleaseOnCComPtr<T>* operator->() const throw()
{
ATLASSERT(p!=NULL);
return (_NoAddRefReleaseOnCComPtr<T>*)p;
}
So it ASSERTS at the ATLASSERT and crashes if I continue. It says: Access violation reading location 0x0000000000000000. I don't know how to make this work...Thanks for your efforts so far.
Well, I have no idea what argument p is and where it comes from, but it all looks you do something wrong with your call
Code:
pIRange->put_Value(vRangeValueDataType, 0, COleVariant(saRet));
BTW, why don't you implement a loop through the range cells to put value for each of them rather than trying to put something strange to the whloe range??
Victor Nijegorodov
-
March 28th, 2019, 12:42 PM
#7
Re: How to send an array of data to Excel using early binding?
Actually, I figured out how to do it. In my March 25 email, when I showed the other helper's example, I misinterpreted the "Value2" as a string being sent. Actually, it was the name of a "put" function, put_Value2(). So it works to put a SAFEARRY in there, using "Value" or Value2". Here is the code:
Code:
CComPtr<_Worksheet> pSheet;
CComPtr<Range> pRange;
CComDispatchDriver pd;
COleSafeArray saRet; //holds the data
CComPtr<_Worksheet> pSheet;
hr = pSheet->get_Range(CComVariant(L"A1"), vOptional, &pRange);
pd = pRange;
hr = pd.PutPropertyByName(L"Value2", (COleVariant)saRet);
//or pd.PutProperty(0x56c, (COleVariant)saRet);
//or pd.PutPropertyByName(L"Value", (COleVariant)saRet);
//or pd.PutProperty(6, (COleVariant)saRet);
Note the similarity in sending an entire array at once as Victor's link in his first reply showed. Thanks so much for your help, Victor!
-
March 28th, 2019, 02:01 PM
#8
Re: How to send an array of data to Excel using early binding?
Originally Posted by garyflet1
Actually, I figured out how to do it. In my March 25 email, when I showed the other helper's example, I misinterpreted the "Value2" as a string being sent. Actually, it was the name of a "put" function, put_Value2(). So it works to put a SAFEARRY in there, using "Value" or Value2". Here is the code:
Code:
CComPtr<_Worksheet> pSheet;
CComPtr<Range> pRange;
CComDispatchDriver pd;
COleSafeArray saRet; //holds the data
CComPtr<_Worksheet> pSheet;
hr = pSheet->get_Range(CComVariant(L"A1"), vOptional, &pRange);
pd = pRange;
hr = pd.PutPropertyByName(L"Value2", (COleVariant)saRet);
//or pd.PutProperty(0x56c, (COleVariant)saRet);
//or pd.PutPropertyByName(L"Value", (COleVariant)saRet);
//or pd.PutProperty(6, (COleVariant)saRet);
Note the similarity in sending an entire array at once as Victor's link in his first reply showed. Thanks so much for your help, Victor!
Wow! Great!
And you are welcome!
Victor Nijegorodov
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|