none
Automation COM API error of Excel 2010 RRS feed

  • Question

  • Hi all,

    I am testing following source code for aging test of my application.

    But, when it repeats the loop more than 20,000 times, it shows  following popup dialog.

    (Title : Microsoft Visual Basic for applications, Contents : Unpredictable error, exit)

    Could you guess the reason? Is there any way to avoid this?

    ============= source code ===============

    while(1){

    objAppExcel.CreateDispatch(L"Excel.Application")

    objAppExcel.put_DisplayAlerts(FALSE);

    objWorkBooks = objAppExcel.get_Workbooks();

    bkThis = objWorkBooks.Open(strExcelFileName, 
    VOptional,  // UpdateLinks, 
    VOptional,  // ReadOnly, 
    VOptional,  // Format, 
    VOptional,  // Password, 
    VOptional,  // WriteResPassword, 
    VTrue,  // IgnoreReadOnlyRecommended, 
    VOptional,  // Origin, 
    VOptional,  // Delimiter, 
    VOptional,  // Editable, 
    VOptional,  // Notify, 
    VOptional,  // Converter, 
    VOptional,  // AddToMru, 
    VOptional,  // Local, 
    VOptional  // CorruptLoad, 
    );

    // SaveAsPDF
    bkThis.ExportAsFixedFormat((long)0, COleVariant(L"d:\\storage\\agingExcel.pdf"), VQuality, VTrue, VTrue, VFrom, VTo, VFalse, VOptional);

    objAppExcel.put_DisplayAlerts(FALSE);
    objAppExcel.Quit();
    objAppExcel.ReleaseDispatch();
    }

    Thanks,

    Kim

    Monday, February 9, 2015 2:02 AM

Answers

  • Hi Kim,

    According to the code you post, you were using C++, BUT I’m a little confused that the error was popped form VBA, so I suspect the error is popped from the Excel Application instead of COM Interface.

    >> Any way, it fails when it open a file. (bkThis = objWorkBooks.Open.......)

    I’m not able to guess the root cause of this issue based on the information your post. Is there any VBA code in the workbook you were opening (especially in the Workbook_Open event)?

    Could you please provide us a sample spreadsheet and sample project to help us reproduce this issue?

    And I hope this KB will give you some help to troubleshoot this issue.

    # Error Trapping with Visual Basic for Applications

    Regards,

    Jeffrey


    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.

    • Proposed as answer by L.HlModerator Monday, February 23, 2015 2:10 AM
    • Marked as answer by L.HlModerator Tuesday, February 24, 2015 5:22 AM
    Wednesday, February 11, 2015 2:02 AM
    Moderator

All replies

  • Hello Kim,

    What line of code generate the error?

    Anyway, I'd suggest creating an instance of the Excel Application outside of the loop. Inside you can just open the required files and save it in the appropriate format. When all operations are done, you can call the Quit method.

    Monday, February 9, 2015 10:24 AM
  • Hello Eugene,

    Thanks for your reply.

    My original program are using JNI and thread.

    In thread and JNI function, I have to create instance of Excel, whenever Java calls JNI function of my application DLL.

    If I reuse the instance as class variable, creation fails in the case of JNI and thread.

    Any way, it fails when it open a file. (bkThis = objWorkBooks.Open.......)

    It throws exception during it shows VB error popup dialog.

    Tuesday, February 10, 2015 5:18 AM
  • Hi Kim,

    According to the code you post, you were using C++, BUT I’m a little confused that the error was popped form VBA, so I suspect the error is popped from the Excel Application instead of COM Interface.

    >> Any way, it fails when it open a file. (bkThis = objWorkBooks.Open.......)

    I’m not able to guess the root cause of this issue based on the information your post. Is there any VBA code in the workbook you were opening (especially in the Workbook_Open event)?

    Could you please provide us a sample spreadsheet and sample project to help us reproduce this issue?

    And I hope this KB will give you some help to troubleshoot this issue.

    # Error Trapping with Visual Basic for Applications

    Regards,

    Jeffrey


    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.

    • Proposed as answer by L.HlModerator Monday, February 23, 2015 2:10 AM
    • Marked as answer by L.HlModerator Tuesday, February 24, 2015 5:22 AM
    Wednesday, February 11, 2015 2:02 AM
    Moderator