From Paul Kimmel's CodeGuru column, Paul Kimmel on VB/VB.NET.
Classes and components provide a granular way to reuse code. A class of applications referred to as application servers represents a way for a client application to reuse a whole server application, which is often a significant improvement over reusing some classes or components.
Microsoft has a whole suite of applications that are available for this purpose. Collectively these applications are referred to as Microsoft Office XP. Generally included in Office are Microsoft's Word, Excel, and PowerPoint, and developer versions include Access and FrontPage. All of these Office applications have the ability to be programmed with Visual Basic for Applications and an extensive object model exposed using OLE Automation, or just Automation.
Note: Microsoft Office XP's MS-Excel was used for the examples in this article.
To demonstrate how Automation works in Office XP I will be providing an Automation controller example that shows you how to control Microsoft Excel. In this scenario Excel is the code that we are reusing, and in this context Excel is referred to as an Automation server. The sample client application-the code that we are writing-plays the role of Automation controller.
The key to successfully implementing an Automation controller for any of the Office XP applications is to name what it is that you want to accomplish and then find the entities in the OLE Automation interface for that Office application and program to that interface. Each Office application has its own object model. For example, you can look up the MS-Excel object model in the Visual Basic for Applications editor's help, as illustrated in figure 1.
Click here for larger image
Figure 1: Excel's object model
For our purposes we will create an Automation controller that enables us to start an instance of Excel and dump some random data into the Worksheet. Clearly, this serves only as a basic introduction. However, once you understand the rudiments everything else is a matter of figuring out what part of the object model contains the data and capabilities that you need to get the job done.
Adding a Reference to Excel
Our example will implement an Automation controller in VB6 for MS-Excel. The first thing we need to do after we have identified our objective and created a project is to add a reference to the Excel Object library. To accomplish this select Project | References in Visual Basic 6 and check the Microsoft Excel 10.0 Object Library as shown in figure 2.
Click here for larger image
Figure 2: Add a reference to the Microsoft Excel 10.0 Object Library
It is worth noting that Automation has been around for quite a while in MS-Office. Thus if you are using an earlier version of Office then the version number of the Object Library will change. If you don't have Excel installed then code that is very similar to the example code in this article can be used to experiment with Automation in one of the other Microsoft Office applications, like Word.
Creating an Instance of Excel
After you have added a reference to the Excel object library you can easily control Excel from your client application. The key is to declare a variable of type Excel.Application and create a new instance of that type. You can perform this step when your client application is loaded-as shown in listing 1-or upon demand at some later time.
Note: If you are an experienced programmer then you know that you do not have to add a reference to Excel to create an Automation controller. You can declare a variable of type Object and use the CreateObject("Excel.Application") method call. This is referred to as a late binding and results in weaker code than does introducing the library and using specific types.
Listing 1: The Form_Load event handler creates an instance of the Excel.Application object.
Private Excel As Excel.Application
Private Sub Form_Load()
Set Excel = New Excel.Application
Set Workbook = Excel.Workbooks.Add
End Sub
From the listing you can see that the reference to an instance of Excel is declared outside of the method in the Form's scope (or class scope as an alternative). A new instance of the object is created and assigned to the variable named Excel. I also elected to create a new Workbook object in the Load event. You could create these objects at any time. After Load runs we can verify that Excel is running by opening the Windows Task Manager-see figure 3-and look for the Excel.exe executable in the task manager.
Click here for larger image
Figure 3: Microsoft Excel running as an application server, shown in the windows XP Task Manager
By default when you start Excel as an Automation server it is not visible. I added some code that demonstrates how to show or conceal the running Automation server (see listing 2).
Listing 2: One example that demonstrates how to manage revealing and concealing the running instance of Excel created in listing 1.
Private Sub MenuShow_Click()
If (MenuShow.Caption = "&Show") Then
MenuShow.Caption = "&Hide"
Workbook.Activate
Excel.Visible = True
Else
MenuShow.Caption = "&Show"
Excel.Visible = False
End If
End Sub
In our example the Visible state of the instance of Excel we created is associated closely with the value of a menu caption. If the caption is "&Show" then we toggle the Visible state to True, Active the Workbook, and toggle the menu caption to a suitable value, indicating that when we click the menu the next time the instance of Excel will be hidden. Any similar strategy for showing and hiding the server instance is sufficient. The key is to remember to examine or modify the Visible state of the Application object for this purpose.
Using the Active Workbook and Creating a Worksheet
You can interact with Excel in as many ways as are exposed by the object model. MS-Excel is workbook and worksheet oriented. A file is referred to as a workbook and a single spreadsheet is referred to as a worksheet. For our purposes we need one workbook and one worksheet. Listing 1 demonstrated how to add a new Workbook and the next listing (listing 3) demonstrates how to add data to the active Worksheet.
Listing 3: Add data to the active Worksheet in Excel.
Private Row As Integer
Private Sub MenuAdd_Click()
Dim Worksheet As Worksheet
Set Worksheet = Workbook.ActiveSheet
Row = Row + 1
Worksheet.Rows.Cells(Row, 1) = Text1.Text
End Sub
I shortened the code that adds data to our Worksheet by declaring a temporary variable. (I wouldn't declare temporary variables in production code, as it adds unnecessary lines of code.) A private field named Row is used to store an internal counter and the text in Text1 is added to column 1 and whatever the current value of Row is.
The Automation controller is a very simplistic application that simply dumps data into a Worksheet into the first column. However, at this point you have all of the basic rudiments that get you to data at the cell level. The final step is to demonstrate how we can clean up our work area.
Cleaning up Your Work Area
For our purposes the Worksheet represents a temporary work area. The data put into the Worksheet is not persistent; that is, it is not intended to outlive the current run of the Automation controller application. Listing 4 demonstrates how we can close the active Workbook, discarding the temporary data and shut down the instance of Excel.
Listing 4: Discarding changes to the active Workbook and shutting down the Automation server.
Private Sub Form_Unload(Cancel As Integer)
Call Workbook.Close(False)
Excel.Quit
Set Excel = Nothing
End Sub
Summary
The most difficult challenge when implementing an Automation controller application is to learn what the Automation server's object model has to offer. After you learn how to start and stop instances of the server all you need to do is know the name of the objects that contain the data you are interested in. The objects in the object model will be different for each server. Fortunately, the applications in Office XP represent well-understood solution domains and as a result are reasonably intuitive to understand.
It makes sense that there are objects in the object model that match objects you use when using Excel as a client application. For example, it is reasonable to expect that there is a representation of a Workbook, Worksheet, columns, rows, and cells. However, there is much more. If you can identify the entity you are looking for in the Excel client then it will be much easier to find in the Excel Automation object model. The same relative contextual relationships should be supported too. For example, Workbooks should have something similar to a collection of Worksheets, and Worksheets should have collections of columns, rows, and cells.
Office XP and OLE Automation represent a powerful combination for incorporating spreadsheets, word processors, database and presentation management into your applications. Many other Microsoft applications such as FrontPage and Visual SourceSafe expose an Automation model, as well as applications offered by other vendors.
About the Author
Paul Kimmel is a freelance writer for Developer.com and CodeGuru.com. Look for his most recent book, Visual Basic .Net Unleashed, at a bookstore near you. Also look for his upcoming book "Advanced C# Programming" from Osborne/McGraw-Hill. Paul Kimmel is available to help design and build your .NET solutions and can be contacted at pkimmel@softconcepts.com.
# # #
IE 7
Firefox 2.0
