CODEWITHSUNDEEP
c#excelexcel-interopvba
marksfrancis
marksfrancis

Reputation: 1752

Running an Excel macro on a different workbook

I am trying to run a VBA macro from one workbook on another workbook.
I am using the Microsoft.Office.Interop.Excel (or equivalent) object (add a reference (COM) to Microsoft Excel 12.0 Object Library if you want to use it) to edit and use Excel documents from a C# executable.

For example, document one has some data in it. Document two contains macros to format document one. Document one is new every day, so I can't store the macros there. I'm trying to run a macro from document two on document one using the Excel.Application.Run().

The example macro I'm using is simple (stored in the Microsoft Excel Object: ThisWorkbook):

Sub Test()
  Sheets("Sheet1").Select
  Range("A1").Value = 32
End Sub

I need this to run on a different workbook. I can run it on the same workbook with the following code:

Application xlApp = new Application(); //Excel app
Workbook xlWbk = null;
try
{
  xlWbk = xlApp.Workbooks.Open(DocumentTwoLocation);
  xlApp.Run("'" + DocumentTwoLocation + "'!" + MacroName); //MacroName example: ThisWorkbook.Test
}
finally
{
  if (xlWbk != null)
    try
    {
      xlWbk.Close(true); //Saves changes
    }
    catch
    {
      xlWbk.Close(false);
    }
  xlApp.Quit();
}

When I change DocumentTwoLocation in the xlApp.Workbooks.Open to DocumentOneLocation, the macro will not run. A COMException is thrown (HRESULT: 0x800A03EC) which is NameNotFound (only thrown when the xlApp.Run() is used). The file addresses are correct. Even if I open both documents beforehand like so:

Application xlApp = new Application(); //Excel app
Workbook xlWbk = null;
Workbook xlMacroBook = null;
try
{
  xlWbk = xlApp.Workbooks.Open(DocumentOneLocation);
  xlMacroBook = xlApp.Workbooks.Open(DocumentTwoLocation);
  xlApp.Run("'" + DocumentTwoLocation + "'!" + MacroName); //MacroName example: ThisWorkbook.Test
}
finally
{
  if (xlWbk != null)
    try
    {
      xlWbk.Close(true); //Saves changes
    }
    catch
    {
      xlWbk.Close(false);
    }
  if (xlMacroBook != null)
    xlMacroBook.Close(false) //Don't save changes
  xlApp.Quit();
}

An error still occurs (same 0x800A03EC exception).

MSDN has virtually no documentation on what I am missing or how I should approach this (https://msdn.microsoft.com/en-us/library/microsoft.office.interop.excel.applicationclass.run.aspx)

Something similar has been asked here: Error when calling Excel macro from C# and I've done all I can to match what they have written (including adding the ReleaseComObject, and setting to null after) but to no avail.

Upvotes: 3

Views: 2001

Answers (1)

marksfrancis
marksfrancis

Reputation: 1752

I have since solved the issue.

The issue occuring was as a result of trying to run an "xlsm" format macro on a different document. Running macros on another workbook is only possible with Microsoft Office Excel Binary Worksheet (xlsb format). If you're facing the same issue - use an xlsb to save the macros in, not xlsm.

For those of you interested, the final code is as follows:

/// <summary>
/// Run a macro from an xlsb file on another excel file
/// </summary>
/// <param name="ExcelFile">The excel file to run the macro on</param>
/// <param name="MacroFileLocation">The xlsb file the macro is saved in</m>
/// <param name="Macro">The macro name to run (e.g. Module1.Example)</param>
static void Test(string ExcelFile, string MacroFileLocation, string Macro)
{
    Application xlApp = new Application(); //Excel app
    Workbook xlWbk = null;
    try
    {
        xlWbk = xlApp.Workbooks.Open(ExcelFile);

        string MacroCommand = "'" + MacroFileLocation + "'!" + Macro;
        xlApp.Run(MacroCommand);
    }
    finally
    {
        //Clean up
        if (xlWbk != null)
            try
            {
                xlWbk.Close(true);
            }
            catch
            {
                //Couldn't save - consider alerting user
                xlWbk.Close(false);
            }
        xlApp.Quit();

        System.Runtime.InteropServices.Marshal.ReleaseComObject(xlWbk);
        System.Runtime.InteropServices.Marshal.ReleaseComObject(xlApp);

        xlWbk = null;
        xlApp = null;
    }
}

Upvotes: 3

Related Questions