Programmatically enable Excel macro in C#

Question

I have an Excel add-in, and I would like to do the following:

1. Check if the workbook has any macro.
2. Prompt user if he wants to enable the macro.
3. If yes, enable the macro for user.

Is there any way to enable a macro in C#?

Solution

This article is very helpful for you.

According to the article, I implemented this as a WinForm application so I used MessageBox to prompt user if he wants to enable the macro.

Before doing this, you have to check "Trust access to the VBA project object model" at [File]->[Options]->[Trust Center] in your "xlsm" workbook.

using VBA = Microsoft.Vbe.Interop;
using Excel = Microsoft.Office.Interop.Excel;

private void ReadExcel()
{
    string filePath = @"C:\temp\Macro.xlsm";

    Microsoft.Office.Interop.Excel.Application appExcel = null;
    Microsoft.Office.Interop.Excel.Workbooks workbooks = null;
    Microsoft.Office.Interop.Excel.Workbook workbook = null;

    object oMiss = System.Reflection.Missing.Value;

    appExcel = new Microsoft.Office.Interop.Excel.Application();
    appExcel.DisplayAlerts = true;
    appExcel.AutomationSecurity = Microsoft.Office.Core.MsoAutomationSecurity.msoAutomationSecurityByUI;
    // Make the excel visible
    appExcel.Visible = true;
    workbooks = appExcel.Workbooks;
    workbook = workbooks.Open(filePath, oMiss,
                              oMiss, oMiss,
                              oMiss, oMiss,
                              oMiss, oMiss,
                              oMiss, oMiss,
                              oMiss, oMiss,
                              oMiss, oMiss,
                              oMiss);

    if (workbook.HasVBProject)  // Has macros
    {
        try
        {
            // Show "Microsoft Excel Security Notice" prompt
            var project = workbook.VBProject;
        }
        catch (System.Runtime.InteropServices.COMException comex)
        {
            // Macro is enabled.
        }
    }

    workbook.Close(true, oMiss, oMiss);

    System.Runtime.InteropServices.Marshal.ReleaseComObject(workbook);
    workbook = null;
    System.Runtime.InteropServices.Marshal.ReleaseComObject(workbooks);
    workbooks = null;
    appExcel.Quit();
    System.Runtime.InteropServices.Marshal.ReleaseComObject(appExcel);
    appExcel = null;
}

Comments

[Dhinnesh Jeevan]

Hi jhmt, thanks for the code! Btw, instead of running the macro, I would just like to enable macros on the workbook. You know, like when you open a workbook, you have that yellow bar where you can click to enable macros. How can I do that?

[jhmt]

@DhinneshJeevan Then, we can shorten the code:). If you set Microsoft.Office.Interop.Excel.Application.AutomationSecurit‌​y property to Microsoft.Office.Core.MsoAutomationSecurity.msoAutomationSec‌​urityByUI, "Microsoft Excel Security Notice" dialog asks users if they enable macros or not when the program accesses to the workbook's VBProject. However, if users enable macros, System.Runtime.InteropServices.COMException will be thrown therefore you have to handle it.

[Dhinnesh Jeevan]

Nice! Now I get the pop-up when loading the workbook, and I can select Enable/Disable macro. However I realized that if I enable the macro, then save the file, I will still be prompted the next time I open the file. However if I click on the yellow bar once to enable the macro, then save the file, I will not be prompted the next time I open it.

[jhmt]

@DhinneshJeevan If you want the yellow bar to prompt you every time, you have to disable "Trusted Documents" for your Excel application. Go to [FILE]->[Options]->[Trust Center]->[Trust Center Settings]->[Trusted Documents] and tick "Disable Trusted Documents".