Creating Objects

You can use CreateObject to start an application as an OLE automation server and create an instance of that application object's class. An object is an instance, or an example, of a particular class. (See the sidebar That Obscure Object of Desire in Chapter 11 of Developing Applications with Microsoft Office for more information on classes.) The syntax for the CreateObject function is as follows:

Dim objVariable As Object
Set objVariable = CreateObject("ApplicationName.ObjectType")

ApplicationName.ObjectType is the class of object that you're creating. For example, the following code creates a Microsoft Excel application object (another instance of Microsoft Excel running) in Access Basic, VBA for Microsoft Excel, or Visual Basic:

Function MakeAnotherXL()
  Dim objXL As Object
  Set objXL = CreateObject("Excel.Application")
  objXL.Visible = True
End Function

In this example, the line objXL.Visible = True unhides the instance of Microsoft Excel launched by the CreateObject function and leaves users there. Unless you're using OLE automation to deliver data to users in Microsoft Excel—for example, by generating a report in Microsoft Excel—you should leave the Microsoft Excel object hidden (its normal state) and close the Microsoft Excel object when you're done with it. Not only will your integrated business applications run faster, but closing OLE automation objects frees up system resources.

Here's sample Access Basic code that creates a Word application object. To leave users in Word, declare the object variable (in this case, objWord) as a global variable (When you declare variables local to a procedure, they're no longer valid at the procedure's end). The following code, which declares objWord in the declarations section, leaves users in Word.

Function MakeWordAppObject()
    Set objWord = CreateObject("Word.Basic")
End Function

The following table shows the application names and object types for the two OLE automation servers included with Office--Microsoft Excel and Word. Although Word exposes only one class of objects to OLE automation (the Word.Basic class) Microsoft Excel exposes three: Excel.Application, Excel.Sheet, and Excel.Chart.

Application Names and Object Types for OLE Automation Servers

Application Name	Object Type
Microsoft Excel	Application
	Sheet
	Chart
Word	Basic

Each of these classes behaves somewhat differently, and each is suited to particular purposes. Here's a brief summary of when to use each of the classes that Microsoft Excel exposes to OLE automation:

Use the Excel.Application class when you want to present data in Microsoft Excel and leave users there. If you set the Microsoft Excel application object's property to Visible, you can exit the application that created the Microsoft Excel object, such as Access, without exiting Microsoft Excel. When you use Microsoft Excel's other two classes, the object persists only as long as the creating application remains open. When you use the Excel.Application class, you can use VBA for Microsoft Excel's methods and properties to create and populate sheets. (The sample application PIVOT presented later includes an example of this.)
Use the Excel.Sheet class when you want to calculate data in Microsoft Excel behind the scenes, or when you want to populate a sheet with data for later use. Even if you make this object visible, it closes when you exit the application that created it (such as Access). (See the MakeXLObject and the ComputeVDB functions presented later for sample code.)
Use the Excel.Chart class when you want to create a chart for later use. Even if you make this object visible, it closes when you exit the application that created it (such as Access).

Sample Code: The MakeXLObject Function

The MakeXLObject() function shown below uses the Excel.Sheet class to create an instance of an Excel worksheet. It then populates it with data from an Access database and saves the sheet for later use. "Later use" can be use by the Access application that created it, or by users themselves. This function, written in Access Basic, saves the worksheet as a standard Microsoft Excel workbook (CSTMRPT.XLS) which contains a single sheet. Here are a few things worth noting about the code (which starts on the next page):

All the VBA for Microsoft Excel code is non-bold, so it's easy to spot.

When you launch Microsoft Excel through OLE automation, it's completely invisible. In fact, it doesn't even appear on the Task List. But if you monitor your free resources, it's obvious that it is open. To close Microsoft Excel, use the following code:

objVariable.Application.[Quit]

Set objVariable = Nothing

Put Quit in the preceding code in square brackets so that the OLE automation client that's creating the Microsoft Excel object, such as Access, associates it with Microsoft Excel and not with the client's own application object. Also, although it's not strictly necessary to use the second line (and the keyword Nothing is undocumented in Access Basic), it's good practice. You'll see why when you experiment with making the various classes of Microsoft Excel objects visible. For example, to make the Microsoft Excel.Sheet class visible, you have to declare the object variable as a global variable. Of course, if you do this, the object variable remains in memory at the end of the function... unless you set it to Nothing.

The MakeXLObject function declares the variable objXL globally by putting it in the declarations section. To leave users in the newly created worksheet, comment out the two lines that close the object and then add the following line:

objXL.Application.Visible = True

When you test this, notice that the title of the sheet isn't "CSTMRPT.XLS," but "Object." When you close Access, this object closes too, although the Microsoft Excel application itself remains open. (See the section titled Getting Objects presented later for more information).

The following line of code prevents Microsoft Excel from prompting users if the filename used for this report already exists:

If Dir$(strSavedReport) <> "" Then Kill strSavedReport

You need this line because the usual method for suppressing such prompts--Application.DisplayAlerts = False--doesn't work under OLE automation, and the SendKeys statement works only when Microsoft Excel is visible.

Function MakeXLObject ()
    On Error GoTo ErrorMakeXLObject

    Dim dbSolution As Database
    Dim tdfCustomers As TableDef
    Dim rstCustomers As Recordset
    Dim intRow As Integer
    Dim intColumn As Integer
    Dim strSavedReport As String

    Set dbSolution = DBEngine(0)(0)
    Set tdfCustomers = dbSolution.TableDefs("Customers")
    Set rstCustomers = dbSolution.OpenRecordset("Investment")

'Set full path and name for saved Microsoft Excel object (i.e., report).
    strSavedReport = "C:\CSTMRPT.XLS"

'objXL is dimmed in Declarations section because otherwise
'you can't display the Sheet object and leave users in it.
'Create a Microsoft Excel worksheet object.
    Set objXL = CreateObject("Excel.Sheet")

'Display hourglass.
    DoCmd Hourglass True

'Add column headings from table.
    intRow = 1
    For intColumn = 1 To tdfCustomers.Fields.Count
        objXL.Cells(intRow, intColumn).Value = 
            tdfCustomers.Fields(intColumn - 1).Name
    Next intColumn

'Select first record
    intRow = 2
    rstCustomers.MoveFirst
    Do Until rstCustomers.EOF
        For intColumn = 1 To rstCustomers.Fields.Count
            objXL.Cells(intRow, intColumn).Value = 
                rstCustomers.Fields(intColumn - 1)
        Next intColumn

'Select other records and increment row counter
        rstCustomers.MoveNext
        intRow = intRow + 1
    Loop

'Check whether CSTMRPT.XLS exists, and kill it if it does;
'this prevents prompt to users to overwrite existing file.
'Excel's usual method, Application.DisplayAlerts = False,
'doesn't work under OLE automation. Also, SendKeys only works
'when the Microsoft Excel's application object is visible.
    If Dir$(strSavedReport) <> "" Then Kill strSavedReport

'Best fit Microsoft Excel columns and save sheet.
    objXL.Cells(1, 1).CurrentRegion.EntireColumn.AutoFit
    objXL.SaveAs strSavedReport

'Exit Microsoft Excel and clear object variable.
    objXL.application.[Quit]
    Set objXL = Nothing

ExitMakeXLObject:
    MsgBox "Saved Microsoft Excel object as C:\CSTMRPT.XLS.", 64,
        "MakeXLObject"
'Turn off hourglass.
    DoCmd Hourglass False
    Exit Function

ErrorMakeXLObject:
    If Err <> 0 Then
        MsgBox "An error occurred in MakeObject. " & Chr$(13) &
            "ERROR = " & Str$(Err), 48, "MakeObject"
    End If
    Resume ExitMakeXLObject
End Function

Sample Code: The ComputeVDB Function

The ComputeVDB function, shown below, uses Microsoft Excel to compute variable declining balance depreciation on assets tracked in an Access database. (This calculation isn't available in Access.) Although this function displays the depreciation in a message box, you can just as easily add this number to a report. Note that the basic idea for populating a Microsoft Excel sheet with Access data is essentially the same as in the MakeXLObject function described above. You can use this same idea to populate a sheet for charting Access data with a Microsoft Excel chart. Once again, all the VBA for Microsoft Excel code is non-bold, so it's easy to spot.

Function ComputeVDB ()
  On Error GoTo DepreciationError

  Dim dbSolution As Database
  Dim rstAsset As Recordset
  Dim intNumFields As Integer
  Dim i As Integer
  Dim strMsg As String

  Set dbSolution = DBEngine(0)(0)
  Set rstAsset = dbSolution.OpenRecordset("Assets")

'objXL is dimmed in Declarations so that you can display the
'Sheet object and leave users in it. Create worksheet object.
  Set objXL = CreateObject("Excel.Sheet")

'Display hourglass.
  DoCmd Hourglass True

'Read values from the Assets table into the Microsoft Excel worksheet.
  rstAsset.MoveFirst
  For i = 2 To rstAsset.Fields.count - 1
    objXL.Cells(i - 1, 1).Value = rstAsset.Fields(i)
  Next i

'Set the year for which to compute depreciation.
  objXL.Cells(1, 2).Value = 3

'Compute the depreciation and display it.
  objXL.Cells(3, 2).Formula = "=VDB(A1, A2, A3, B1-1, B1, A4)"
  strMsg = "The third year depreciation on the first asset "
  strMsg = strMsg & "recorded in the table ""Assets"" is "
  strMsg = strMsg & Format$(objXL.Cells(3, 2).Value, "Currency")
  MsgBox strMsg, 64, "Compute Depreciation"
    
'Exit Microsoft Excel and clear object variable.
  objXL.Application.[Quit]
  Set objXL = Nothing

DepreciationExit:
'Turn off hourglass
  DoCmd Hourglass False
  Exit Function

DepreciationError:
    If Err <> 0 Then
        MsgBox "An unidentified error occurred. " & Chr$(13) &
            "ERROR = " & Str$(Err), 48, "Compute Depreciation"
    End If
    Resume DepreciationExit
End Function