none
Handling Excel Exception in C# RRS feed

  • Question

  • Hi,

    We have an excel add-in that allows users to create custom dashboards using Pivot tables. If a user opens an already existing excel file, the source code first refreshes the data connection with the SSAS server and displays the data and is presented with the pivot table field/area section to add further data columns to their sheet.

    When trying to load a huge lot of data, Excel shows the following error -

    "Data could not be retrieved from the external data source.

    Error message returned by the external data souce:

    Memory error: Allocation failure: Not enough storage is available to process this command."

    I know the cause of the error but I want to handle it properly in my source code ( C#/ VS 2010) and display a custom message to the user.

    Here is the problem:

    If the the user opens an existing file which has a lot of data columns, the source code is able to catch this exception, since we are forcing the connection refresh for the first time the excel sheet is loaded.

    But in the case where the initial data is less,the connection refresh is completed and then the user adds more columns from Pivot table field list, the above error when shown, is not caught in the source code. We have the try-catch blocks at the proper places.

    Are we doing something wrong? Is there anything that we can do to handle such exceptions?  

    Thanks and Regards.

    • Moved by Barry Wang Wednesday, August 13, 2014 7:52 AM
    Tuesday, August 12, 2014 7:35 AM

Answers

  • Hi Anon,

    >>I've tried adding custom handlers for some of the Excel application object events - SheetCalculate, SheetChange, AfterCalculate etc. but it didn't help either. 

    After the error, the execution control does not enter these events. 

    So, there is no way we can handle an exception that is thrown by Excel? <<

    No, I am afraid that we can't handle en exception which thrown by Excel application. Here is the statement about Exception Handling Statements:

    C# provides built-in support for handling anomalous situations, known as exceptions, which may occur during the execution of YOUR program. These exceptions are handled by code that is outside the normal flow of control.

    You also can get more detail about Exception Handling from link below:

    Exceptions and Exception Handling

    Hope it is hlepful.

    Best regards

    Fei


    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.


    Friday, August 15, 2014 3:38 AM
    Moderator

All replies

  • Hi Anon316,

    This case sounds related to Excel for developers and I've moved it to the right forum.

    Regards,



    Barry
    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.

    Wednesday, August 13, 2014 7:52 AM
  • Hi Anon,

    >>But in the case where the initial data is less,the connection refresh is completed and then the user adds more columns from Pivot table field list, the above error when shown, is not caught in the source code. We have the try-catch blocks at the proper places.<<

    Did this issue caused by the code or user? If the issue was caused by code, as far as I know, we can use try-catch to catch most of Exception except the exception which will cause the Excel application crashed. Would you mind sharing with us which line of code caused this exception?

    If this exception was caused by the operation of users, I think we can hardly to catch this exception, because this exception was thrown by Excel application.

    Hope it is helpful.

    Best regards

    Fei


    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.

    Thursday, August 14, 2014 2:33 AM
    Moderator
  • Hi Fei,

    The above error comes up whenever a user is trying to select a lot of columns in the Pivot table. The external database server, is running at 98% memory capacity and hence Excel reports this error. 

    I was hoping to catch this in the source code, rather than Excel handling it for me. Also, the Excel application does not crash, it just fails to load the selected columns. 

    I've tried adding custom handlers for some of the Excel application object events - SheetCalculate, SheetChange, AfterCalculate etc. but it didn't help either. 

    After the error, the execution control does not enter these events. 

    So, there is no way we can handle an exception that is being thrown by Excel? 

    Thanks and Regards.

    Thursday, August 14, 2014 6:41 AM
  • Hi Anon,

    >>I've tried adding custom handlers for some of the Excel application object events - SheetCalculate, SheetChange, AfterCalculate etc. but it didn't help either. 

    After the error, the execution control does not enter these events. 

    So, there is no way we can handle an exception that is thrown by Excel? <<

    No, I am afraid that we can't handle en exception which thrown by Excel application. Here is the statement about Exception Handling Statements:

    C# provides built-in support for handling anomalous situations, known as exceptions, which may occur during the execution of YOUR program. These exceptions are handled by code that is outside the normal flow of control.

    You also can get more detail about Exception Handling from link below:

    Exceptions and Exception Handling

    Hope it is hlepful.

    Best regards

    Fei


    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.


    Friday, August 15, 2014 3:38 AM
    Moderator
  • Thanks for your help Fei.
    Tuesday, August 19, 2014 4:04 AM