In my application I have to check if a excel-document contains vb-macros. So I've written the following method to check the excel-document:
internal static bool ExcelContainsMacros(string pathToExcelFile)
{
bool hasMacros = true;
Microsoft.Office.Interop.Excel._Application excelApplication = new Microsoft.Office.Interop.Excel.Application();
Microsoft.Office.Interop.Excel.Workbook workbooks = null;
try
{
object isReadonly = true;
workbooks = excelApplication.Workbooks.Open(
pathToExcelFile, missing, isReadonly, missing, missing, missing,
missing, missing, missing, missing, missing, missing,
missing, missing, missing);
hasMacros = workbooks.HasVBProject;
LogHasMacros(hasMacros);
}
catch (Exception exception)
{
LogError(exception);
}
finally
{
excelApplication.Workbooks.Close();
excelApplication.Quit();
}
return hasMacros;
}
With some excel-files I get a message from excel with a runtime-error 91.
91: Object variable or With block variable not set
I debugged it and just realized that the message appears at the call to excelApplication.Workbooks.Close();
. If I remove this line of code but the same excel-message appears at the call of excelApplication.Quit();
.
What do I have to do to close the excel-sheet correctly and prevent excel from showing this message?
Pertinent to your task, you may refer to the following refined code snippet, which utilizes .NET/C#, Microsoft.Office.Interop.Excel
object library and Runtime.InteropServices.Marshal
object:
internal static bool? ExcelContainsMacros(string pathToExcelFile)
{
bool? _hasMacro = null;
Microsoft.Office.Interop.Excel._Application _appExcel =
new Microsoft.Office.Interop.Excel.Application();
Microsoft.Office.Interop.Excel.Workbook _workbook = null;
try
{
_workbook = _appExcel.Workbooks.Open(pathToExcelFile, Type.Missing, true);
_hasMacro = _workbook.HasVBProject;
// close Excel workbook and quit Excel app
_workbook.Close(false, Type.Missing, Type.Missing);
_appExcel.Application.Quit(); // optional
_appExcel.Quit();
// release COM object from memory
System.Runtime.InteropServices.Marshal.FinalReleaseComObject(_appExcel);
_appExcel = null;
// optional: this Log function should be defined somewhere in your code
LogHasMacros(hasMacros);
return _hasMacro;
}
catch (Exception ex)
{
// optional: this Log function should be defined somewhere in your code
LogError(ex);
return null;
}
finally
{
if (_appExcel != null)
{
_appExcel.Quit();
// release COM object from memory
System.Runtime.InteropServices.Marshal.FinalReleaseComObject(_appExcel);
}
}
Notice nullable bool?
type: in this context, null
returned by function indicates the error (in other words, the result is undetermined), true
/false
values indicate presence/absence of any VBA macro in Excel file under test.
Hope this may help.