[FAQ's: OD] How do I automate an Office App using C#?

[RobDog888]

You can automate just about any of the Office suite application. Office OneNote 2003 is one app that has limited programmatical support. Only two functions are exposed.

Excel, Word and Outlook are the Office Apps that get all the attention because of their vast ability to be customized and automated, not to mention that they themselves are widely used and popular.


Office Automation involves using COM (Microsoft® OLE Component Object Model).

There are several aspects to be concerned with when performing automation of Office applications:

• Early vs Late Binding
• The Office Application Version(s)
• Security
• Distribution

If your planning on only supporting one version of the Office application your automating then you dont have to worry about other versions so Early Binding would be good. Also, you get the Intelisense popups with Early Binding and not with Late. So Late Binding is best if your going to be supporting multiple versions of Office as the referene needs to be dynamic and attach when created in your program. Late Binding also requires you to either use the actual value of all constants or define them yourself with the appropriate values. With C# Late Binding requires you to use Reflection so you can specify the type of object and what function, methods and properties you are needing to access in your code.

If your not going to be distributing the Program or Add-In then your only needing to support a single Office version. This makes like allot easier but if you ever upgrade your Office version then you will need to recompile your program or Add-In after you have updated the references to your new version.

Security is another aspect to consider when choosing the type of program architecture your going to use. If its just a small .NET program automating and Office app or an all out Add-In. Each type has its pros and cons. They do preset security issues between the Office Application and the .NET program as the Office Applications have some built in security and you are also performing cross process marshalling so depending on the operations being performed you may need to code taking that into consideration. For the most power and security you would want to write a COM Add-In using .NET vs. just automating it with the basic .NET program. You can have even more power in your automation if you have Visual Studio Tools for Office (VSTO) available to you. With a COM Add-In you get an easier way to distribute the program, more security for your code, and security between Applications and for the ultimate in Office Automation you will want to use VSTO.


For more information on Office Add-Ins check out my other FAQ thread - "How do I use .NET to make an Add-In for an Office application?"

Also, for more information on VSTO, see this thread - "How do I make my first VSTO 2003 application?"
and this thread - "How do I make my first VSTO 2005 application?"

C# Code Examples below in post #2 and #3.

[RobDog888]

C# 2003 Late Binding Excel 2003 Automation Code Example:

using System;
using System.Drawing;
using System.Collections;
using System.ComponentModel;
using System.Windows.Forms;
using System.Data;
using System.Reflection;

namespace NET_Office_Automation_CS
{
    /// 

    /// Late Binding Office Automation C# FAQ Example
    /// 
    public class Form1 : System.Windows.Forms.Form
    {
        // DECLARE OUR APPLICATION OBJECT AS UNKNOWN GENERIC OBJECT
        private object moApp;
        private System.Windows.Forms.Button btnOpen;
        private System.Windows.Forms.TextBox txtFile;
        private System.Windows.Forms.Button btnBrowse;
        private System.Windows.Forms.Button btnClose;
        private System.Windows.Forms.Label lblFile;
        private System.Windows.Forms.OpenFileDialog openFileDialog1;
        private System.ComponentModel.Container components = null;

        public Form1()
        {
            InitializeComponent();
            Application.EnableVisualStyles();
            Application.DoEvents();
        }

        "Windows Form Designer generated code"

        [STAThread]
        static void Main() 
        {
            Application.Run(new Form1());
        }

        private void Form1_Load(object sender, System.EventArgs e)
        {
            // CREATE A GENERIC OBJECT VARIABLE & TRAP FOR EXCEL NOT INSTALLED
            try
            {
                System.Type moAppType;
                moAppType = System.Type.GetTypeFromProgID("Excel.Application");
                moApp = System.Activator.CreateInstance(moAppType);
                // KEEP IT HIDDEN FROM THE USER UNTIL WE USE IT
                moApp.GetType().InvokeMember("Visible", BindingFlags.SetProperty, null, moApp, 
                    new object[] {false});
            } 
            catch (Exception ex) 
            { 
                MessageBox.Show(ex.Message, "VB/Office Guru™ Excel Late Binding C# Automation FAQ", 
                MessageBoxButtons.OK, MessageBoxIcon.Exclamation);
            }
        }

        private void Form1_Closing(object sender, System.ComponentModel.CancelEventArgs e)
        {
            // CHECK IF EXCEL APPLICATION IS STILL INSTANCIATED
            if ((moApp != null) == true)
            {
                // CHECK IF INSTANCE HAS ANY OPEN WORKBOOKS
                object oWBs = moApp.GetType().InvokeMember("Workbooks", BindingFlags.InvokeMethod | 
                    BindingFlags.GetProperty , null, moApp, null);
                if (System.Convert.ToInt32(oWBs.GetType().InvokeMember("Count", BindingFlags.InvokeMethod | 
                    BindingFlags.GetProperty, null, oWBs, null)) > 0)
                {
                    // CLOSE ALL OPEN WORKBOOKS & DONT SAVE THEM
                    // LOOP BACKWARDS SO INDEX INFORMATION WILL BE RELEVANT AS THEY CLOSE
                    for (int i = System.Convert.ToInt32(oWBs.GetType().InvokeMember("Count", 
                        BindingFlags.InvokeMethod | BindingFlags.GetProperty, null, oWBs, null)); i >= 1; i--)
                    {
                        // moApp.Workbooks.Item(i).Close(false)
                        object oWB = oWBs.GetType().InvokeMember("Item", BindingFlags.InvokeMethod | 
                            BindingFlags.GetProperty, null, oWBs, new object[] {i});
                        /// .Close args: SaveChanges:=False, FileName:=vbNullString, RouteWorkbook:=False
                        oWB.GetType().InvokeMember("Close", BindingFlags.InvokeMethod, null, oWB, 
                            new object[] {false, String.Empty, false});
                    }
                }
                // CLOSE OUR EXCEL APPLICATION OBJECT
                moApp.GetType().InvokeMember("Quit", BindingFlags.IgnoreReturn | BindingFlags.Instance | 
                    BindingFlags.InvokeMethod, null, moApp, null);
            }
            // ENSURE ITS DESTROYED

            moApp = null;
            // ABSOLUTELY NONE OF OUR EXCEL INSTANCES WILL BE LEFT RUNNING
        }

        private void btnOpen_Click(object sender, System.EventArgs e)
        {
            // DECLARE & CREATE OUR WORKBOOK OBJECT
            // MAKE IT VISIBLE TO THE USER NOW
            moApp.GetType().InvokeMember("Visible", BindingFlags.IgnoreReturn | BindingFlags.Public | 
                BindingFlags.Static | BindingFlags.SetProperty, null, moApp, new object[] {true});
            // GET THE WORKBOOKS COLLECTION OF OUR APPLICATION OBJECT INSTANCE
            object oWBs = moApp.GetType().InvokeMember("Workbooks", BindingFlags.InvokeMethod | 
                BindingFlags.GetProperty, null, moApp, null);
            // GET THE FIRST WORKBOOK IN COLLECTION
            object oWB = oWBs.GetType().InvokeMember("Open", BindingFlags.InvokeMethod | 
                BindingFlags.GetProperty, null, oWBs, new string[] {this.txtFile.Text});
            // GET THE WORKSHEETS COLLECTION
            object oShts = oWB.GetType().InvokeMember("Worksheets", BindingFlags.InvokeMethod | 
                BindingFlags.GetProperty, null,oWB, null);
            // GET THE FIRST SHEET
            object oSht = oShts.GetType().InvokeMember("Item", BindingFlags.InvokeMethod | 
                BindingFlags.GetProperty, null, oShts, new object[] {1});
            // SAVE THE SHEET NAME
            string SheetName = oSht.GetType().InvokeMember("Name", BindingFlags.InvokeMethod | 
                BindingFlags.GetProperty, null, oSht, null).ToString();
            // GET THE CELLS COLLECTION
            object oCells = oSht.GetType().InvokeMember("Cells", BindingFlags.InvokeMethod | 
                BindingFlags.GetProperty, null, oSht, null);
            // INITIALIZE VARIABLE AS AN OBJECT
            object oCell = oCells.GetType().InvokeMember("Item", BindingFlags.InvokeMethod | 
                BindingFlags.GetProperty, null, oCells, new object[] {1, 1});
            // WRITE CURRENT DATE/TIME - CELL A1
            oCell.GetType().InvokeMember("Value2", BindingFlags.SetProperty, null, oCell, 
                new string[] {System.DateTime.Now.ToString()});
            // MAKE FIRST SHEET ACTIVE
            oSht.GetType().InvokeMember("Activate", BindingFlags.IgnoreReturn | BindingFlags.Public | 
                BindingFlags.Static | BindingFlags.InvokeMethod, null, oSht, null);
            this.Text = "Open Excel File: " + SheetName;
            this.Focus();
            // ENSURE VARIABLES ARE DESTROYED & RELEASED AS IT GOES OUT OF SCOPE
            SheetName = string.Empty;
            oCell = null;
            oCells = null;
            oSht = null;
            oShts = null;
            oWB = null;
            oWBs = null;
            // CALL THE GARBAGE COLLECT METHOD
            GC.Collect();
            GC.WaitForPendingFinalizers();
        }

        private void btnClose_Click(object sender, System.EventArgs e)
        {
            this.Close();
        }

        private void btnBrowse_Click(object sender, System.EventArgs e)
        {
            // BROWSE FOR EXCEL WORKBOOK FILE
            openFileDialog1.CheckFileExists = true;
            openFileDialog1.Filter = "Excel Workbooks Only (*.xls)| *.xls";
            openFileDialog1.FilterIndex = 1;
            openFileDialog1.Multiselect = false;
            openFileDialog1.Title = "Select Excel Workbook";
            if (openFileDialog1.ShowDialog() == DialogResult.OK) 
            {
                txtFile.Text = openFileDialog1.FileName;
            }
        }
    }
}

[RobDog888]

C# 2003 Early Binding Excel 2003 Automation Code Example:

using System;
using System.Drawing;
using System.Collections;
using System.ComponentModel;
using System.Windows.Forms;
using System.Data;
// ADD A REFERENCE TO MS EXCEL xx.0 OBJECT LIBRARY>
using Excel = Microsoft.Office.Interop.Excel;

namespace NET_Office_Automation_2_CS
{
    /// 

    /// Early Binding Office Automation C# FAQ Example
    /// 
    public class Form1 : System.Windows.Forms.Form
    {
        // DECLARE OUR APPLICATION OBJECT AS AN UNKNOWN GENERIC OBJECT>
        private Excel.Application moApp;
        private System.Windows.Forms.Button btnClose;
        private System.Windows.Forms.Button btnOpen;
        private System.Windows.Forms.TextBox txtFile;
        private System.Windows.Forms.Label lblFile;
        private System.Windows.Forms.Button btnBrowse;
        private System.Windows.Forms.OpenFileDialog openFileDialog1;
        private System.ComponentModel.Container components = null;

        public Form1()
        {
            InitializeComponent();
            Application.EnableVisualStyles();
            Application.DoEvents();
        }

        "Windows Form Designer generated code"

        /// 

        /// The main entry point for the application.
        /// 
        [STAThread]
        static void Main() 
        {
            Application.Run(new Form1());
        }

        private void Form1_Load(object sender, System.EventArgs e)
        {
            // CREATE OUR GENERIC UNKNOWN OBJECT VARIABLE AND TRAP FOR EXCEL NOT INSTALLED>
            try
            {
                moApp = new Excel.Application();
                // KEEP IT HIDDEN FROM THE USER UNTIL WE NEED TO USE IT>
                moApp.Visible = false;
            } 
            catch (Exception ex) 
            { 
                MessageBox.Show(ex.Message, "VB/Office Guru™ Excel Early Binding C# Automation FAQ", 
                    MessageBoxButtons.OK, MessageBoxIcon.Exclamation);
            }
        }

        private void Form1_Closing(object sender, System.ComponentModel.CancelEventArgs e)
        {
            // CHECK IF EXCEL APPLICATION IS STILL INSTANCIATED>
            if ((moApp != null) == true)
            {
                // CHECK IF OUR EXCEL INSTANCE HAS ANY OPEN WORKBOOKS IN IT>
                if (moApp.Workbooks.Count > 0)
                {
                    // CLOSE ALL OPEN WORKBOOKS AND DONT SAVE THEM>
                    // LOOP BACKWARDS SO THE INDEX INFORMATION WILL BE RELEVANT AS THEY CLOSE>
                    for (int i = System.Convert.ToInt32(moApp.Workbooks.Count); i >= 1; i--)
                    {
                        System.Reflection.Missing objMissing = System.Reflection.Missing.Value;
                        // .Close args: SaveChanges:=false, FileName:=null, RouteWorkbook:=false
                        moApp.Workbooks.get_Item(i).Close(false, objMissing, false);
                        objMissing = null;
                    }
                }
                // CLOSE OUR EXCEL APPLICATION OBJECT>
                moApp.Quit();
            }
            // MAKE SURE ITS DESTROYED>
            moApp = null;
            // ABSOLUTELY NONE OF OUR EXCEL INSTANCES/OBJECTS WILL BE LEFT RUNNING>
        }

        private void btnOpen_Click(object sender, System.EventArgs e)
        {
            System.Reflection.Missing oMsng = System.Reflection.Missing.Value;
            // DECLARE AND CREATE OUR WORKBOOK OBJECT>
            Excel.Workbook oWB = (Excel.Workbook)moApp.Workbooks.Open(this.txtFile.Text, oMsng, oMsng,
                oMsng, oMsng, oMsng, oMsng, oMsng, oMsng, oMsng, oMsng, oMsng, oMsng, oMsng, oMsng);
            // MAKE IT VISIBLE TO THE USER NOW THAT WE ARE GOING TO BE USING IT>
            moApp.Visible = true;
            // GET THE FIRST SHEET>
            Excel.Worksheet oSht = (Excel.Worksheet)oWB.Worksheets.get_Item(1);
            // SAVE THE SHEET NAME>
            string SheetName = oSht.Name;
            // INITIALIZE A VARIABLE(RANGE/CELL) TO An OBJECT VARIABLE>
            Excel.Range oRange = (Excel.Range)oSht.Cells.get_Item(1,1);
            // WRITE THE CURRENT DATE/TIME TO CELL A1>
            oRange.Value2 = System.DateTime.Now.ToString();
            // MAKE OUR SHEET THE ACTIVE ONE DISPLAYED>
            oSht.Activate();
            this.Text = "Open Excel File: " + SheetName;
            this.Focus();
            // MAKE SURE OUR OBJECT VARIABLES ARE DESTROYED AND RELEASED SINCE IT GOES OUT OF SCOPE>
            SheetName = string.Empty;
            oRange = null;
            oSht = null;
            oWB = null;
            // CALL THE GARBAGE COLLECT METHOD>
            GC.Collect();
            GC.WaitForPendingFinalizers();
        }

        private void btnClose_Click(object sender, System.EventArgs e)
        {
            this.Close();
        }

        private void btnBrowse_Click(object sender, System.EventArgs e)
        {
            // OUR BROWSE FOR EXCEL WORKBOOK FILE CODE>
            openFileDialog1.CheckFileExists = true;
            openFileDialog1.Filter = "Excel Workbooks Only (*.xls)| *.xls";
            openFileDialog1.FilterIndex = 1;
            openFileDialog1.Multiselect = false;
            openFileDialog1.Title = "Select Excel Workbook";
            if (openFileDialog1.ShowDialog() == DialogResult.OK) 
            {
                txtFile.Text = openFileDialog1.FileName;
            }
        }
    }
}

[RobDog888]

Open an Access database from C# using Late Binding

using System;
using System.Reflection;
using System.Windows.Forms;
//LATE BINDING EXAMPLE FOR VARIOUS VERSIONS OF ACCESS
namespace WindowsFormsApplication1
{
    public partial class Form1 : Form
    {
        public Form1()
        {
            InitializeComponent();
        }

        private void button1_Click(object sender, EventArgs e)
        {
            Type oAppType;
            object oApp;
            oAppType = Type.GetTypeFromProgID("Access.Application");
            oApp = Activator.CreateInstance(oAppType);
            oApp.GetType().InvokeMember("Visible", BindingFlags.IgnoreReturn | BindingFlags.Public |
                BindingFlags.Static | BindingFlags.SetProperty, null, oApp, new object[] { true });
            object oDB = oApp.GetType().InvokeMember("OpenCurrentDatabase", BindingFlags.InvokeMethod |
                BindingFlags.GetProperty, null, oApp, new object[] { "C:\\RobDog888.accdb" }); // CHANGE DB NAME AND LOCATION TO YOURS
            
        }
    }
}