Alison Balteramp;#039;s Mastering Microsoft Office Access 1002003 [Electronic resources]

Controlling Excel from Access

Before you attempt to talk to Excel, you must understand its object model. Excel gives you an excellent overview of the Excel object model. You can find this model by searching for "object model" in Excel Help. Each object in the model has hypertext links that enable you to obtain specific help on the object, its properties, and its methods.

After you launch Excel, it launches as a hidden window with a Visible property of False. Destroying the Excel object variable does not cause Excel to terminate. To make things even more complicated, each time you use the New keyword within the Dim or Set statement, a new instance of Excel is launched. This means that it is possible for numerous hidden copies of Excel to be running on a user's machine, which can lead to serious resource problems. If you want to use a running instance of Excel, you can omit the New keyword. This has its disadvantages as well. Let's say, for example, that the user of your application has created a large spreadsheet and has not saved it recently. Your application uses an existing instance of Excel, creates a new workbook, prints, and then exits without saving. You might find that your user is very angry about the loss of his important work. For this reason, I have found it preferable to suffer the potential resource costs and create my own instance of Excel. If you want to launch Excel invisibly, do your work, and get out, make sure that you terminate Excel upon completion of your code.

Before you execute code that relies on a running copy of Excel, it is important to ascertain that Excel launched successfully. The function shown in Listing 22.3 attempts to launch Excel. If it is successful, True is returned from the function. Otherwise, False is returned from the function.

Listing 22.3 The CreateExcelObj Subroutine

Function CreateExcelObj() As Boolean 'Invoke error handling On Error GoTo CreateExcelObj_Err 'Assume a False return value CreateExcelObj = False 'Attempt to launch Excel Set gobjExcel = New Excel.Application 'If Excel launches successfully, return True CreateExcelObj = True CreateExcelObj_Exit: Exit Function CreateExcelObj_Err: 'If an error occurs, display a message and return False MsgBox "Couldn't Launch Excel!!", vbCritical, "Warning!!" CreateExcelObj = False Resume CreateExcelObj_Exit End Function

The routine begins by invoking error handling. It initializes the return value for the function to False. The routine then attempts to launch Excel. If it is successful, the public variable gobjExcel references the running instance of Excel, and the function returns True. If an error occurs, the routine executes the code within the error handler. The code displays a message, and sets the return value for the function to False.

NOTE

You can find this code and most other examples used in this chapter in the CHAP22EX.MDB database located on your sample code CD-ROM. This routine is located in basUtils.

CAUTION

To take advantage of the exciting world of automation, you must install all automation server applications on the user's machine, and the user must possess a full license to the server applications. In fact, you will be unable to compile and run the examples contained in the sample database for this chapter unless you have the server applications loaded on your development machine.

The CreatExcelObj function is called from the Click event of the cmdFillExcel command button on the frmSimpleExcel form. The application attempts to talk to the Excel object only if the return value of the function is True, indicating that Excel was loaded successfully.

Private Sub cmdFillExcel_Click() 'If Excel is launched successfully, 'execute the FillCells routine If CreateExcelObj() Then Call FillCells End If End Sub

If Excel launches successfully, the FillCells subroutine executes, as shown in Listing 22.4.

Listing 22.4 The FillCells Subroutine

Sub FillCells() 'Declare an Excel Worksheet object Dim objWS As Excel.Worksheet 'Invoke Error Handling On Error GoTo FillCells_Err With gobjExcel 'Add a Workbook to the Workbooks collection .Workbooks.Add 'Point the Worksheet object at the active sheet Set objWS = gobjExcel.ActiveSheet 'Set the value of various cells in the sheet With objWS .Cells(1, 1).Value = "Schedule" .Cells(2, 1).Value = "Day" .Cells(2, 2).Value = "Tasks" .Cells(3, 1).Value = 1 .Cells(4, 1).Value = 2 End With 'Select A3 through A4 .Range("A3:A4").Select 'Use the AutoFill method to fill the range of A3 'through A33 with numeric values .Selection.AutoFill gobjExcel.Range("A3:A33") 'Select cell A1 .Range("A1").Select 'Make Excel visible .Visible = True End With FillCells_Exit: Exit Sub FillCells_Err: 'If the Excel object is still set, quit Excel and destroy 'the object variable If Not gobjExcel Is Nothing Then gobjExcel.Quit Set gobjExcel = Nothing End If Resume FillCells_Exit End Sub

You can find this relatively simple routine in frmSimpleExcel, which is part of the CHAP22EX.MDB database file (see Figure 22.4). It begins by using the Add method on the Workbooks collection of the Excel object to add a new workbook to the instance of Excel. It then uses Set objWS = gobjExcel.ActiveSheet to provide a shortcut for talking to the active sheet in the new Excel workbook. Using the objWS object reference, it modifies the values of several cells. It then uses the AutoFill method to quickly fill a range of cells with data. It returns the cursor to cell A1, and the Excel object is made visible. You might wonder what the AutoFill method is; it automates the process of filling a range of cells with a pattern of data. Figure 22.5 shows the results. I mention it here not just to tell you what it is, but also to illustrate an important point: You must know the product you are automating and its capabilities. If you are not familiar with the product from a user's perspective, you will find it extremely difficult to work with the product using automation.

Figure 22.4. The form used to launch, communicate with, and close Excel.

Figure 22.5. Using the AutoFill method to populate a range of cells.