Using Interop.Excel to check if Excel file contains VBA Macros

Question

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?

you need to close the workbook you are working on. workbooks.Close() - also workbooks is a horrible name for your variable as it closely matches the Workbooks object. — Sorceri
– Sorceri, Commented Mar 29, 2016 at 14:00
You can just remove the erroneous line excelApplication.Workbooks.Close() : it should work. — Alexander Bell
– Alexander Bell, Commented Mar 29, 2016 at 14:02
If I remove the excelApplication.Quit(); doesn't this have any impact on the excel-sheet? — Tomtom
– Tomtom, Commented Mar 29, 2016 at 14:28
Please refer to my detailed answer containing the refined code snippet. Best regards, — Alexander Bell
– Alexander Bell, Commented Mar 29, 2016 at 14:45

Alexander Bell · Accepted Answer · 2016-03-29 16:09:05Z

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.

Collectives™ on Stack Overflow

Using Interop.Excel to check if Excel file contains VBA Macros

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related