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

Question

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"

Dewhurst

Answer 1

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.

    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);
    }

Dewhurst

RunMacro only seems to work if the macro file is in the users' Documents folder. Any attempt to give a full path to the file elsewhere results in an error message "the macro was not found or macros have been deactivated". – Specht

Yes, I've made several tries and this way to invoke macro (with InvokeMember) has only worked when my macro was phisically in "My Documents" folder. Trying to specify other location (with macro copied in that location) does not work. I've tried also with Microsoft.Office.Interop.Excel.Application class, Run method and it has only worked under the same conditions: macro filel phisically in "My Documents" folder. – Meadows

Code line: Excel.ApplicationClass oExcel = new Excel.ApplicationClass(); shows error in my Visual Sudio. Instead it I used: using Microsoft.Office.Interop.Excel; using _Excel = Microsoft.Office.Interop.Excel; and then _Application excel = new _Excel.Application(); my solution in more details is in #55262392 – Josephjosepha

Answer 2

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();
}

Considering

Sorry, I didn't realize my naming conventions were a bit misleading. What I called Worksheet01 and Worksheet02 were two different files (one being an xlsm and the other a csv). – Dewhurst

Answer 3

    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();
    }

Jun

Answer 4

This worked for me, i have xlsm files with a macro called "ref". Part of the macro code is to exit the excel app (Application.Quit).

This code will open the file, run the macro and then closes it:

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);
        }

Arwood