Programmatic Execution of an Excel Macro on Remote Machine from a website
June 22, 2012 Leave a comment
I tried searching everywhere for a solution on this issue but found nothing. To give you an idea what I want to achieve here is the scenario.
We have a website where users generate and 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 (ie 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 like 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 such.
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.
