通过 C# 运行 Excel 宏：从一个工作簿在另一个工作簿上运行宏？

Running an Excel Macro via C#: Run a macro from one workbook on another?

提问by kirbycope

I am looking to run a macro, let's call it Macro01 from WorkSheet01 on WorkSheet02.

我想运行一个宏，让我们从 WorkSheet02 上的 WorkSheet01 中将其称为 Macro01。

Using Microsoft.Office.Interop.Excel NamespaceI have opened a WorkSheet01.

使用Microsoft.Office.Interop.Excel 命名空间我打开了一个 WorkSheet01。

public void Main_CodedStep()
    {
        // Object for missing (or optional) arguments.
        object oMissing = System.Reflection.Missing.Value;

        // Create an instance of Microsoft Excel
        Excel.ApplicationClass oExcel = new Excel.ApplicationClass();

        // Make it visible
        oExcel.Visible = true;

        // Open Worksheet01.xlsm
        Excel.Workbooks oBooks = oExcel.Workbooks;
        Excel._Workbook oBook = null;
        oBook = oBooks.Open("C:\Users\Admin\Documents\Worksheet01.xlsm", oMissing, oMissing,
            oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, 
            oMissing, oMissing, oMissing, oMissing, oMissing, oMissing);
    }

I then use an automated script to pull a report. This report is opened via IE's download prompt and not the Interop.

然后我使用自动脚本来提取报告。此报告是通过 IE 的下载提示而不是互操作打开的。

The problem comes when I try to run the macro via C# (I made another new Excel.ApplicationClass(); only so it compiled, I believe this is one of my missteps.)

当我尝试通过 C# 运行宏时，问题就出现了（我制作了另一个新 Excel.ApplicationClass()；只有这样它才能编译，我相信这是我的失误之一。）

public void FirstMacro_CodedStep()
    {
        // Create an instance of Microsoft Excel
        Excel.ApplicationClass oExcel = new Excel.ApplicationClass();
        Console.WriteLine("ApplicationClass: " + oExcel);

        // Run the macro, "First_Macro"
        RunMacro(oExcel, new Object[]{"Worksheet01.xlsm!First_Macro"});

        //Garbage collection
        GC.Collect();
    }

    private void RunMacro(object oApp, object[] oRunArgs)
    {
        oApp.GetType().InvokeMember("Run", System.Reflection.BindingFlags.Default | System.Reflection.BindingFlags.InvokeMethod, null, oApp, oRunArgs);
    }

When this method runs it runs the macro from Worksheet01 on Worksheet01 instead of Worksheet02. Also it was looking for the worksheet in My Documents so I moved it over to see what would happen.

当此方法运行时，它会在 Worksheet01 而不是 Worksheet02 上运行来自 Worksheet01 的宏。它还在“我的文档”中寻找工作表，因此我将其移过来看看会发生什么。

Recap:

回顾：

1. Open Worksheet01
2. Via scripting get and open a report (Worksheet02) from MSIE
3. Run Macro01 from Worksheet01 on Worksheet02

1. 打开工作表01
2. 通过脚本从 MSIE 获取并打开报告 (Worksheet02)
3. 从 Worksheet02 上的 Worksheet01 运行 Macro01

Resources:

资源：

http://support.microsoft.com/kb/306683

http://support.microsoft.com/kb/306683

http://msdn.microsoft.com/en-us/library/microsoft.office.interop.excel.aspx

http://msdn.microsoft.com/en-us/library/microsoft.office.interop.excel.aspx

For those who would like to try it add this to your using directives:

对于那些想尝试的人，请将其添加到您的 using 指令中：

using System.Reflection;
using Microsoft.Office.Core; //Added to Project Settings' References from C:\Program Files (x86)\Microsoft Visual Studio 10.0\Visual Studio Tools for Office\PIA\Office14 - "office"
using Excel = Microsoft.Office.Interop.Excel; //Added to Project Settings' References from C:\Program Files (x86)\Microsoft Visual Studio 10.0\Visual Studio Tools for Office\PIA\Office14 - "Microsoft.Office.Interop.Excel"

采纳答案by kirbycope

I found a solution that I'd like to share. First, I removed the bit where I opened Worksheet01. I then had my automated script save the .CSV to My Documents. I then used the code I had to open Worksheet01 to open the downloaded file. The key thing at this point is that Worksheet01 is in the Documents folder with Worksheet02. Lastly I used the code to run the macro from Worksheet01, which runs on Worksheet02.

我找到了一个我想分享的解决方案。首先，我删除了打开 Worksheet01 的部分。然后我让我的自动脚本将 .CSV 保存到我的文档。然后我使用我必须打开 Worksheet01 的代码来打开下载的文件。此时的关键是 Worksheet01 与 Worksheet02 位于 Documents 文件夹中。最后，我使用代码从 Worksheet01 运行宏，该宏在 Worksheet02 上运行。

    public void WebTest_CodedStep()
    {
        // Object for missing (or optional) arguments.
        object oMissing = System.Reflection.Missing.Value;

        // Create an instance of Microsoft Excel
        Excel.ApplicationClass oExcel = new Excel.ApplicationClass();

        // Make it visible
        oExcel.Visible = true;

        // Define Workbooks
        Excel.Workbooks oBooks = oExcel.Workbooks;
        Excel._Workbook oBook = null;

        // Get the file path
        string path = System.Environment.GetFolderPath(Environment.SpecialFolder.MyDocuments);
        path = path + "\Worksheet02.csv";

        //Open the file, using the 'path' variable
        oBook = oBooks.Open(path, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing,  oMissing, oMissing, oMissing, oMissing, oMissing, oMissing);

        // Run the macro, "First_Macro"
        RunMacro(oExcel, new Object[]{"Worksheet01.xlsm!First_Macro"});

        // Quit Excel and clean up.
        oBook.Close(false, oMissing, oMissing);
        System.Runtime.InteropServices.Marshal.ReleaseComObject (oBook);
        oBook = null;
        System.Runtime.InteropServices.Marshal.ReleaseComObject (oBooks);
        oBooks = null;
        oExcel.Quit();
        System.Runtime.InteropServices.Marshal.ReleaseComObject (oExcel);
        oExcel = null;

        //Garbage collection
        GC.Collect();
    }

    private void RunMacro(object oApp, object[] oRunArgs)
    {
        oApp.GetType().InvokeMember("Run", System.Reflection.BindingFlags.Default | System.Reflection.BindingFlags.InvokeMethod, null, oApp, oRunArgs);
    }

回答by Jeremy Thompson

I ran this C# VSTO code to invoke a VBA Macro, this is the syntax I use:

我运行此 C# VSTO 代码来调用 VBA 宏，这是我使用的语法：

this.Application.Run("mymacro");

Edit:

编辑：

Macros are Workbook wide, perhaps you need to make Sheet2 the active worksheet before running the macro, eg:

宏是工作簿范围的，也许您需要在运行宏之前使 Sheet2 成为活动工作表，例如：

foreach (Worksheet worksheet in workbook.Sheets.ComLinq<Worksheet>())
{
    if (worksheet.Name == "Sheet2") worksheet.Activate();
}