在远程计算机编程执行Excel宏从一个网站 [英] Programmatic Execution Excel Macro on Remote Machine from a website
问题描述
我有一个网站，用户使用宏，当我尝试在我的它生成完美，内部运行Excel中宏本机上运行生成Excel报表。当我发布到服务器上，并在我登录在那里的同时（RDP公开会议），并尝试从一个浏览器，服务器之外它也运行如预期运行。当我在服务器（RDP），然后运行它的服务器以外的浏览器注销时，会出现的问题（即来自我的机器）的宏不运行而造成我的Excel。
这是我现在用的是code
公共类报告
{
保护工作簿簿{获得;组; }
受保护应用程序的Excel {获得;组; }
公共无效RunReport（）
{
//在服务器上启动Excel
Excel的=新的应用程序
{
DisplayAlerts =假，
ScreenUpdating =假，
可见=假
};
//将工作簿模板
工作簿= Excel.Workbooks.Open（@D：\ Book1.xlt）;
//执行宏生成报告，如果有的话
ExecuteMacros（）;
Workbook.SaveAs（@D：\ Ray'sTesting.xls，XlFileFormat.xlExcel8）;
QuitExcel（）;
}
私人无效QuitExcel（）
{
如果（练习册！= NULL）
{
Workbook.Close（假）;
对Marshal.ReleaseComObject（练习册）;
}
如果（EXCEL！= NULL）
{
Excel.Quit（）;
对Marshal.ReleaseComObject（EXCEL）;
}
}
私人无效ExecuteMacros（）
{
常量字符串legacyModuleName =模块1;
常量字符串legacyMacroName =myMacro;
布尔legacyMacroExists = FALSE;
尝试
{
VAR legacyMacroModule = Workbook.VBProject.VBComponents.Item（legacyModuleName）;
如果（legacyMacroModule！= NULL）
{
INT legacyMacroStartLine = legacyMacroModule codeModule.ProcStartLine [legacyMacroName，Microsoft.Vbe.Interop.vbext_ProcKind.vbext_pk_Proc]。
legacyMacroExists = legacyMacroStartLine＆GT; 0;
}
}
赶上（例外）
{
legacyMacroExists = FALSE;
}
如果（！legacyMacroExists）
{
返回;
}
// VBA $ C $下的动态宏调用CI2遗留宏
VAR模块code =新的StringBuilder（）;
模块code.AppendLine（公用Sub LaunchLegacyMacro（））;
模块code.AppendLine（的String.Format（{0} {1}，legacyModuleName，legacyMacroName））;
模块code.AppendLine（结束子）;
//添加动态宏放在ThisWorkbook模块
变种workbookMainModule = Workbook.VBProject.VBComponents.Item（的ThisWorkbook）;
。workbookMainModule codeModule.AddFromString（模块code.ToString（））;
//执行动态宏
Microsoft.VisualBasic.Interaction.CallByName（练习册，LaunchLegacyMacro，Microsoft.VisualBasic.CallType.Method，新的对象[] {}）;
}
}
我通过我们每次运行Excel宏时编辑注册表通过
得到了这个工作 私有静态无效ModifyExcelSecuritySettings（）
{
//确保我们有编程访问该项目运行宏
使用（VAR键= Microsoft.Win32.Registry.CurrentUser.OpenSubKey（@软件\微软\办公室\ 14.0 \ EXCEL \安全，真））
{
如果（关键！= NULL）
{
如果（（int）的key.GetValue（AccessVBOM，0）！= 1）
{
key.SetValue（AccessVBOM，1）;
}
key.Close（）;
}
}
}
所以，code应该是这样的。
公共无效RunReport（）
{
ModifyExcelSecuritySettings（）;
//在服务器上启动Excel
Excel的=新的应用程序
{
DisplayAlerts =假，
ScreenUpdating =假，
可见=假
};
.....
我还创建了一个博客文章的完整的解决方案，你可以在这里查看
<一个href="http://anyrest.word$p$pss.com/2012/06/22/programmatic-execution-excel-macro-on-remote-machine-from-a-website/" rel="nofollow">http://anyrest.word$p$pss.com/2012/06/22/programmatic-execution-excel-macro-on-remote-machine-from-a-website/
I 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 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(@"D:\Book1.xlt");
// Execute macros that generates the report, if any
ExecuteMacros();
Workbook.SaveAs(@"D:\Ray'sTesting.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 legacyModuleName = "Module1";
const string legacyMacroName = "myMacro";
bool legacyMacroExists = false;
try
{
var legacyMacroModule = Workbook.VBProject.VBComponents.Item(legacyModuleName);
if (legacyMacroModule != null)
{
int legacyMacroStartLine = legacyMacroModule.CodeModule.ProcStartLine[legacyMacroName, Microsoft.Vbe.Interop.vbext_ProcKind.vbext_pk_Proc];
legacyMacroExists = legacyMacroStartLine > 0;
}
}
catch (Exception)
{
legacyMacroExists = false;
}
if (!legacyMacroExists)
{
return;
}
// VBA code for the dynamic macro that calls the CI2 legacy macro
var moduleCode = new StringBuilder();
moduleCode.AppendLine("Public Sub LaunchLegacyMacro()");
moduleCode.AppendLine(string.Format("{0}.{1}", legacyModuleName, legacyMacroName));
moduleCode.AppendLine("End Sub");
// Add the dynamic macro to the ThisWorkbook module
var workbookMainModule = Workbook.VBProject.VBComponents.Item("ThisWorkbook");
workbookMainModule.CodeModule.AddFromString(moduleCode.ToString());
// Execute the dynamic macro
Microsoft.VisualBasic.Interaction.CallByName(Workbook, "LaunchLegacyMacro", Microsoft.VisualBasic.CallType.Method, new object[] { });
}
}
I got this working by editing the registry every time we run the excel macro by
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();
}
}
}
So the code should look like this
public void RunReport()
{
ModifyExcelSecuritySettings();
// Launch Excel on the server
Excel = new Application
{
DisplayAlerts = false,
ScreenUpdating = false,
Visible = false
};
.....
I also created a blog post for the full solution which you can view here
这篇关于在远程计算机编程执行Excel宏从一个网站的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
