Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

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

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

Using Microsoft.Office.Interop.Excel Namespace I have opened a 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.

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.)

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.

Recap:

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

Resources:

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

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 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"
like image 702
kirbycope Avatar asked Jan 09 '13 23:01

kirbycope


People also ask

Can I use C# in Excel macro?

You can call a (C#) web service via a macro. I think the same applies to restful endpoints written in webapi ...

Can you run a macro outside of Excel?

There are many instances where you could copy your Excel Macro to a VBScript file and run it directly, but sometimes you don't want to deal with that headache. It's in these moments where it's beneficial to create a VBScript file to run your existing macros from outside Excel. You already have a working VBA macro.


1 Answers

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

this.Application.Run("mymacro");

Edit:

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

foreach (Worksheet worksheet in workbook.Sheets.ComLinq<Worksheet>())
{
    if (worksheet.Name == "Sheet2") worksheet.Activate();
}
like image 121
Jeremy Thompson Avatar answered Oct 01 '22 15:10

Jeremy Thompson