Microsoft Office Tutorials and References
In Depth Information
E.2.1.2 The GetObject function
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
Search JabSto ::




Custom Search