我有一个C#Interop方法,该方法打开Excel文件,运行宏,保存并关闭它。
private void DoExcelMacro(string fileName, string macroName)
{
Microsoft.Office.Interop.Excel.Application excel = new Microsoft.Office.Interop.Excel.Application();
Microsoft.Office.Interop.Excel.Workbook workbook;
workbook = excel.Workbooks.Open(fileName);
excel.DisplayAlerts = false;
excel.Run(macroName);
Worksheet ws = workbook.ActiveSheet;
workbook.SaveAs(fileName);
excel.DisplayAlerts = true;
ws = null;
workbook.Close();
workbook = null;
excel = null;
GC.Collect();
}
它效果很好,除非宏中存在错误,例如:
Public Sub MacroEntry()
Dim test AS Integer
test = "A"
End Sub
关闭DisplayAlerts
行不通。如果我在弹出窗口上单击"结束",我会在C#应用中收到错误。
问题1:有没有办法抑制错误弹出窗口,但让错误落入C#App?
或
我可以修改Excel宏以捕获错误
Public macroError As ErrObject
Public Sub MacroEntry()
On Error GoTo ErrorHandler
Dim test As Integer
test = "A"
ErrorHandler:
Set macroError = Err
Exit Sub
End Sub
问题2:如何从C#Interop对象读取Excel变量macroError
?
如果可能的话,我更喜欢问题1。
如果您正在运行通常是sub(void返回)的宏,则更改为函数(Happy-Path返回"字符串"),但是通过捕获错误,您,您可以在C#中检查一个非空字符串。
Public Function MacroEntry() As String
On Error GoTo ErrorHandler
Dim test As Integer
test = "A"
Exit Function
ErrorHandler:
MacroEntry= Err.Description
Exit Function
End Function
C#:
string macroErrorReturn = excel.Run(macroName).ToString();
if (macroErrorReturn != "") {
//error occurred in macro
}