Using the CreateObject and GetObject Functions

Article
09/14/2006

You can use the Set statement with the CreateObject and GetObject functions to create a top-level object variable that represents a Microsoft® Office application. These functions should be used only in those situations where the New keyword does not provide the functionality you require.

You use the CreateObject function to create a top-level object variable that represents an Office application in the following two situations:

The Office application for which you want to create an Application object is not available on the local computer but is available on some other computer on your network. For example, you can run Microsoft® Visual Basic® for Applications (VBA) code that prints reports from a Microsoft® Access database that is located on a network server even though Access is not installed on the computer from which the code is run. If Access is installed on the network server, you can create an Access Application object that runs on the server by specifying the name of the server in the CreateObject function's optional servername argument. For example:
```
Dim objAcApp As Object
Set objAcApp = CreateObject("Access.Application", "MyServer1")
```
The servername argument of the CreateObject function is the same as the machine name portion of a share name. Therefore, for a share named \\MyServer1\Public, the servername argument is "MyServer1".

To successfully run an Office application as a remote server, you must configure Distributed Component Object Model (DCOM) settings on the computer that is acting as a server, and also possibly on the client computers. To configure DCOM, run the Distributed COM Configuration utility (Dcomcnfg.exe) from the Run box on the Startup menu. For more information about configuring DCOM, search the Microsoft Technical Support Web site (https://support.microsoft.com) for "Configure DCOM."
The CreateObject function is also useful when you are not sure if the Office application you want to automate will be installed on the computer that runs your code. The following example illustrates how to use the CreateObject function to make sure an application is available for automation:
```
Sub CreateObjectExample()
   Dim objApp As Object

   Const ERR_APP_NOTFOUND As Long = 429

   On Error Resume Next

   ' Attempt to create late-bound instance of Access application.
   Set objApp = CreateObject("Access.Application")
   If Err = ERR_APP_NOTFOUND Then
      MsgBox "Access isn't installed on this computer. " _
         & "Could not automate Access."
      Exit Sub
   End If
   With objApp
      ' Code to automate Access here.
      .Quit
   End With
   Set objApp = Nothing
End Sub
```
Note The Application object variable in this procedure is declared by using the Object data type and is late-bound to the application by using the CreateObject function. The code must be written this way, because, if an object variable is declared as a specific Application object type and that application is not present, the code will break.

Note The CreateObject function also must be used to work with objects from any automation component from script. This is because scripting has no method of establishing references to type libraries to support early binding. However, for security reasons, you wouldn't typically use the CreateObject function from script to create an instance of an Office application.

You can use the GetObject function in these situations:

You must create a reference to a running instance of an application. For example, the following code creates a reference to the running instance of Access. If Access is not running when the code executes, a Set statement is used to create an object variable for the Access Application object.
```
Sub GetObjectExample()
   Dim acApp As Access.Application

   Const ERR_APP_NOTRUNNING As Long = 429

   On Error Resume Next

   ' Attempt to reference running instance of Access.
   Set acApp = GetObject(, "Access.Application")
   ' If Access isn't running, create a new instance.
   If Err = ERR_APP_NOTRUNNING Then
      Set acApp = New Access.Application
   End If
   With acApp
      ' Code to automate Access here.
   End With
   ' If instance of Access was started by this code,
   ' shut down application.
   If Not acApp.UserControl Then
      acApp.Quit
      Set acApp = Nothing
   End If
End Sub
```
If multiple instances of the application you want to automate are running, there is no way to guarantee which instance the GetObject function will return. For example, if two sessions of Access are running and you use the GetObject function to retrieve an instance of Access from code running in Excel, there's no way to guarantee which instance of Access will be used.

There are few circumstances where it makes sense to use the GetObject function to return a reference to a running instance of an Office application. If a user opened the running instance, you would rarely want your code to be manipulating the objects in that instance of the application. However, when you use the Shell function to start an Access application (so that you can supply a password and workgroup information file to open a secured database), it does make sense to work with the running instance of Access by using the GetObject function to return a reference to the instance of Access that you started.

You also use the GetObject function when you must open an Office file and return a reference to the host application object at the same time. The following example shows how to use the GetObject function to open an Access database from disk and return a reference to the Access application. When HTML is passed as the value for the lngRptType argument, the procedure creates a Web page from a report and displays that page in a Web browser.

Function GetReport(Optional lngRptType As opgRptType) As Boolean
   ' This function outputs a report in the format specified by
   ' the optional lngRptType argument. If lngRptType is specified,
   ' the report is automatically opened in the corresponding
   ' application.
   ' lngRptType can be any of the following constants defined
   ' by Enum opgRptType in the Declarations section of this
   ' module:
   ' XLS = output to Excel
   ' RTF = output to Rich Text Format
   ' SNAPSHOT = output to Access snapshot report format
   ' HTML = output to HTML
   ' If lngRptType is not specified, the report is opened in
   ' Access and displayed in Print Preview.

   Dim acApp              As Access.Application
   Dim strReportName      As String
   Dim strReportPath      As String

      Const SAMPLE_DB_PATH As String = "c:\program files\" _
      & "microsoft office\office\samples\northwind.mdb"

   strReportName = "Alphabetical List of Products"
   strReportPath = "c:\my documents\"
   ' Start Access and open Northwind Traders database.
      Set acApp = GetObject(SAMPLE_DB_PATH, "Access.Application")
   With acApp
      ' Output or display in specified format.
      With .DoCmd
         Select Case lngRptType
            Case XLS
            .OutputTo acOutputReport, strReportName, _
               acFormatXLS, strReportPath & "autoxls.xls", True
            Case RTF
            .OutputTo acOutputReport, strReportName, _
               acFormatRTF, strReportPath & "autortf.rtf", True
            ' Snapshot Viewer must be installed to view snapshot
            ' output.
            Case SNAPSHOT
            .OutputTo acOutputReport, strReportName, _
               acFormatSNP, strReportPath & "autosnap.snp", True
            Case HTML
            .OutputTo acOutputReport, strReportName, _
               acFormatHTML, strReportPath & "autohtml.htm", _
               True, "NWINDTEM.HTM"
            Case Else
            acApp.Visible = True
            .OpenReport strReportName, acViewPreview
         End Select
      End With
      ' Close Access if this code created current instance.
      If Not .UserControl Then
         acApp.Quit
         Set acApp = Nothing
      End If
   End With
End Function

Share via

Using the CreateObject and GetObject Functions

See Also

Additional resources