Catch Excel Macro Errors in C#
AsimHussain
I have a C# service which basically converts XLS (2003) files to interactive
HTML pages at runtime. In this service, I have three types of macros:
1) GenerateWorkSheet (converts a multi sheet XLS workbook to interactive HTML)
2) GeneratePivotChart (converts a pivot chart active XLS workbook to HTML)
2) GeneratePivotTable (converts a pivot table active XLS workbook to HTML)
Every thing works fine if the correct macro is executed with the correct
sheet (i.e. worksheet macro runs for excel file with one or more data sheets,
or pivot chart macro is run when the excel has the chart sheet activated
etc). However, if something else happens, it seems that the
oBook.Application.Run method gets stuck and does not return anything.
For example, I have an XLS file with a pivot chart as the active sheet.
Using C#, I create the following macro at run-time:
Sub SavePivotTable()
A_Sheet_Name = ActiveCell.Worksheet.Name
objectName = "Fifteen_min_stats_PT_11551"
Title = "Fifteen_min_stats"
SaveFilePath =
"C:\XLSConversion\Files\WebLoadTemplates\Fifteen_min_stats_PT.htm"
With ActiveWorkbook.PublishObjects.Add(xlSourcePivotTable, SaveFilePath,
A_Sheet_Name, "PivotTable1", xlHtmlList, objectName, Title)
.Publish (True)
.AutoRepublish = False
End With
End Sub
Technically, when I run this macro (In Excel, using VBA Editor) on a pivot
chart active XLS file, I get the following error:
Run Time Error '91' Object variable not set
This is fine, because I am trying to save a pivot chart as a pivot table.
However, I do not know how to catch this using the Excel service. When I run
the following method
oBook.Application.Run("SavePivotTable", oMissing, ....)
The method gets stuck and no exception is thrown, therefore, I am unable to
catch the errors returned by Excel. All of this results in the service to
hang up until I manually abort the EXCEL.EXE process on the computer (which
then finally results in an exception which in turn gets caught by the service
and things get back to normal).
I would appreciate if someone could please tell me how to catch these kind
of errors and why does the oBook.Application.Run method no throw any
exception.
Regards
Asim