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()
{
Excel = new Application
{
DisplayAlerts = false,
ScreenUpdating = false,
Visible = false
};
Workbook = Excel.Workbooks.Open(@"C:\{YourTemplateName}.xlt");
DoStuff();
ExecuteMacros();
Workbook.SaveAs(@"C:\{YourExportedFileName}.xls", XlFileFormat.xlExcel8);
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)
{
macroExists = false;
}
if (!macroExists)
{
return;
}
var moduleCode = new StringBuilder();
moduleCode.AppendLine("Public Sub LaunchMacro()");
moduleCode.AppendLine(string.Format("{0}.{1}", moduleName, macroName));
moduleCode.AppendLine("End Sub");
var workbookMainModule = Workbook.VBProject.VBComponents.Item("ThisWorkbook");
workbookMainModule.CodeModule.AddFromString(moduleCode.ToString());
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()
{
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();
Excel = new Application
{
DisplayAlerts = false,
ScreenUpdating = false,
Visible = false
};
Workbook = Excel.Workbooks.Open(@"C:\{YourTemplateName}.xlt");
DoStuff();
ExecuteMacros();
Workbook.SaveAs(@"C:\{YourExportedFileName}.xls", XlFileFormat.xlExcel8);
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