Results 1 to 4 of 4

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

  1. #1

    Thread Starter
    Ex-Super Mod RobDog888's Avatar
    Join Date
    Apr 2001
    Location
    LA, Calif. Raiders #1 AKA:Gangsta Yoda™
    Posts
    60,710

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

    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.
    Last edited by RobDog888; Aug 13th, 2006 at 04:36 PM.
    VB/Office Guru™ (AKA: Gangsta Yoda®)
    I dont answer coding questions via PM. Please post a thread in the appropriate forum.

    Microsoft MVP 2006-2011
    Office Development FAQ (C#, VB.NET, VB 6, VBA)
    Senior Jedi Software Engineer MCP (VB 6 & .NET), BSEE, CET
    If a post has helped you then Please Rate it!
    Reps & Rating PostsVS.NET on Vista Multiple .NET Framework Versions Office Primary Interop AssembliesVB/Office Guru™ Word SpellChecker™.NETVB/Office Guru™ Word SpellChecker™ VB6VB.NET Attributes Ex.Outlook Global Address ListAPI Viewer utility.NET API Viewer Utility
    System: Intel i7 6850K, Geforce GTX1060, Samsung M.2 1 TB & SATA 500 GB, 32 GBs DDR4 3300 Quad Channel RAM, 2 Viewsonic 24" LCDs, Windows 10, Office 2016, VS 2019, VB6 SP6

  2. #2

    Thread Starter
    Ex-Super Mod RobDog888's Avatar
    Join Date
    Apr 2001
    Location
    LA, Calif. Raiders #1 AKA:Gangsta Yoda™
    Posts
    60,710

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



    C# 2003 Late Binding Excel 2003 Automation Code Example:
    Code:
    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;
                }
            }
        }
    }
    Last edited by RobDog888; Apr 15th, 2007 at 05:00 PM. Reason: Fix code tags from recent upgrade
    VB/Office Guru™ (AKA: Gangsta Yoda®)
    I dont answer coding questions via PM. Please post a thread in the appropriate forum.

    Microsoft MVP 2006-2011
    Office Development FAQ (C#, VB.NET, VB 6, VBA)
    Senior Jedi Software Engineer MCP (VB 6 & .NET), BSEE, CET
    If a post has helped you then Please Rate it!
    Reps & Rating PostsVS.NET on Vista Multiple .NET Framework Versions Office Primary Interop AssembliesVB/Office Guru™ Word SpellChecker™.NETVB/Office Guru™ Word SpellChecker™ VB6VB.NET Attributes Ex.Outlook Global Address ListAPI Viewer utility.NET API Viewer Utility
    System: Intel i7 6850K, Geforce GTX1060, Samsung M.2 1 TB & SATA 500 GB, 32 GBs DDR4 3300 Quad Channel RAM, 2 Viewsonic 24" LCDs, Windows 10, Office 2016, VS 2019, VB6 SP6

  3. #3

    Thread Starter
    Ex-Super Mod RobDog888's Avatar
    Join Date
    Apr 2001
    Location
    LA, Calif. Raiders #1 AKA:Gangsta Yoda™
    Posts
    60,710

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



    C# 2003 Early Binding Excel 2003 Automation Code Example:
    Code:
    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;
                }
            }
        }
    }
    Last edited by RobDog888; Apr 15th, 2007 at 05:00 PM. Reason: Fix code tags from recent upgrade
    VB/Office Guru™ (AKA: Gangsta Yoda®)
    I dont answer coding questions via PM. Please post a thread in the appropriate forum.

    Microsoft MVP 2006-2011
    Office Development FAQ (C#, VB.NET, VB 6, VBA)
    Senior Jedi Software Engineer MCP (VB 6 & .NET), BSEE, CET
    If a post has helped you then Please Rate it!
    Reps & Rating PostsVS.NET on Vista Multiple .NET Framework Versions Office Primary Interop AssembliesVB/Office Guru™ Word SpellChecker™.NETVB/Office Guru™ Word SpellChecker™ VB6VB.NET Attributes Ex.Outlook Global Address ListAPI Viewer utility.NET API Viewer Utility
    System: Intel i7 6850K, Geforce GTX1060, Samsung M.2 1 TB & SATA 500 GB, 32 GBs DDR4 3300 Quad Channel RAM, 2 Viewsonic 24" LCDs, Windows 10, Office 2016, VS 2019, VB6 SP6

  4. #4

    Thread Starter
    Ex-Super Mod RobDog888's Avatar
    Join Date
    Apr 2001
    Location
    LA, Calif. Raiders #1 AKA:Gangsta Yoda™
    Posts
    60,710

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

    Open an Access database from C# using Late Binding

    Code:
    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
                
            }
        }
    }
    VB/Office Guru™ (AKA: Gangsta Yoda®)
    I dont answer coding questions via PM. Please post a thread in the appropriate forum.

    Microsoft MVP 2006-2011
    Office Development FAQ (C#, VB.NET, VB 6, VBA)
    Senior Jedi Software Engineer MCP (VB 6 & .NET), BSEE, CET
    If a post has helped you then Please Rate it!
    Reps & Rating PostsVS.NET on Vista Multiple .NET Framework Versions Office Primary Interop AssembliesVB/Office Guru™ Word SpellChecker™.NETVB/Office Guru™ Word SpellChecker™ VB6VB.NET Attributes Ex.Outlook Global Address ListAPI Viewer utility.NET API Viewer Utility
    System: Intel i7 6850K, Geforce GTX1060, Samsung M.2 1 TB & SATA 500 GB, 32 GBs DDR4 3300 Quad Channel RAM, 2 Viewsonic 24" LCDs, Windows 10, Office 2016, VS 2019, VB6 SP6

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width