-
May 22nd, 2006, 03:31 AM
#1
[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 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 Posts • VS.NET on Vista • Multiple .NET Framework Versions • Office Primary Interop Assemblies • VB/Office Guru™ Word SpellChecker™.NET • VB/Office Guru™ Word SpellChecker™ VB6 • VB.NET Attributes Ex. • Outlook Global Address List • API 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
-
Aug 10th, 2006, 07:41 PM
#2
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 04: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!
• Reps & Rating Posts • VS.NET on Vista • Multiple .NET Framework Versions • Office Primary Interop Assemblies • VB/Office Guru™ Word SpellChecker™.NET • VB/Office Guru™ Word SpellChecker™ VB6 • VB.NET Attributes Ex. • Outlook Global Address List • API 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
-
Aug 10th, 2006, 07:41 PM
#3
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 04: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!
• Reps & Rating Posts • VS.NET on Vista • Multiple .NET Framework Versions • Office Primary Interop Assemblies • VB/Office Guru™ Word SpellChecker™.NET • VB/Office Guru™ Word SpellChecker™ VB6 • VB.NET Attributes Ex. • Outlook Global Address List • API 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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|