none
How to prevent Time out when Automate Excel via Visual C++? RRS feed

Answers

  • I used the code from the SO post in a VS2010 console application to automate 32-bit Excel 2010 in Win7 SP1.

    So far I have been unable to reproduce the error.  Excel saved the new workbook after tests of a 5 minute, 10 minute and a 15 minute wait.

    Right now I'm waiting for the 30 minute wait test to complete.

    Update -

    30 minute wait completed and workbook saved without error.


    • Edited by RLWA32 Wednesday, September 11, 2019 2:42 PM
    • Marked as answer by tempc Friday, September 13, 2019 10:30 PM
    Wednesday, September 11, 2019 2:13 PM
  • Both EADLL and EAApp are zipped and can be downloaded from:

    https://www.dropbox.com/s/6btgglnt9znadwp/EA.zip?dl=0

    I downloaded your sample and built the dialog-based app and the dll with VS2010.  As a general observation I would recommend building these projects using MFC shared dlls instead of the MFC static lib.

    In any event, my test of the downloaded sample did NOT generate a COleDispatchException after the 15 minute wait and the test workbook was successfully created.

    1. For the best of compatilibity, I am importing type library of Office 2003 to generate the classes. While the classes will be used to automate Office 2010.
    This had no adverse effect in my test using your code.
    2. The automation is performed in a DLL instead a normal Windows app.

    Calling the automation code from a dll instead of from an exe is not an important distinction.

    However, best practices would be to call OleInitialize and OleUninitialize from the exe.

    The sample code calls OleUninitialize BEFORE the destructor for the CApplication object runs to release the IDispatch interface pointer for the Excel Application object.  This is a coding error.

    I also suggest that you revise the declarations of the vTrue and vFalse variants to use the VARIANT_TRUE and VARIANT_FALSE values with a VT_BOOL data type.

    Finally, calling Sleep from any application that must pump messages will cause it to become unresponsive.  You might consider using a different technique (e.g., WM_TIMER) to implement the wait.


    • Marked as answer by tempc Friday, September 13, 2019 10:30 PM
    Thursday, September 12, 2019 10:43 AM
  • I have retested my codes on a computer with activated Office. The problem does not appear any more. So the problem is just caused by an un-activated version of Office. Need I report this as a bug to Microsoft?

    That's up to you. :)

    • Marked as answer by tempc Friday, September 13, 2019 10:32 PM
    Friday, September 13, 2019 8:44 AM

All replies

  • I used the code from the SO post in a VS2010 console application to automate 32-bit Excel 2010 in Win7 SP1.

    So far I have been unable to reproduce the error.  Excel saved the new workbook after tests of a 5 minute, 10 minute and a 15 minute wait.

    Right now I'm waiting for the 30 minute wait test to complete.

    Update -

    30 minute wait completed and workbook saved without error.


    • Edited by RLWA32 Wednesday, September 11, 2019 2:42 PM
    • Marked as answer by tempc Friday, September 13, 2019 10:30 PM
    Wednesday, September 11, 2019 2:13 PM
  • Hi, RLWA32,

    Thank you very much for your help and time.

    I re-check my codes. I think there may be 3 possible reasons that prevent the error from reoccur:

    1. For the best of compatilibity, I am importing type library of Office 2003 to generate the classes. While the classes will be used to automate Office 2010.

    2. The automation is performed in a DLL instead a normal Windows app.

    3. I forget to include the

    ::OleInitialize(NULL);

    and

    ::OleUninitialize();

    in the posted codes. But they exists in the acutal function.

    Therefore, to easily reproduce the error, I make the following two test projects in VS2008:

    1. EADLL, which is a MFC DLL. It contains a TestExcel function that will automate Excel 2010 and invoke SaveAs to save Excel file as .xlsx file format. If let the sleep time to 15 mins, then an exception will occurs.

    2. EAApp, which is a Dialog-based App to invoke the DLL.

    I retest EADLL & EAApp on Windows 7 Ultimate(with SP1) 64bit version and confirm the error will occur when sleep 15 mins.

    Both EADLL and EAApp are zipped and can be downloaded from:

    https://www.dropbox.com/s/6btgglnt9znadwp/EA.zip?dl=0

    Thank you very much.

    Thursday, September 12, 2019 2:41 AM
  • Both EADLL and EAApp are zipped and can be downloaded from:

    https://www.dropbox.com/s/6btgglnt9znadwp/EA.zip?dl=0

    I downloaded your sample and built the dialog-based app and the dll with VS2010.  As a general observation I would recommend building these projects using MFC shared dlls instead of the MFC static lib.

    In any event, my test of the downloaded sample did NOT generate a COleDispatchException after the 15 minute wait and the test workbook was successfully created.

    1. For the best of compatilibity, I am importing type library of Office 2003 to generate the classes. While the classes will be used to automate Office 2010.
    This had no adverse effect in my test using your code.
    2. The automation is performed in a DLL instead a normal Windows app.

    Calling the automation code from a dll instead of from an exe is not an important distinction.

    However, best practices would be to call OleInitialize and OleUninitialize from the exe.

    The sample code calls OleUninitialize BEFORE the destructor for the CApplication object runs to release the IDispatch interface pointer for the Excel Application object.  This is a coding error.

    I also suggest that you revise the declarations of the vTrue and vFalse variants to use the VARIANT_TRUE and VARIANT_FALSE values with a VT_BOOL data type.

    Finally, calling Sleep from any application that must pump messages will cause it to become unresponsive.  You might consider using a different technique (e.g., WM_TIMER) to implement the wait.


    • Marked as answer by tempc Friday, September 13, 2019 10:30 PM
    Thursday, September 12, 2019 10:43 AM
  • Hi, RLWA32,

    It is really strange, since the error will occur in two of my computers easily.

    I try to use VS2010 to rebuild the project, then I run EAApp and will still get the exception.

    Am I missing something that will make the different result between yours and mine?

    Thanks

    Thursday, September 12, 2019 12:07 PM
  • I'm using a 32 bit version of Office 2010 and 32 bit code for the test application.

    What are you doing?

    Thursday, September 12, 2019 12:16 PM
  • Hi, RLWA32,

    I am using 32bit as well.

    Thursday, September 12, 2019 1:11 PM
  • Hi, RLWA32,

    One possible reason is that the Office 2010 on two computers are all not activated yet. Tomorrow I will try to find a computer with Office activated to see if that is the reason of the problem.

    Thursday, September 12, 2019 1:26 PM
  • I'm using a 32 bit version of Office 2010 and 32 bit code for the test application.

    What are you doing?

    I have retested my codes on a computer with activated Office. The problem does not appear any more. So the problem is just caused by an un-activated version of Office. Need I report this as a bug to Microsoft?
    Friday, September 13, 2019 12:19 AM
  • I have retested my codes on a computer with activated Office. The problem does not appear any more. So the problem is just caused by an un-activated version of Office. Need I report this as a bug to Microsoft?

    That's up to you. :)

    • Marked as answer by tempc Friday, September 13, 2019 10:32 PM
    Friday, September 13, 2019 8:44 AM
  • I have retested my codes on a computer with activated Office. The problem does not appear any more. So the problem is just caused by an un-activated version of Office. Need I report this as a bug to Microsoft?

    That's up to you. :)

    OK. I see. Thank you very much for all your time, efforts and helps.
    Friday, September 13, 2019 10:32 PM