[FAQ's: OD] How do I automate an Office App using VB.NET?

[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.

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?"

VB.NET Code Examples below in post #2 and #3.

[RobDog888]

VB.NET 2003 Late Binding Automation Code Example:

Option Explicit On 
'Option Strict Off is needed to allow the late binding
Option Strict Off

'Late Binding Automation FAQ Example
Public Class Form1

    Inherits System.Windows.Forms.Form

    "Windows Form Designer generated code"

    'Declare our Application Object as an unknown generic Object
    Public moApp As Object

    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        'Declare and create a Workbook object
        Dim oWB As Object = moApp.Workbooks.Add()
        'Make it Visible to the user now that we are going to use it
        moApp.Visible = True
        'Write some text into Cell A1 on Sheet1
        oWB.Sheets("Sheet1").Cells(1, 1).Value = "Meow!"
        'Make sure our object variable is destroyed and released since its going out of scope
        oWB = Nothing
    End Sub

    Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        'Create our generic unknown object variable and trap for Excel not installed
        Try
            moApp = CreateObject("Excel.Application")
            'Keep it hidden from the user until we need to use it
            moApp.Visible = False
        Catch ex As Exception
            MessageBox.Show(ex.Message, "VB/Office Guru™ Excel Late Binding VB.NET Automation FAQ", _
            MessageBoxButtons.OK, MessageBoxIcon.Exclamation)
        End Try
    End Sub

    Private Sub Form1_Closing(ByVal sender As Object, ByVal e As System.ComponentModel.CancelEventArgs) _
    Handles MyBase.Closing
        'Check if Excel Application is still instanciated
        If IsNothing(moApp) = False Then
            'Check if our Excel instance has any opened workbooks in it
            If moApp.Workbooks.Count > 0 Then
                'Close all open workbooks not saving them
                'Loop backwards so the index information will be relevant as they close
                For i As Integer = moApp.Workbooks.Count To 1 Step -1
                    moApp.Workbooks.Item(i).Close(SaveChanges:=False)
                Next
            End If
            'Close our Excel Application object
            moApp.Quit()
        End If
        'Make sure its destroyed
        moApp = Nothing
        'Absolutely none of our Excel instances will be left running!
    End Sub

End Class

[RobDog888]

VB.NET 2003 Early Binding Automation Code Example:

Option Explicit On 
Option Strict On
'Add a reference to MS Excel xx.0 Object Library
'Import the Excel Primary Interop Assembly
Imports Microsoft.Office.Interop

Public Class Form1

    Inherits System.Windows.Forms.Form

    "Windows Form Designer generated code"

    Private moApp As Excel.Application

    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        'Declare and create a Workbook object
        Dim oWB As Excel.Workbook = moApp.Workbooks.Add()
        'Make it Visible to the user now that we are going to use it
        moApp.Visible = True
        'Extra code to follow option strict so we can access a cell.
        Dim oSht As Excel.Worksheet = DirectCast(oWB.Sheets("Sheet1"), Excel.Worksheet)
        Dim oRange As Excel.Range = CType(oSht.Cells(1, 1), Excel.Range)
        'Write some text into Cell A1 on Sheet1
        oRange.Value = "Meow!"
        'Make sure our object variable is destroyed and released since its going out of scope
        oRange = Nothing
        oSht = Nothing
        oWB = Nothing
    End Sub

    Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        'Create our generic unknown object variable and trap for Excel not installed
        Try
            moApp = DirectCast(CreateObject("Excel.Application"), Excel.Application)
            'Keep it hidden from the user until we need to use it
            moApp.Visible = False
        Catch ex As Exception
            MessageBox.Show(ex.Message, "VB/Office Guru™ Excel Early Binding VB.NET Automation FAQ", _
            MessageBoxButtons.OK, MessageBoxIcon.Exclamation)
        End Try
    End Sub

    Private Sub Form1_Closing(ByVal sender As Object, ByVal e As System.ComponentModel.CancelEventArgs) _
    Handles MyBase.Closing
        'Check if Excel Application is still instanciated
        If IsNothing(moApp) = False Then
            'Check if our Excel instance has any opened workbooks in it
            If moApp.Workbooks.Count > 0 Then
                'Close all open workbooks not saving them
                'Loop backwards so the index information will be relevant as they close
                For i As Integer = moApp.Workbooks.Count To 1 Step -1
                    moApp.Workbooks.Item(i).Close(SaveChanges:=False)
                Next
            End If
            'Close our Excel Application object
            moApp.Quit()
        End If
        'Make sure its destroyed
        moApp = Nothing
        'Absolutely none of our Excel instances will be left running!
    End Sub

End Class