Note that the excel server will not terminate by

This preview shows page 465 - 468 out of 490 pages.

Note that the Excel server will not terminate by itself, even if the XLApp variable is destroyed. If we have made Excel visible, then we can close it programmatically, as well as from the user interface in the usual way (choosing Exit from the File menu, for instance). But if the Excel server is invisible, it must be closed using the Quit method: XlApp.Quit (If we fail to terminate the Excel server, it will remain running invisibly, taking up system resources, until the PC is restarted.) E.2.1 An Alternative Approach The approach described for programming Excel from within another application is the preferred approach, since it is the most efficient. However, there is an alternative approach that you may encounter, so let us discuss it briefly. As before, we assume that a reference has been set to the Excel object library. E.2.1.1 The CreateObject function
452 The CreateObject function can start an Automation server, create an object, and assign it to an object variable. Thus, we can write: Dim XLApp as Excel.Application Set XLApp = CreateObject("Excel.Application") This approach will execute more slowly than the previous approach using the New keyword, but it is perfectly valid. As before, we must remember to close Excel using the Quit method (or through normal means if Excel is visible). E.2.1.2 The GetObject function If Excel is already running, the CreateObject function will start a second copy of the Excel server. To use the currently running version, we can use the GetObject function to set a reference to the Application object of a running copy of Excel. This is done as follows: Set XLApp = GetObject(, "Excel.Application") (The first parameter of GetObject is not used here.) One of the problems with using GetObject is that it will produce an error if Excel is not running. Thus, we need some code that will start Excel if it is not running or use the existing copy of Excel if it is running. The trick to this is to know that if GetObject fails to find a running copy of Excel, then it issues error number 429 ("ActiveX component can't create object"). Thus, the following code does the trick: Dim XLApp As Excel.Application On Error Resume Next ' Try to get reference to running Excel Set XLApp = GetObject(, "Excel.Application") If Err.Number = 429 Then ' If error 429, then create new object Set XLApp = CreateObject("Excel.Application") ElseIf Err.Number <> 0 Then ' If another type of error, report it MsgBox "Error: " & Err.Description Exit Sub End If E.2.1.3 No object library reference We have been assuming that the client application has a reference to the server's object library. However, it is still possible for a client application (an Automation client) to program the objects of an Automation server (such as Excel) without such a reference. Under these circumstances, we cannot refer to objects by name in code, since the client will not understand these names. Instead, we must use the generic Object data type, as in the following code: Dim XLApp As Object Dim wb As Object
453 Set XLApp = CreateObject("Excel.Application") XLApp.Visible = True Set wb = XLApp.Workbooks.Add

  • Left Quote Icon

    Student Picture

  • Left Quote Icon

    Student Picture

  • Left Quote Icon

    Student Picture