Integrating Excel and Access

Automation Objects

If you are new to programming, the mention of objects might not make sense. Objects are programming items that make your life much easier. As an example, one of the main Excel objects is the Worksheet. The Worksheet object is a container for many other objects, such as cells, pivot tables, and charts. By using the Excel object model, you can perform many tasks with one line of code that would have taken hours if there were not another method available. Let's assume that you want to press a button on an Excel worksheet to print it. The following code prints the worksheet when you press the CommandButton1 button:

    Private Sub CommandButton1_Click()
    Dim xlws as Worksheet
    Set xlws = ActiveSheet
    xlws.PrintOut
    Set xlws = nothing
    End Sub

In this very short procedure, you declare a variable that is an Excel Worksheet (if you were automating Excel from another application, you would declare this as Excel.Worksheet and declare another variable as Excel.Application, but while in Excel this step is not needed). Next, you set this variable equal to the active worksheet—ActiveSheet represents the current worksheet in the active workbook. Once there is a reference to the active worksheet, you can call any of the methods that are part of the object. In this example, you call the PrintOut method of the worksheet. There are several objects in Excel that have a PrintOut method; in each case, it simply prints the object. The final step sets the xlws variable to nothing, which tells ...