provided by: 
Originally published at Internet.com ----------------------------------- 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.Applicatiion 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
Author: Paul Kimmel
Read article at Internet.com site