Referencing an Existing Object with GetObject

In a couple of instances you need to use the GetObject function:

• When you want to access an instance of an object that's already loaded, such as an Excel Application object.
• When you want to start an application and load an existing file in a single step.

If neither one of these two scenarios is applicable, you'd use the CreateObject function or the New keyword in a declaration statement.

The function GetObject has the syntax GetObject(pathname, class). The pathname argument specifies the pathname and filename of a file located on your computer or on a drive on your network. If the pathname isn't included in the filename, Visual Basic looks in the current folder of the application containing the code that's running the GetObject function. If pathname is omitted, the class argument is needed.

The class argument has the same syntax as the CreateObject function: ApplicationName.ObjectType. ApplicationName is the name of the application providing the object you want to create, and ObjectType is the type or class of object to create. If you specify the filename but not the class, Visual Basic determines which application to start and which object to activate based on the filename you provide.

The GetObject function is useful when you want to determine whether an application is already loaded. That way you can avoid loading a separate instance and perhaps confusing the user with multiple instances of an application.

1. In Excel, save the default workbook as Book1 in the Temp subfolder on your machine. For our purposes, we'll use C:\Temp. Later, you'll load the workbook in Excel by using the GetObject function.
2. Exit Excel and then start PowerPoint.
3. In the opening PowerPoint dialog box, select Blank Presentation, and then click OK. In the New Slide dialog box, choose any AutoLayout and click OK.
4. On the Tools menu, point to Macro and then click Visual Basic Editor on the submenu.
5. On the Tools menu, click References, select the Microsoft Word 9.0 Object Library from the list, and click OK.
6. On the Insert menu, click Module. In the inserted module, create a new procedure by typing Sub GetExistingApp and pressing ENTER.
7. Add the following procedure-level variable declaration to the GetExistingApp procedure:

Dim appWd As Word.Application

8. After the procedure-level variable declaration, add the following lines within the procedure:

On Error Resume Next Set appWd = GetObject(, "Word.Application")

You add the line On Error Resume Next before the Set statement to tell Visual Basic that if an error occurs when it's running a line of code it should continue to the next line. If you omit the first argument, pathname, preceding the comma in the Set statement above from the GetObject function, as shown here, the function returns a currently active object of the specified class type. In this case, the class is the Word application. If no object of the specified type exists, an error occurs.

9. Add the following If…Then condition block:

If appWd Is Nothing Then Set appWd = CreateObject("Word.Application") End If

The Visual Basic keyword Nothing is used with object variables and indicates whether an object variable has been assigned to an actual object in memory. When you declare an object variable, Visual Basic sets it to Nothing until you set it to an object by using the Set statement. The declaration of an object variable as a specific type tells Visual Basic what type of object that variable can be set to. In the preceding If…Then condition, if you didn't set the object variable appWd in the GetObject function, appWd still isn't assigned to a valid object and is set to Nothing instead. This means that an existing instance of the Word application wasn't found and you have to create a new one by using the CreateObject function.

10. Add the following as the last line in the procedure:

appWd.Visible = True

Setting the Visible property of the Word Application object to True displays the Word application window on the screen.

11. Place the cursor in the procedure and press F5 to run it. If Word isn't running, the procedure creates a new instance of the application. However, if Word is running, the GetObject function returns the current instance. If the instance of Word is running in the background, as it is when you use Word as your e-mail editor in Outlook, GetObject returns the instance of Word that's currently running in the background, which is not displayed to the user.
12. Exit PowerPoint without saving changes. Don't exit Word.

When you use the GetObject function to start an application with a file that's already loaded, it's essentially the same as first using the CreateObject function to create an instance of an application, and then using an Open method of the document collection of the specific application to open the file. The GetObject function reduces the number of lines of code needed to do the same thing.

1. Switch to Word and open a new document. Open the Visual Basic Editor. On the Tools menu, click References, select Microsoft Excel 9.0 Object Library from the list, and click OK.
2. Insert a new code module, and then add the following module-level variable declaration:

Dim m_wbBook1 As Excel.Workbook

By adding a module-level declaration, you ensure that Excel won't automatically close the workbook that will be loaded by the GetObject function once you run all the lines of code in the procedure that you create in the next step.

3. Create a new procedure by typing Sub UsingGetObject and pressing ENTER.

4. Type the following line within the procedure:

Set m_wbBook1 = GetObject("C:\Temp\Book1.xls")

You use the Set statement to assign the object returned by GetObject (an Excel Workbook object) to the module-level object variable m_wbBook1. The pathname argument specified in the GetObject function above assumes you have correctly saved an Excel file named Book1.xls to drive C in the Temp subfolder. If you saved to a different file location the Excel file in step 1 of "Access an Existing Application" earlier in this chapter, make sure that the pathname argument in the GetObject function reflects the correct location.

5. Type the following two lines after the Set statement:

m_wbBook1.Windows(1).Visible = True m_wbBook1.Application.Visible = True

When GetObject runs, it loads the Excel workbook invisibly. By default, each workbook contains at least one window. To make it visible, set its Visible property to True.

6. Place the cursor in the procedure and press F5 to run it. When this code runs, it starts the application associated with the specified pathname (Excel, in this case) and opens the object in the specified file (Book1.xls).