[FAQ's: OD] How do I automate an Office App using VB.NET?-VBForums
Results 1 to 3 of 3

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

  1. #1

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

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

    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.
    Last edited by RobDog888; Aug 13th, 2006 at 05: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!
    Star Wars Gangsta Rap 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 Core 2 Extreme Ed., 2 WD Raptor 10K RPM 300 GB HDs, 2 GBs DDR2 667 MHz RAM, 2 Viewsonic 24" LCDs, Windows 7 SP-1/Windows 8.1, Office 2010, VS 2013

  2. #2

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

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

    VB.NET 2003 Late Binding Automation Code Example:
    Code:
    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
    Last edited by RobDog888; Apr 15th, 2007 at 05:58 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!
    Star Wars Gangsta Rap 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 Core 2 Extreme Ed., 2 WD Raptor 10K RPM 300 GB HDs, 2 GBs DDR2 667 MHz RAM, 2 Viewsonic 24" LCDs, Windows 7 SP-1/Windows 8.1, Office 2010, VS 2013

  3. #3

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

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

    VB.NET 2003 Early Binding Automation Code Example:
    Code:
    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
    Last edited by RobDog888; Apr 15th, 2007 at 05:59 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!
    Star Wars Gangsta Rap 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 Core 2 Extreme Ed., 2 WD Raptor 10K RPM 300 GB HDs, 2 GBs DDR2 667 MHz RAM, 2 Viewsonic 24" LCDs, Windows 7 SP-1/Windows 8.1, Office 2010, VS 2013

Posting Permissions

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



Featured


Click Here to Expand Forum to Full Width

Survey posted by VBForums.