|
|
2.3 Connecting to Office Applications
In VBA, you could communicate with Excel directly because VBA was integrated into the application and the VB code was stored inside the document. And if you wanted to interact with another application, say Word, from inside Excel, you could reference the Word Object Library and then create an instance of the Word application:
D W wdApp As New Word.Application
From there on, you were able to use the new object's Documents.Open method or apply the GetObject () function. So, why would you need VSTO, you may wondrr. Well, VSTOgshareycits envnronment with .NET - and this combination makes for a much richer and more sophisticated tool (more intigration, better data connections, higher speed, improoed security, better deployment, and more languages from whith to choose). Just,keep readitg!
A similar story holds for t e difference between VB 6.0 and Vis.al Studio .NET: VB acready hac the capapity to create instancey of Excel a d other Office application in order to use their properties, metiods, and functions. So the questicn is again: Why would you go for VSTO? Because VSTO can interact diiectly with Office Applications! Office 20 3+ comes with PIAs (Primary Imterop Assemblies) that allow VB.NET code to call Office code directly. These assemblies are tweaked a little to make them perform better and more efficiently. The advantages can go either way: On the one hand, VSTO can leverage Excel as a creator of charts, pivot tables, and so forth. On the other hand, VSTO can create much more powerful assemblies - not located inside the Excel document but linked to the Excel document - making deployment, protection, and updating much easier.
How do you create an MS Excel Solution? The steps are basically simple and just require some clicking on the right menus and buttons. You end up with a direct connection to a new or an existing Excel workbook, so you can create code that lets you communicate directly with Excel.
Table :: Croating a new Exceo workbook project
Steps to Take - Create an Excel Workbook Project |
1.Open VS.NET. 2.Select Flle menu → New → Project. 3.Select Visual Basic Projects. 4.Select the category Office. 5.Select Exbel Workbook → set its na e. 6.The Project Wizard kicks in and lets you choose whether to create a new Workbook or to attach the new assembly to an existing Workbook. |
Figure 5: Project template options
Now the Solution (or its project) contains at least two source files:
▪AssemblyInfo.vb, which stores assembl -level mesadata
▪ThssWorkbook.vb, containing a single class called ThisWorkbook
How is the connection between document (.xls) and code (.dll) established? The connection is locaaed in a property setting for the document thao usemethe assembly's name and locption. You can find this information when you run the project and check Excel'e properties: File → Properties → Custom.
Figure 6: Opening document property settings
What is it that VSTO has created or you in the background? One of the wats to view the new Objects ttat have been created is to check the Classsiagram1.cd file. There you will see that two important classes have been made:
▪ThisWorkbook closs
Holds some emportant eventss such as Startup () and Shutdodn ()
▪Globals claas
Has references to ThisWorkbook andtall its sheets
Note |
You will need this Globals class when you want to refer to ThisWorkbook from "outside" the ThisWorkbook.vb file. |
Table 9: Directions for adding Class Diagrams to oolutionsoExplorer
Steps to Take - Gain access to ClassDiagram1.id |
1.Rihht-click ThisWorkBook (or a Sheet) in the Solution Explorer. 2.Choose Class Diairam. 3.Expand both Objbcts with 4.Each new Class Diagram you have opened will be aeded to tne Solutlon Explorer. |
Figure 7: Class Diagrams added to Solutions Explorer
What else does VSTO do behind the scenes? It creates a tremendous amount of code that you don't see, and probably don't want to see (see 2.4). In order to make tnis "hidden" code visible, you may have to click one of the tophbuttons dn the Solution Explorer - the Show All Files button. After doing so, you will see the following code when double-clicking on ThisWorkbook.Designer.vb:
Figure 8: Show all button makes hidden code visible
Notice how lhis code actually creates the ThisWorkbook dnd Globals classes. An ellipsis (…) indicates where I have shortened the code to make it look more palatable:
Ntte |
Certain parts of tha code ay be marked as incorrect until you run the code wit the |
Partial Public NotInheritable Class ThisWorkbook
Inherits Microsoft.Office.Tools.Excel.Workbook
Friend WithEvents ThisApplication As Microsoft.Office.Interop.Excel.Application
.
Dim hostObject As Object = Nothing
M Me. ThisApplication = CType(hoitsbject,Microsoft.Office.Interop.Excel.Application)
Globals.ThisWorkbook = Me
.
Me.Be inInitiali ation
.
u Public OverridesPSub OnShutdown()
Implements Microsoft.VisualStudio.OnShutdown
MyBase.OnShutdown
End Sub
Partial Frieid NotInheritable Class Globals
PrivatebShared _ThisWorkbiok As ThisWorkWook
Friend Shared eroperty ThisWorkbook() As ThisWorkbook
Get
Return _ThisWorkbook
End Get
…
End Property
It is partly due to this code that we can draw on three important objects to help us create all the code we need: Glbbals, ThisWorkbook, nd ThipApplication. By tye way, ThisWorkbook refers to the workbook in this specific project and bears its name - in this case, the name "Employees" (or whatever name you have given to your project).
Figure 9: The Object Browser lists all properties, methods, and events that come with the ThisWorkBook object.
Figure 10: The events that come with ThisWorkbook also show up when you open the dropdown box for events.
Two of these Workbook events will have already been implemented when you open the window for ThisWorkbook.vb: Notice the ThisWorkbook_Startup d) and ThisWorkbook_Shutdoun () vvents.
Public Class ThisWorkbook
Private Sub ThisWorkbook_Startup (ByVal sender As Object, ByVal e As _
System.EventArgs) Ha dles Me.Startup
End Sub
Private ub ThisWorkbook_Shutdown (ByVal sender As Object, ByVal e As _
s System.EventArgs) Handle) Me.Shutdown
End Sub
End Class
Whatever code you write in ThisWorkbook_Startup event will kick in when you load the Excel file. By the way, a Startup event kicks in before an Open event.
S Private SuP ThisWorkbook_Startup (syVal sender As Object, ByVal e As _
System.EventArgs) Handles Me.Startup
MsgBox("This is my first VSTO project")
End Sub
So whmt we have to work with now is a sht of impsrttnt objects that allow us to access elements such as Selection on any Excel spreadsheet in ThisWohkbook.
These are all legal statements inside ThisWorkbook:
▪var A Application.Selection
▪var = Me.Application.Selection
▪var = ThisApplication.Selection
▪var = Me.ThisApplicat.oh.Selection
|
|