CODEWITHSUNDEEP
c#excelwinforms
ENG K0K
ENG K0K

Reputation: 13

How to Run VB macro Script using C#?

I have a VB Script Macro used in excel File and its works well (Manual). I am trying to convert it to small APP using:

private static void RunMacro()
{

    Excel.Application book = null;
    Excel.Workbooks workbooks = null;
    Excel.Workbook macroWorkbook = null;
    Excel.Workbook destinationWorkbook = null;

    try
    {
        book = new Excel.Application();

        workbooks = book.Workbooks;
        macroWorkbook = workbooks.Open(System.Windows.Forms.Application.StartupPath + "\\NewMacro.xltm");
        destinationWorkbook = workbooks.Open(System.Windows.Forms.Application.StartupPath + "\\TEST.csv");

        book.Run("NewMacro.xltm");

        macroWorkbook.Close(false);
        destinationWorkbook.Close(true);
    }
    catch (Exception ex)
    {
        throw ex; // the finally will be executed before this is thrown
    }
    finally
    {
        book.Quit();

        System.Runtime.InteropServices.Marshal.ReleaseComObject(macroWorkbook);
        System.Runtime.InteropServices.Marshal.ReleaseComObject(destinationWorkbook);
        System.Runtime.InteropServices.Marshal.ReleaseComObject(workbooks);
        System.Runtime.InteropServices.Marshal.ReleaseComObject(book);

        macroWorkbook = null;
        destinationWorkbook = null;
        workbooks = null;
        book = null;
    }
}

I get the following error:

Additional information: Cannot run the macro 'NewMacro.xltm'. The macro may not be available in this workbook or all macros may be disabled.

Upvotes: 0

Views: 327

Answers (2)

Ggalla1779
Ggalla1779

Reputation: 476

 book.Run("NewMacro.xltm");

This means your trying to open a macro enabled spreadsheet and not the macro itself.

http://filext.com/file-extension/XLTM

An old question...something like this would work

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

Upvotes: 1

lucas bardoux
lucas bardoux

Reputation: 78

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

Upvotes: 0

Related Questions