لطفا منتظر باشید ...
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. |
'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.
