通过c#运行Excel宏:从一个工作簿在另一个工作簿上运行宏

本文关键字：工作簿 运行 一个 另一个 Excel 通过 | 更新日期: 2023-09-27 17:51:19

我希望运行一个宏，让我们将其称为WorkSheet01上WorkSheet02上的Macro01。

使用<<p> em> Microsoft.Office.Interop。Excel命名空间我已打开工作表01. 
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);
    }

然后我使用一个自动脚本来拉出一个报告。这个报告是通过IE的下载提示打开的，而不是通过Interop打开的。

问题来了，当我试图通过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);
    }

当此方法运行时，它在Worksheet01上而不是Worksheet02上运行来自Worksheet01的宏。它还在我的文档中寻找工作表，所以我把它移到别处看看会发生什么。

回顾:

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

资源:

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

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

对于那些想尝试它的人，将此添加到您的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"

通过c#运行Excel宏:从一个工作簿在另一个工作簿上运行宏

我发现了一个解决方案，我想分享。首先，我删除了我打开工作表01的位置。然后我让我的自动脚本将. 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);
    }

我运行了这个c# VSTO代码来调用VBA宏，这是我使用的语法:

this.Application.Run("mymacro");
编辑:

宏是工作簿宽度的，也许您需要在运行宏之前将Sheet2设置为活动工作表，例如:

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

    public void ExecuteMacro()
    {
    //    using Excel = Microsoft.Office.Interop.Excel;
    //    using System;
    //    using System.IO;
        string path = Environment.CurrentDirectory;
        string filePath = "";
        string[] fileEntries = Directory.GetFiles(".''Source");
        foreach (string fileName in fileEntries)
        {
            if (fileName.IndexOf(".xlsm") > 0 && fileName.IndexOf("$")<1) filePath = fileName;
        }
        if (filePath == "") return;
        filePath = filePath.Replace(".''", "''");
        string fileDest = filePath.Replace("Source","Processed");
        filePath = path+filePath;
        fileDest = path+fileDest;
        Excel.Application ExcelApp = new Excel.Application();
        Excel.Workbook wb = ExcelApp.Workbooks.Open(filePath, ReadOnly: false);
        try
        {
            ExcelApp.Visible = false;
            ExcelApp.Run("UpdateSheets");
            try
            {
                File.Delete(fileDest);
            }
            catch (Exception) { }
            wb.SaveAs(fileDest);
        }
        catch (Exception) { }
        wb.Close(false);
        ExcelApp.Application.Quit();
        ExcelApp.Quit();
    }

这对我有用，我有一个名为"ref"宏的xlsm文件。宏代码的一部分是用于退出excel应用程序(Application.Quit)。

下面的代码将打开文件，运行宏，然后关闭它:

using _Excel = Microsoft.Office.Interop.Excel;
using Microsoft.Office.Interop.Excel;
public static void runMacro (string sourceFile)
        {
            _Application excel = new _Excel.Application();
            excel.Visible = true;
            string pathToExcelXlsmFile = sourceFile;
            Workbook wb;
            Worksheet ws;
            int sheetNumber = 3; // sheet number where macro is in
            wb = excel.Workbooks.Open(pathToExcelXlsmFile);
            ws = wb.Worksheets[sheetNumber];
            //Call VBA code
            string runMacro = Path.GetFileName(sourceFile) + "!ref";
            excel.GetType().InvokeMember("Run", System.Reflection.BindingFlags.Default | System.Reflection.BindingFlags.InvokeMethod, null, excel, new Object[] { runMacro});
            excel.Quit();
            System.Runtime.InteropServices.Marshal.FinalReleaseComObject(excel);
        }
相关文章：