Opening Objects

Use the GetObject function to open an existing object. For example, you can use this function to open Microsoft Excel workbooks but not Word documents. The syntax for the GetObject function is as follows:

Dim objXL As Object
Set objXL = GetObject("PathOfFile", "ApplicationName.ObjectType")

When you use GetObject to open a file, the second argument, ApplicationName. ObjectType, is optional. But if you use it, use either Excel.Sheet or Excel.Chart; you can't open a file with the Excel.Application class. You can, however, use this class to determine whether Microsoft Excel is already open. (See the section titled The CreatePIVOTTable Function presented later for more information.) For example, the following Access Basic code opens the workbook created by the MakeXLObject function.

Note If you run GetXLObject with the Immediate Window open, it displays the first five rows of data from this sheet.

Function GetXLObject ()
    Dim objXLOpen As Object
    Dim intRow As Integer
    Dim intColumn As Integer

'Open CSTMRPT.XLS in its normal, hidden state.
    Set objXLOpen = GetObject("C:\CSTMRPT.XLS", 
        "Excel.Sheet")
    For intRow = 1 To 5
        For intColumn = 1 To 3
            Debug.Print objXL.Cells(intRow, intColumn).Value
        Next intColumn
    Next intRow
    objXLOpen.Parent.Saved = True
    objXLOpen.Application.[Quit]
    Set objXLOpen = Nothing
End Function

To open a workbook and make it visible, you have to put the workbook's name in square brackets, and declare the object variable globally. For example, the following code opens CSTMRPT.XLS and leaves users there. However, when you close Access, the Excel.Sheet object closes too.

Function OpenXLObject ()
    Dim intRow As Integer
    Dim intColumn As Integer

'Open CSTMRPT.XLS in its normal, hidden state.
    Set objXL = GetObject("C:\[CSTMRPT.XLS]", "Excel.Sheet")
    objXL.Application.Visible = True
End Function

The code shown above is quite similar to the code shown below. However, when you close Access, the following code leaves you in CSTMRPT.XLS.

Function OpenXLSheet ()
    Set objXL = CreateObject("Excel.Application")
    objXL.Workbooks.Open "C:\CSTMRPT.XLS"
    objXL.Application.Visible = True
End Function