CodeGuru Home VC++ / MFC / C++ .NET / C# Visual Basic VB Forums Developer.com
Results 1 to 8 of 8
  1. #1
    Join Date
    Jan 2017
    Posts
    13

    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?

  2. #2
    VictorN's Avatar
    VictorN is offline Super Moderator Power Poster
    Join Date
    Jan 2003
    Location
    Hanover Germany
    Posts
    20,396

    Re: How to send an array of data to Excel using early binding?

    You should loop through the range cells and set the value for each cell.
    See example in How to automate Excel from MFC and Visual C++ 2005 or Visual C++ .NET to fill or obtain data in a range using arrays
    Victor Nijegorodov

  3. #3
    Join Date
    Jan 2017
    Posts
    13

    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!

  4. #4
    VictorN's Avatar
    VictorN is offline Super Moderator Power Poster
    Join Date
    Jan 2003
    Location
    Hanover Germany
    Posts
    20,396

    Re: How to send an array of data to Excel using early binding?

    Quote Originally Posted by garyflet1 View Post
    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!

    Quote Originally Posted by garyflet1 View Post
    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

  5. #5
    Join Date
    Jan 2017
    Posts
    13

    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.

  6. #6
    VictorN's Avatar
    VictorN is offline Super Moderator Power Poster
    Join Date
    Jan 2003
    Location
    Hanover Germany
    Posts
    20,396

    Re: How to send an array of data to Excel using early binding?

    Quote Originally Posted by garyflet1 View Post
    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

  7. #7
    Join Date
    Jan 2017
    Posts
    13

    Cool 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!

  8. #8
    VictorN's Avatar
    VictorN is offline Super Moderator Power Poster
    Join Date
    Jan 2003
    Location
    Hanover Germany
    Posts
    20,396

    Re: How to send an array of data to Excel using early binding?

    Quote Originally Posted by garyflet1 View Post
    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
  •  





Click Here to Expand Forum to Full Width

Featured