C#中调用Excel中宏VBA

为了在C#中使用Excel，我们要先做一点准备工作，通过查找（前提是你安装Visual Studio.Net和Excel 2000），在你的计算机中找到TlbImp和Excel9.olb，将他们复制到一个文件夹中，在DOS窗口中执行 TlbImp Excel9.olb，这时会产生以下三个文件：Excel.dll、Office.dll和VBIDE.dll。
在C#.Net项目中引用Excel.dll、Microsoft.Office.Core

C# 代码：

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Text;
using System.Windows.Forms;

using System.Reflection;
using Excel = Interop.Excel;
using Microsoft.Office.Core;

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

        private void button1_Click(object sender, EventArgs e)
        {
            // Object for missing (or optional) arguments.
            object oMissing = System.Reflection.Missing.Value;


            // Create an instance of Microsoft Excel, make it visible,
            // and open Book1.xls.
            Excel.ApplicationClass oExcel = new Excel.ApplicationClass();
            oExcel.Visible = true;
            Excel.Workbooks oBooks = oExcel.Workbooks;
            Excel._Workbook oBook = null;
            oBook = oBooks.Open("e:\\book1.xls", oMissing, oMissing,
                oMissing, oMissing, oMissing, oMissing, oMissing, oMissing,
                oMissing, oMissing, oMissing, oMissing, oMissing, oMissing);


           // RunMacro(oExcel, new Object[] { "AddToCellMenu()" });
            // Run the macros.
            RunMacro(oExcel, new Object[] { "DoKbTest" });
            RunMacro(oExcel, new Object[]{"DoKbTestWithParameter",
                           "c#调用宏"});



            // 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;

            //GC.Collect();   //Garbage collection.

        }

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

    }
}

 

Excel 中VBA代码：

'Display a message box that displays the application name.
Public Sub DoKbTest()
   MsgBox "Hello from " & Application.Name
End Sub

'Display a message box with the string passed from the
'Automation client.
Public Sub DoKbTestWithParameter(sMsg As String)
   MsgBox sMsg
End Sub