Driving Excel from Other Office Programs

In the previous sections, you saw how Excel can be used to drive other Microsoft applications such as Word or Outlook.

By the same token, these other applications can also be used to control Excel. For example, you can write a macro in Word or Access that populates an Excel spreadsheet and then saves it. Excel does not actually have to be running, and the spreadsheet itself does not have to be seen on screen. If Excel is running, then a new workbook will be created, but you do have the option to set the Visible property for your virtual application to False so that the spreadsheet never appears. When running this type of application it is quite entertaining to set the Visible property to True and watch the numbers appearing in the spreadsheet as if some ghostly operator were adding them. I have seen it impress a number of senior managers, but the problem is that if someone touches the keyboard or even closes your spreadsheet, it can cause unpredictable results. If you set the Visible property to False, there is nothing the user can do to upset the procedure, and they need not even be aware that it is running.

The example I am going to use creates an Excel spreadsheet from within a Word document. VBA works in exactly the same in all Microsoft Office applications, except that the object model is obviously too different from application to application to take into the functionality of the application.

Once you load Word, you need to enter the VBA code window. This works exactly the same as in Excel: press alt-f11 . The code window has exactly the same functionality as the one that you are used to in Excel.

When you used a different application from Excel, you had to put in a reference to the Object Library file first. We have to do the same thing in Word by putting in a reference to the Excel Object Library in order to tell Word how to find the Excel object model.

You use Tools References from the Visual Basic Editor menu as before, but this time you select the Excel Object Library and check the box next to it, as shown in Figure 14-3.

Figure 14-3: Selecting the Excel Library file

This now gives your code all it needs to manipulate Excel. Insert a module by selecting Insert Module from the Code menu, and then enter the following code:

 Sub Test_Excel() 
 Dim oEapp As Excel.Application 
 Set oEapp = CreateObject("Excel.Application") 
 Dim oWBook As Workbook, oWSheet As Worksheet 
 Set oWBook = oEapp.Workbooks.Add 
 Set oWSheet = oWBook.Worksheets(1) 
 oWSheet.Range("a1").Value = "My test Excel spreadsheet" 
 oWBook.SaveAs ("c:\TestExcel.xls") 
 oWBook.Close 
 oEapp.Quit 
 Set oWSheet = Nothing 
 Set oWBook = Nothing 
 End Sub 

When this is run, it will create a spreadsheet called C:\TestExcel.xls and cell A1 on sheet1 will have the value ‚“My test Excel spreadsheet. ‚½ The code creates a variable oEApp to hold the Excel application object. It then sets that variable as an Excel object. Variables oWBook and oWSheet are then created to represent a workbook and a worksheet, and the workbook is set to a new workbook, which is added into the Workbooks collection. The Worksheet object is set to the first worksheet in the Worksheets collection (there must always be at least one worksheet in a workbook).

Cell A1 on the first sheet is set to read ‚“My test Excel spreadsheet, ‚½ and the Workbook is then set as ‚“C:\TestExcel.xls. ‚½ The workbook is then closed by calling the Close method. This is the same as selecting File Close from the menu in Excel. Next the Application is closed, which is the same as selecting File Exit from the Excel menu. Finally, the workbook and worksheet variables are set to Nothing in order to release all memory used by the Excel objects.

As explained previously, it is very important to be sure to properly close and set to Nothing any objects created within VBA code. Otherwise, the memory consumed is left hanging and is unable to be used by other applications later. This can cause problems for users when they exit your application because an application was not properly terminated and is holding memory that may be needed.

I hope this has given you a taste of the immense power of the VBA language and how you can manipulate other Microsoft applications from within it.