如何从 Windows 服务运行 excel vba 代码

How to run excel vba code from a Windows Service

提问by user1948787

I want to run some excel vba code from my Windows Service. The Service is using a fileSystemWatcher to monitor a directory for an xml file to be added. Once a file is added the content of the xml file is deserialized into object properties. At this stage I want to open an excel file and pass these values in to certain cells and run vba code from that work book. I have this working perfectly from a windows forms application but I cant get it working from my Windows Service application. I attached a debugger to the application to try and see what was happening but there were no errors thrown and all the steps completed successfully. I know a Windows Service does not support opening MS Office files as there is issues with interacting with the UI and the User permissions it has. But I am looking for a work around any way to be able to get this vba code running from a service. I am using Windows 7 Home Premium 32 bit and I have my Account for my service set to LocalSystem.This is the code that I am using:

我想从我的 Windows 服务运行一些 excel vba 代码。该服务正在使用 fileSystemWatcher 来监视要添加的 xml 文件的目录。添加文件后，xml 文件的内容将反序列化为对象属性。在这个阶段，我想打开一个 excel 文件并将这些值传递给某些单元格并运行该工作簿中的 vba 代码。我在 Windows 窗体应用程序中完美地运行了这个，但我无法从我的 Windows 服务应用程序中得到它。我将调试器附加到应用程序以尝试查看发生了什么，但没有抛出错误并且所有步骤都成功完成。我知道 Windows 服务不支持打开 MS Office 文件，因为与 UI 交互和它拥有的用户权限存在问题。但是我正在寻找一种能够从服务中运行此 vba 代码的方法。我使用的是 Windows 7 Home Premium 32 位，并且我的服务帐户设置为 LocalSystem。这是我正在使用的代码：

    private void FSWatcherTest_Created(object sender, System.IO.FileSystemEventArgs e)
    {
            Trade t;
            XmlSerializer serializer;
            XmlReader reader;
            XmlWriter writer;

            string filePath = @"C:\Inbox\TradeInfo.xml";


            serializer = new XmlSerializer(typeof(Trade));
            reader = XmlReader.Create(filePath);
            t = (Trade)serializer.Deserialize(reader);
            reader.Close();



            string path=@"C:\Windows\System32\config\systemprofile\Desktop\TwsDde.xls";

            oXL = new Microsoft.Office.Interop.Excel.Application();

            oXL.Visible = true;

            oXL.DisplayAlerts = false;

            mWorkBook = oXL.Workbooks.Open(path,2, false, 5, "", "", true,Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "", true, false, 0, true,false, false);

            //Get all the sheets in the workbook

            mWorkSheets = mWorkBook.Worksheets;

            //Get the allready exists sheet

            mWSheet1=(Microsoft.Office.Interop.Excel.Worksheet)mWorkSheets.get_Item("Basic Orders");

            // Microsoft.Office.Interop.Excel.Range range= mWSheet1.UsedRange;

            mWSheet1.Cells[12, 1] = "GE";
            mWSheet1.Cells[12, 2] = "STK";
            mWSheet1.Cells[12, 7] = "SMART";
            mWSheet1.Cells[12, 9] = "USD";
            mWSheet1.Cells[12, 12] = "Buy";
            mWSheet1.Cells[12, 13] = "100";
            mWSheet1.Cells[12, 14] = "MKT";

            Excel.Range range;
            Excel.Range row;

            range = mWSheet1.get_Range("A12", "O12");
            range.EntireRow.Select();

            oXL.Run("TwsDde.xls!Sheet2.placeOrder");
        }

Any help would be greatly appreciated. Or alternative ways of doing the same thing i.e running a windows form that contains this code maybe?

任何帮助将不胜感激。或者做同样事情的替代方法，即运行包含此代码的 Windows 窗体？

回答by David Heffernan

What you are attempting to do is not supported:

不支持您尝试执行的操作：

Microsoft does not currently recommend, and does not support, Automation of Microsoft Office applications from any unattended, non-interactive client application or component (including ASP, ASP.NET, DCOM, and NT Services), because Office may exhibit unstable behavior and/or deadlock when Office is run in this environment.

Microsoft 当前不建议也不支持从任何无人参与的非交互式客户端应用程序或组件（包括 ASP、ASP.NET、DCOM 和 NT 服务）自动化 Microsoft Office 应用程序，因为 Office 可能表现出不稳定的行为和/或在此环境中运行 Office 时出现死锁。

You can automate Excel if the process is run in an interactive desktop. Attempts to automate Excel from a non-interactive desktop (e.g. from a session) are not supported and fail.

如果该过程在交互式桌面中运行，您可以自动化 Excel。不支持从非交互式桌面（例如从会话）自动化 Excel 的尝试并且失败。

回答by user1948787

Hi I managed to get this working in a kind of round about way. I basically followed this tutorial and code on launch an interactive process from a serviceto allow the service to run as SYSTEM user but to adopt the session ID for the first logged on user and give the user Administrative privileges to run interactive processes such as GUI applications. To open excel and run a macro through a Windows service I first wrote all the code to do this in a Windows Forms application and tested it worked there first. I then changed the executable and it worked perfectly. I hope this helps someone else with the same problem.

嗨，我设法以一种全面的方式进行了这项工作。我基本上遵循本教程和代码从服务启动交互式进程，以允许服务以 SYSTEM 用户身份运行，但采用第一个登录用户的会话 ID，并授予用户管理权限以运行交互式进程，例如 GUI 应用程序. 为了打开 excel 并通过 Windows 服务运行宏，我首先在 Windows 窗体应用程序中编写了所有代码来执行此操作，并首先测试它在那里工作。然后我更改了可执行文件，它运行良好。我希望这可以帮助其他有同样问题的人。

回答by user2080095

The Excel is working nice under user service without "desktop interacting" enabled.
First, The standard ways to creating Excel COM not working under service. You need launch Excel.exe process from you program - use ShellExecuteEx(recomended way) or CreateProcess(not recomended) to do it.
Secondstage: Attach to Excel COM Application object using GetActiveObject. Now you can correctly access to all Excel COM interfaces.
Also in several operating systems the Excel may be work incorrectly crash under LocalSystem Account - to correct this manually create 2 folders: C:\Windows\system32\config\systemprofile\desktop
C:\Windows\SysWow64\config\systemprofile\desktop Other bug under service without desctop interacting the Excel in first time ask the user credentials and freeze in memory. You can disable it in registry or interface (see Excel help). Other information about launching processes is there.

Excel 在用户服务下运行良好，无需启用“桌面交互”。
首先，创建 Excel COM 的标准方法不在服务下工作。您需要从您的程序中启动 Excel.exe 进程 - 使用 ShellExecuteEx（推荐方式）或 CreateProcess（不推荐）来执行此操作。
第二阶段：使用 GetActiveObject 附加到 Excel COM 应用程序对象。现在您可以正确访问所有 Excel COM 接口。
同样在几个操作系统中，Excel 可能会在 LocalSystem 帐户下错误地工作 - 要更正此问题，请手动创建 2 个文件夹：C:\Windows\system32\config\systemprofile\desktop
C:\Windows\SysWow64\config\systemprofile\desktop 服务下的其他错误，没有在第一次询问用户凭据并冻结在内存中没有 desctop 与 Excel 交互的情况下。您可以在注册表或界面中禁用它（请参阅 Excel 帮助）。那里有关于启动进程的其他信息。