VB.Net Office Integration FAQ
VB.Net Office Integration FAQ
VB.Net Office Integration FAQ
VB.Net Office Integration FAQ
VB.Net Office Integration FAQ
VB.Net Office Integration FAQ VB.Net Office Integration FAQ VB.Net Office Integration FAQ VB.Net Office Integration FAQ VB.Net Office Integration FAQ VB.Net Office Integration FAQ VB.Net Office Integration FAQ VB.Net Office Integration FAQ
VB.Net Office Integration FAQ VB.Net Office Integration FAQ
VB.Net Office Integration FAQ
Go Back  Xtreme Visual Basic Talk > > > VB.Net Office Integration FAQ


Closed Thread
 
Thread Tools Display Modes
  #1  
Old 12-19-2003, 01:13 PM
Mike Rosenblum's Avatar
Mike Rosenblum Mike Rosenblum is offline
Microsoft Excel MVP

Forum Leader
* Guru *
 
Join Date: Jul 2003
Location: New York, NY, USA
Posts: 7,848
Default VB.Net Office Integration FAQ


Welcome to Office Integration using VB.Net

This tutorial is designed to cover the basics required for using VB.Net to control and integrate MS Office programs into a VB.Net application.

To start it off, the Tutorial currently consists of two lessons, written for those looking to use COM Interop (Automation) in their VB.Net Application to control MS Office programs such as Excel, Word and Access.

It is written for the VB 6.0 programmer, familiar with Automation, but who wishes to learn some of the basic changes that have occurred in VB.Net.

It is also of value to the VB.Net programmer who may not be familiar with Automation using .Net, for there are a number of pitfalls for the unwary... However, it is not intended as a full-blown tutorial covering every meaningful change in Syntax and Framework in VB.Net. “Just the basics.”

Lesson 1: Basic Changes in VB.Net Automation versus prior VB 6.0

Lesson 2: Automating Office Programs with VB.Net

Good luck, and have fun!

,
Mike
__________________
My Articles:
| Excel from .NET | Excel RibbonX using VBA | Excel from VB6 | CVErr in .NET | MVP |
Avatar by Lebb

Last edited by Wamphyri; 12-19-2003 at 01:32 PM.
  #2  
Old 12-19-2003, 01:16 PM
Mike Rosenblum's Avatar
Mike Rosenblum Mike Rosenblum is offline
Microsoft Excel MVP

Forum Leader
* Guru *
 
Join Date: Jul 2003
Location: New York, NY, USA
Posts: 7,848
Default

===================================================
Lesson 1: Basic Changes in VB.Net Automation versus prior VB 6.0
-------------------------------------------------------------------

There are a number of fundamental changes to be observed when Automating in VB.Net versus the previous VB 6.0 / 5.0 / 4.0 versions. Here we will cover a few of the most important:

(1) There is no 'Set' Keyword used in VB.Net. In VB.Net you omit its usage where it would have been used previously in VB 6.0.

(2) The tricky part about Automating an Office program such as Excel, Word or Access is that you need to declare each sub-object as its own, separate variable.

For example, instead of the following code
Code:
oWB = oExcel.Workbooks.Add()
when Automating from VB.Net, you are required to break it down as follows:
Code:
dim oWorkbooks as Excel.Workbooks oWorkbooks = oExcel.Workbooks oWB = oWorkbooks.Add()
While the first line of code looks cleaner and is a perfectly valid construction 99% of the time when using VB.Net, using such code for COM Automation will not allow your referenced Application Object to quit properly when done. (For more information on this, please see examples used in Lesson 2, below, as well as MSKB - 317109.)


(3) 'Cleanup' is also more involved when using Automation in .Net than it was in VB 6.0 or even for most other areas of .Net programming. To properly release your object references when using COM Automation in .Net, you must explicitly call System.Runtime.InteropServices.Marshal.ReleaseComObject() for each object. For example:
Code:
oWB.Close(SaveChanges:=False) System.Runtime.InteropServices.Marshal.ReleaseComObject(oWB) oWB = Nothing oExcel.Quit() System.Runtime.InteropServices.Marshal.ReleaseComObject(oExcel) oExcel = Nothing
However, using the following routine can be of great help in keeping your code manageable:
Code:
Private Sub NAR(ByVal o As Object) ' This sub cleanly releases the COM Automation Object. ' Source: http://support.microsoft.com/?kbid=317109 Try System.Runtime.InteropServices.Marshal.ReleaseComObject(o) Catch Finally o = Nothing End Try End Sub
The above Sub NAR() then allows us to reduce our cleanup proceedure to:
Code:
oWB.Close(SaveChanges:=False) NAR(oWB) oExcel.Quit() NAR(oExcel)
The NAR() routine can be found in the MKBA - 317109, with special thanks to Wamphyri for pointing it out.


(4) Error Handling is substantially changed in .Net. So much so that a full discussion would require it's own, separate lesson. (That is, not here.)

For the squeamish, the older VB6 Error Handling constructs still apply; that is, you can still use 'On Error Goto <linelabel>' and 'On Error Resume Next' as well as the 'Resume' and 'Resume Next' statements. However, it is well worth the investment to learn the new constructs using 'Try...End Try' along with the 'Catch' and 'Finally' keywords.

In the Sub NAR(), above, the 'Try..End Try' is bracketing the error handling. If any error occurs within the 'Try..Catch' block, then code execution immediately jumps to the 'Catch' statement -- which in this case is empty, that is, nothing to execute. The code after the 'Finally' statement always executes regardless of whether an error occurred or not within the 'Try..Catch' section. So for the Sub NAR(), the 'Finally' statement is ensuring that
Code:
o = Nothing
always occurs.

For basic info on Error Handling in VB.Net, see MSDN help on the Try..Catch..Finally Statements.


(5) For the most part, 'Default' properties such as the .Value property of an Excel.Range object must now be explicitly stated as 'Range.Value' at all times. The only Default Properties that can still be accessed in 'Default Mode' are those that require parameters. For example:
Code:
Dim X As Integer X = oExcel.ActiveSheet.Range("A1") ' < -- Run-Time Error! X = oExcel.ActiveSheet.Range("A1").Value ' <-- Correct.
The first line fails because 'X' is being set as a Reference to the Range("A1"). In VB6 the compiler would assume that you *meant* to use Range("A1").Value, due to the lack of the 'Set' keyword. However, in VB.Net, there is no 'Set' Keyword and so 'X' is referencing the Range("A1") object itself. Since 'X' is an Integer, and cannot hold a Range object, an error is raised. (Or to use .Net terminology, "an exception is thrown".)

The 2nd line in the above is fine, for the X is being assigned to the Range.Value. In short, always be explicit with your 'Default' properties & methods if they don't take a parameter, for such 'Default' members are not recognized in .Net.

Note the following, however:
Code:
Dim oWS as Excel.Worksheet oWS = oExcel.Worksheets.Item("Sheet1") ' <-- OK. oWS = oExcel.Worksheets("Sheet1") ' <-- Also OK!
The 2nd version, above, is utilizing the Excel.Worksheets default .Item() property in 'Default Mode'. This is permitted because Worksheets.Item() takes a parameter, while the Excel.Range's default .Value property does not.

There is no ambiguity for the compiler here, which knows that the Default .Item() property is being called, and the oWS object reference is being set to it.


(6) Parentheses are now required when passing parameters to functions and subs at all times. In VB 6.0, one would usually call the MsgBox function in the following manner:
Code:
MsgBox "Hello"
However, in VB.Net, the parentheses are required:
Code:
MessageBox.Show("Hello")
And, yes, the MsgBox command has been updated with VB.Net’s newer MessageBox object. The older MsgBox does still work (if you insist), but it is not recommended. VB.Net will run much more efficiently if you avoid using legacy commands. In short, use the MessageBox -- you'll like it, even if it does have some extra letters.

Good luck out there!

,
Mike
__________________
My Articles:
| Excel from .NET | Excel RibbonX using VBA | Excel from VB6 | CVErr in .NET | MVP |
Avatar by Lebb
  #3  
Old 12-19-2003, 01:17 PM
Mike Rosenblum's Avatar
Mike Rosenblum Mike Rosenblum is offline
Microsoft Excel MVP

Forum Leader
* Guru *
 
Join Date: Jul 2003
Location: New York, NY, USA
Posts: 7,848
Default

====================================================
Lesson 2: Automating Office Programs with VB.Net
-------------------------------------------------------------------
The first step for Automating a Microsoft Office Program with VB.Net is to establish your Application instance. And that begins with making sure that you have the proper Project Library referenced by your VB.Net application.

In this example, we will be Automating (controlling) Microsoft Excel, so you will need to add a reference to your Microsoft Excel Object Library. The procedure to do this is *almost* the same as it was in VB 6.0:

(1) Go to Alt|Project|Add Reference...What is different at this point is that you must now click on the COM Tab at the top, or you will not find it! Now scroll down until you find "Microsoft Excel 10.0 Object Library" (which could be 9.0, 8.0 or even 11.0 depending on your system).

(2) Next you click the <Select> button and then click <OK>. Now your Reference to the Excel Object Library is loaded.


The next step is to write your code! The basic shell for Automating Excel, for example, could look something like this :
Code:
Dim oExcel As Excel.Application Dim oWorkbooks As Excel.Workbooks Dim oSheets As Excel.Sheets ' <-- Quirk in XL requires Excel.Sheets Dim oWB As Excel.Workbook Dim oWS As Excel.Worksheet Dim oRng1, oRng2 As Excel.Range oExcel = New Excel.Application ' <-- No 'Set' Keyword in .Net. oExcel.Visible = True ' <-- Using .Visible = True is optional. ' Your Code Goes Here ' Your Code Goes Here ' Your Code Goes Here ' Your Code Goes Here Cleanup: ' Cleanup is more extensive for COM Automation than is typical for ' other areas of VB.Net. To be 100% certain that your Application ' and other related references are released properly, you should ' use a structure similar to the following: NAR(oRng1) ' <-- See the Sub NAR(), below. NAR(oRng2) NAR(oWS) NAR(oSheets) NAR(oWorkbooks) oWB.Close(False) NAR(oWB) oExcel.Quit() NAR(oExcel) End Sub Private Sub NAR(ByVal o As Object) ' This sub cleanly releases the COM Automation Object. ' Source: http://support.microsoft.com/?kbid=317109 Try System.Runtime.InteropServices.Marshal.ReleaseComObject(o) Catch Finally o = Nothing End Try End Sub
The code above is the basic "shell", if you will, for creating and then safely disposing of your Excel Application object.

The Sub NAR(), shown in the code above, is a clean way to encapsulate the call to the Marshal.ReleaseComObject() to release the COM Automation Object and then set that reference equal to 'Nothing'. In .Net one does not normally have to explicitly force the release of a referenced object like this, but for COM Automation, it is essential.

The Sub NAR() routine, however, allows us to encapsulate this command into a quick call to
Code:
NAR(oMyObject)
instead.

(For further discussion of the Sub NAR() routine, see Lesson 1, above, in points #3 & #4.)


In the area marked "Your Code Goes Here" is where you would generally have code to open a Workbook and manipulate or read data on a Worksheet. An example of this could look like the following:
Code:
oWorkbooks = oExcel.Workbooks ' <-- oExcel.Workbooks.Add cannot be accessed in oWB = oWorkbooks.Add ' in one line, or it will cause Excel to Hang. oSheets = oWB.Worksheets ' < -- Again, oWB.Worksheets.Item("Sheets1") cannot oWS = oSheets.Item(1) ' be accessed in one line. oRng1 = oWS.Range("A1") ' <-- oWS.Range("A1").Value is to be avoided, oRng1.Value = "Hello World!" ' for it would cause Excel to Hang. oRng2 = oWS.Range("B2:E5") ' <-- oRng1.Copy(oWS.Range("B2:E5")) would again oRng1.Copy(oRng2) ' hang the running instance of Excel.
Note in the above that "composite" object references must be avoided at all costs when using Automation in .Net. For example, in the above,
Code:
oWS.Range("A1").Value = "Hello World!"
has to be split into the two lines:
Code:
oRng1 = oWS.Range("A1") oRng1.Value = "Hello World!"
The stucture may look like excess coding and would in fact not be necessary 99% of the time when programming in .Net. However, for Automation it is 100% required or your MS Office Application will not close even after calling Marshal.ReleaseComObject().

(For a further discussion, see Lesson 1, Point (2), above.)


This last section is particular to Excel Automation, but note the line towards the very top of this Lesson that reads
Code:
Dim oSheets As Excel.Sheets ' <-- Quirk in XL requires Excel.Sheets
This line is subtle, but there are problems utilizing the Worksheets collection when Automating Excel from .Net. Instead, use the Excel.Sheets class, which can return either an Excel.Worksheet object or an Excel.Chart object.

If you are NOT utilizing 'Option Strict On' then you'll barely notice the difference and can use the following code:
Code:
oSheets = oWB.Worksheets oWS = oSheets.Item(1)
However, because Excel.Sheets is polymorphic, if using 'Option Strict On' (which is highly recommended) then you will have to utilize CType(), as in the following:
Code:
oSheets = oWB.Worksheets oWS = CType(oSheets.Item(1), Excel.Worksheet)


The examples we used, above, utilize the Excel Object Model. However, with the exception of dealing with the 'Sheets' and 'Worksheets' classes, the concepts are 100% parallel when Automating Word, Access or Outlook. Even if the Methods and Properties within those object models are different, the structure for creating your Application instance and referencing it is the same.

Hope this gets you going...!

,
Mike

Further Reading:
(1) Office Application Does Not Quit After Automation from VS.NET Client
(2) HOWTO: Run Office Macros by Using Automation from Visual Basic .NET
(3) Microsoft Office Development with Visual Studio
(4) Develop Microsoft Office Solutions with Visual Studio .NET

Special Thanks:
To Wamphyri, Reboot and the rest of the ExVBF team for their guidance and (occasionally ) brilliant solutions... Thanks guys.
__________________
My Articles:
| Excel from .NET | Excel RibbonX using VBA | Excel from VB6 | CVErr in .NET | MVP |
Avatar by Lebb
Closed Thread


Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)
 
Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
MsFlexGrid & VBA msl Word, PowerPoint, Outlook, and Other Office Products 2 09-17-2006 04:21 PM
Help with VB,ASP and Office Integration art_abon Word, PowerPoint, Outlook, and Other Office Products 1 12-14-2003 07:22 PM
Office Integration with Java applications alexiabelle Word, PowerPoint, Outlook, and Other Office Products 0 08-03-2003 03:58 AM
Office Integration Thinker Word, PowerPoint, Outlook, and Other Office Products 1 10-13-2001 01:44 AM

Advertisement:





Free Publications
The ASP.NET 2.0 Anthology
101 Essential Tips, Tricks & Hacks - Free 156 Page Preview. Learn the most practical features and best approaches for ASP.NET.
subscribe
Programmers Heaven C# School Book -Free 338 Page eBook
The Programmers Heaven C# School book covers the .NET framework and the C# language.
subscribe
Build Your Own ASP.NET 3.5 Web Site Using C# & VB, 3rd Edition - Free 219 Page Preview!
This comprehensive step-by-step guide will help get your database-driven ASP.NET web site up and running in no time..
subscribe
VB.Net Office Integration FAQ
VB.Net Office Integration FAQ
VB.Net Office Integration FAQ VB.Net Office Integration FAQ
VB.Net Office Integration FAQ
VB.Net Office Integration FAQ
VB.Net Office Integration FAQ VB.Net Office Integration FAQ VB.Net Office Integration FAQ VB.Net Office Integration FAQ VB.Net Office Integration FAQ VB.Net Office Integration FAQ VB.Net Office Integration FAQ
VB.Net Office Integration FAQ
VB.Net Office Integration FAQ
 
VB.Net Office Integration FAQ
VB.Net Office Integration FAQ
 
-->