how to call excel macros programmatically in C#?

Question

Hi,

I have a requirement where i need to call excel (2003) macros in C# program. Can anyone help me with a code snippet to do the same?

The excel macro function takes two input parameters? how can the parameters be passed?

Any code snippet to do the same in C# would be helpful.

Thanks.

Answer 1

Let's say you have an Excel Macro which looks like this

Sub ShowMsg(msg As String, title As String)
    MsgBox msg, vbInformation, title
End Sub

Here is the C# code to pass arguments to that macro and then call it. (TRIED AND TESTED with VS2010 + Excel 2010)

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using Excel = Microsoft.Office.Interop.Excel;

namespace WindowsFormsApplication2
{
    public partial class Form1 : Form
    {
        public Form1()
        {
            InitializeComponent();
        }

        private void button1_Click(object sender, EventArgs e)
        {
            //~~> Define your Excel Objects
            Excel.Application xlApp = new Excel.Application();

            Excel.Workbook xlWorkBook;

            //~~> Start Excel and open the workbook.
            xlWorkBook = xlApp.Workbooks.Open("E:\\Users\\Siddharth Rout\\Desktop\\book1.xlsm");

            //~~> Run the macros by supplying the necessary arguments
            xlApp.Run("ShowMsg", "Hello from C# Client", "Demo to run Excel macros from C#");

            //~~> Clean-up: Close the workbook
            xlWorkBook.Close(false);

            //~~> Quit the Excel Application
            xlApp.Quit();

            //~~> Clean Up
            releaseObject(xlApp);
            releaseObject(xlWorkBook);
        }

        //~~> Release the objects
        private void releaseObject(object obj)
        {
            try
            {
                System.Runtime.InteropServices.Marshal.ReleaseComObject(obj);
                obj = null;
            }
            catch (Exception ex)
            {
                obj = null;
            }
            finally
            {
                GC.Collect();
            }
        }
    }
}

HTH

---

Sid (A good exercise for the Heart is to bend down and help another up) Please do not email me your questions. I do not answer questions by email unless I get paid for it :) If you want, create a thread in VB.Net/Excel forum and email me the link and I will help you if I can.

Answer 2

Thanks for the reply.

In the macro, i m reading an xml file and writing data from xml file to the excel file.

After i come out of the macro, i m saving the excel file. But the excel file does not have any data entered through macro.

The code for calling the macro:

var workbook = excelApp.Workbooks.Open(fileName, false, true, Type.Missing, Type.Missing, Type.Missing, true, Type.Missing, Type.Missing, false, false, Type.Missing, false, true, Type.Missing);
            RunMacro(excelApp, new Object[] { "ProcessXMLExtract", inputFile, "HP" });
            workbook.Save();
            workbook.Close();

code for RunMacro:

private void RunMacro(object oApp, object[] oRunArgs)
        {
            oApp.GetType().InvokeMember("Run",
                System.Reflection.BindingFlags.Default |
                System.Reflection.BindingFlags.InvokeMethod,
                null, oApp, oRunArgs);
        }

Is there anything wrong in the steps? How can i save the data in excel entered by macros?

Thanks.

Answer 3

Show me the code for the macro "ProcessXMLExtract"

---

Sid (A good exercise for the Heart is to bend down and help another up) Please do not email me your questions. I do not answer questions by email unless I get paid for it :) If you want, create a thread in VB.Net/Excel forum and email me the link and I will help you if I can.

Answer 4

Hi Nayna,

How is it going with the problem currently?

Let us know if you need any help.

Thanks.

---

Yoyo Jiang[MSFT]
MSDN Community Support | Feedback to us