none
Run excel automation macro sometimes failed with excel 2013 RRS feed

  • Question

  • Hi,

    I'm trying to run an excel macro in my c++ code.

    This macro was created with excel 2010 and worked well there.

    the macro is :

    Sub macro()
    '
        ActiveChart.ClearToMatchStyle
        ActiveChart.ChartStyle = 268
    End Sub

    Since I updated my office to 2013, run the macro would throw an exception in my code, BUT NOT ALWAYS.

    The c++ code is :

     --------------------------------------------------------------------------------------------------             

     _Workbook objMacroBook;
    try
    {
      objMacroBook = m_objBooks.Open(strMacroFile, // LPCTSTR Filename, 
      g_covOptional, // const VARIANT& UpdateLinks, 
      g_covOptional, // const VARIANT& ReadOnly, 
      g_covOptional, // const VARIANT& Format, 
      g_covOptional, // const VARIANT& Password, 
      g_covOptional, // const VARIANT& WriteResPassword, 
      g_covOptional, // const VARIANT& IgnoreReadOnlyRecommended, 
      g_covOptional, // const VARIANT& Origin, 
      g_covOptional, // const VARIANT& Delimiter, 
      g_covOptional, // const VARIANT& Editable, 
      g_covOptional, // const VARIANT& Notify, 
      g_covOptional, // const VARIANT& Converter, 
      g_covOptional);// const VARIANT& AddToMru);

    m_objBook.Activate();
    m_objChart.Activate();

    m_objApp.Run(COleVariant(strMacro), //const VARIANT& Macro, 
    g_covOptional, // const VARIANT& Arg1, 
    g_covOptional, // const VARIANT& Arg2, 
    g_covOptional, // const VARIANT& Arg3, 
    g_covOptional, // const VARIANT& Arg4, 
    g_covOptional, // const VARIANT& Arg5, 
    g_covOptional, // const VARIANT& Arg6, 
    g_covOptional, // const VARIANT& Arg7, 
    g_covOptional, // const VARIANT& Arg8, 
    g_covOptional, // const VARIANT& Arg9, 
    g_covOptional, // const VARIANT& Arg10, 
    g_covOptional, // const VARIANT& Arg11, 
    g_covOptional, // const VARIANT& Arg12, 
    g_covOptional, // const VARIANT& Arg13, 
    g_covOptional, // const VARIANT& Arg14, 
    g_covOptional, // const VARIANT& Arg15, 
    g_covOptional, // const VARIANT& Arg16, 
    g_covOptional, // const VARIANT& Arg17, 
    g_covOptional, // const VARIANT& Arg18, 
    g_covOptional, // const VARIANT& Arg19, 
    g_covOptional, // const VARIANT& Arg20, 
    g_covOptional, // const VARIANT& Arg21, 
    g_covOptional, // const VARIANT& Arg22, 
    g_covOptional, // const VARIANT& Arg23, 
    g_covOptional, // const VARIANT& Arg24, 
    g_covOptional, // const VARIANT& Arg25, 
    g_covOptional, // const VARIANT& Arg26, 
    g_covOptional, // const VARIANT& Arg27, 
    g_covOptional, // const VARIANT& Arg28, 
    g_covOptional, // const VARIANT& Arg29, 
    g_covOptional);// const VARIANT& Arg30);

    objMacroBook.Close(g_covFalse, // const VARIANT& SaveChanges, 
            g_covOptional, // const VARIANT& Filename, 
          g_covOptional);// const VARIANT& RouteWorkbook);
    }
    catch(CException* e)
    {
    TCHAR errmsg[1024];
    e->GetErrorMessage(errmsg, 1023);
    CString strMsg(_T("Failure running macro: ") + strMacroFile + _T(" : ") + strMacroName + _T(" : ") + errmsg);
    AfxMessageBox(strMsg);

    e->Delete();
    try
    {
    objMacroBook.Close(g_covFalse, // const VARIANT& SaveChanges, 
    g_covOptional, // const VARIANT& Filename, 
    g_covOptional);// const VARIANT& RouteWorkbook);
    }
    catch(CException* e)
    {
    e->Delete();
    }
    return FALSE;
    }

    ------------------------------------------------------------------------------------------------------------------

    I tested that the "ActiveChart" object in the macro is actually still NULL after i run "m_objChart.Activate();". But sometimes it's not.

    I add Sleep(500) after "m_objChart.Activate();" and the macro would work. But i think Sleep is not a good workaround.

    Is excel 2013 doing some extra work after call Activate()? Do we have any way to know that excel has finished its work?

    Any help would be great! Thanks in advance.

    Geoff


    • Edited by GgEeOo Monday, September 29, 2014 6:40 AM adjust the format
    Monday, September 29, 2014 6:35 AM

Answers

  • Thank you all for your help.

    The method in c++ codes is used to running excel macros. And all the macro files are recorded by the clients. So it is not possible to code  the macro into c++ code.

    I add the c++ codes below after I run "m_objChart.Activate();":

    ---------------------------------------------------------------------------------------------------------------------------------

    m_objChart.Activate();

    IDispatch* pDisPatch = NULL;
    while (pDisPatch == NULL)
    {
    #ifdef _WIN32
    ::Sleep(100);
    #else
    MSG msg;

    ::PeekMessage(&msg, NULL, NULL, NULL, PM_NOREMOVE);
    #endif
    pDisPatch = reinterpret_cast<IDispatch*>(m_objApp.GetActiveChart());
    }

    ---------------------------------------------------------------------------------------------------------------------------------

    This is a small change and it worked! Hope this way can help others who surfers from the same issue with me after updating excel to 2013.

    Thank you again.

    Geoff

    • Marked as answer by GgEeOo Wednesday, October 8, 2014 9:01 AM
    Wednesday, October 8, 2014 8:59 AM

All replies

  • Hello,

    I think you should avoid using active and activate at all times. Try to adress the chart by index or by name.

    With active and activate, you never know if the chart will stay the active one or will be active at all. User interaction or an other process could disrubt this.

    Best regards,

    Wouter

    Monday, September 29, 2014 11:41 AM
  • Hello,

    I think you should avoid using active and activate at all times. Try to adress the chart by index or by name.

    With active and activate, you never know if the chart will stay the active one or will be active at all. User interaction or an other process could disrubt this.

    Best regards,

    Wouter

    Thank you for your suggestion, Wouter. 

    The macro was recorded by others which I couldn't modify. They recorded the macro manually. "ActiveChart" object is used in that macro. You mean in c++ code side, we can also generate "ActiveChart" object without using the "m_objChart.Activate();" interface?

    Or do we have any other approach?

    Thanks again for your answer!

    Geoff

    Tuesday, September 30, 2014 2:10 AM
  • Hello,

    if the macro consists only of 2 lines, you could maybey implement it in you C++ code?

    My C++ is outdated ( haven't done any since high school ;) ). But I'll try: if your object m_objChart contains an absolute reference to the chart (I mean you adressed it by index or name),

    You could try:

    m_objChart.ClearToMatchStyle
    m_objChart.ChartStyle = 268

    (no need to activate the chart, so delete this line: m_objChart.activate() )

    Hope it helps


    • Edited by Wouter Defour Tuesday, September 30, 2014 7:23 AM forgot something
    Tuesday, September 30, 2014 7:21 AM
  • Hi Geoff,

    How do you get the chart (m_objChart)?

    I think what Wouter mean is that you need modify the macro code to get the chart by using name or index.

    More information, please refer to:

    # Chart Object (Excel)

    http://msdn.microsoft.com/en-us/library/office/ff194426(v=office.15).aspx

    There is a link about run office macros from C++ app that may benefit you:

    # How To Run Office Macros Using Automation From Visual C++ .NET

    https://support.microsoft.com/kb/306686?wa=wsignin1.0

    Best Regards

    Starain Chen


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Tuesday, September 30, 2014 7:38 AM
    Moderator
  • Thank you all for your help.

    The method in c++ codes is used to running excel macros. And all the macro files are recorded by the clients. So it is not possible to code  the macro into c++ code.

    I add the c++ codes below after I run "m_objChart.Activate();":

    ---------------------------------------------------------------------------------------------------------------------------------

    m_objChart.Activate();

    IDispatch* pDisPatch = NULL;
    while (pDisPatch == NULL)
    {
    #ifdef _WIN32
    ::Sleep(100);
    #else
    MSG msg;

    ::PeekMessage(&msg, NULL, NULL, NULL, PM_NOREMOVE);
    #endif
    pDisPatch = reinterpret_cast<IDispatch*>(m_objApp.GetActiveChart());
    }

    ---------------------------------------------------------------------------------------------------------------------------------

    This is a small change and it worked! Hope this way can help others who surfers from the same issue with me after updating excel to 2013.

    Thank you again.

    Geoff

    • Marked as answer by GgEeOo Wednesday, October 8, 2014 9:01 AM
    Wednesday, October 8, 2014 8:59 AM