Programmatic Execution of an Excel Macro on Remote Machine from a Website

I tried searching everywhere for a solution on this issue but found nothing. To give you an idea about what I want to achieve, here is the scenario.

We have a website where users generate an Excel report using a macro. When I try to run it in my local machine, it generates perfectly and runs the macro inside the Excel. When I publish it into the server and at the same time I am logged in there (RDP open session) and try to run it from a browser outside that server, it is also running as expected. The problem occurs when I am logged off in the server (RDP), then run it in a browser outside the server (i.e., from my machine), the macro does not run but creates my Excel.

This is the code that I am using:

public class Report
{
    protected Workbook Workbook { get; set; }
    protected Application Excel { get; set; }

    public void RunReport()
    {
        // Launch Excel on the server
        Excel = new Application
        {
            DisplayAlerts = false,
            ScreenUpdating = false,
            Visible = false
        };

        // Load the workbook template  
        Workbook = Excel.Workbooks.Open(@"C:\{YourTemplateName}.xlt");

        // You your stuff here
        DoStuff();

        // Execute macros 
        ExecuteMacros();

        Workbook.SaveAs(@"C:\{YourExportedFileName}.xls", XlFileFormat.xlExcel8);

        // Properly Close Excel
        QuitExcel();
    }

    private void QuitExcel()
    {
        if (Workbook != null)
        {
            Workbook.Close(false);
            Marshal.ReleaseComObject(Workbook);
        }

        if (Excel != null)
        {
            Excel.Quit();
            Marshal.ReleaseComObject(Excel);
        }
    }    

    private void ExecuteMacros()
    {
        const string moduleName = "{YourModuleName}";
        const string macroName = "{YourMacroName}";

        bool macroExists = false;
        try
        {
            var macroModule = Workbook.VBProject.VBComponents.Item(moduleName);
            if (macroModule != null)
            {
                int macroStartLine = macroModule.CodeModule.ProcStartLine
                [macroName, Microsoft.Vbe.Interop.vbext_ProcKind.vbext_pk_Proc];
                macroExists = macroStartLine > 0;
            }
        }
        catch (Exception)
        {
            //no macro found
            macroExists = false;
        }

        if (!macroExists)
        {
            return;
        }

        // VBA code for the dynamic macro that calls 
        var moduleCode = new StringBuilder();
        moduleCode.AppendLine("Public Sub LaunchMacro()");
        moduleCode.AppendLine(string.Format("{0}.{1}", moduleName, macroName));
        moduleCode.AppendLine("End Sub");

        // Add the dynamic macro 
        var workbookMainModule = Workbook.VBProject.VBComponents.Item("ThisWorkbook");
        workbookMainModule.CodeModule.AddFromString(moduleCode.ToString());

        // Execute the dynamic macro
        Microsoft.VisualBasic.Interaction.CallByName
        (Workbook, "LaunchMacro", Microsoft.VisualBasic.CallType.Method, new object[] { });
    }
}

As explained, everything works properly until you log off to the server. Now after some investigation, we notice that the Trust access to the VBA project object model is turned off when you log out of the server which makes your macro useless as it would not run.

It was a bit of a search to come up with this conclusion but nice to know there was a solution and here it is.

Since we know that is being turned off when a user logs out, we have to create a method on our application to turn that on so we can run our macro happily and that is done through a registry setting as such.

private static void ModifyExcelSecuritySettings()
{
    // Make sure we have programmatic access to the project to run macros
    using (var key = Microsoft.Win32.Registry.CurrentUser.OpenSubKey
    (@"Software\Microsoft\Office\14.0\Excel\Security", true))
    {
        if (key != null)
        {
            if ((int)key.GetValue("AccessVBOM", 0) != 1)
            {
                key.SetValue("AccessVBOM", 1);
            }
            key.Close();
        }
    }
}

Just change the path depending on the Excel version you are using.

This is then used before you launch Excel on the server like this:

public void RunReport()
{
    ModifyExcelSecuritySettings();

    // Launch Excel on the server
    Excel = new Application
    {
        DisplayAlerts = false,
        ScreenUpdating = false,
        Visible = false
    };

    // Load the workbook template  
    Workbook = Excel.Workbooks.Open(@"C:\{YourTemplateName}.xlt");

    // You your stuff here
    DoStuff();

    // Execute macros 
    ExecuteMacros();

    Workbook.SaveAs(@"C:\{YourExportedFileName}.xls", XlFileFormat.xlExcel8);

    // Properly Close Excel
    QuitExcel();
}

BTW, for those interested, the site is deployed in IIS7 using Integrated Application Pool Identity and Passtrough Authentication / Identity Impersonation.

Filed under: CodeProject, Programming
Tagged: ASP.Net, C#, Excel, IIS, OLE Automation