ACC2000: Using Automation to Create and Manipulate an Excel Workbook

Create a New Microsoft Excel Worksheet

1. Create a new folder on drive C named Examples.
2. Start Microsoft Access and open the sample database Northwind.mdb.
3. Create a new module and type the following function in the Module window:

   Function ExcelTest()
      Dim xlobject As Object, xlsheet As Object
   
      Set xlobject = CreateObject("excel.sheet.5")
      Set xlsheet = xlobject.Application.activeworkbook.sheets("sheet1")
   
      With xlsheet
         .range("a1").Value = CDbl(InputBox("Enter 1st Number", _
            "Excel Example"))
            .range("b1").Value = CDbl(InputBox("Enter 2nd Number", _
            "Excel Example"))
   
            .range("c1").Value = xlsheet.range("a1").Value * _
            xlsheet.range("b1").Value
   
      End With
   
      xlsheet.Parent.SaveAs "c:\examples\xltest.xls"
      xlobject.Application.Quit
      Set xlobject = Nothing
   
   End Function
   

4. Save the module as basExcel.
5. Press CTRL+G to open the Immediate window, type the following statement, and then press ENTER:
   ExcelTest
6. At the Enter 1st number prompt, enter 2. At the Enter 2nd number prompt, enter 3.
7. Start Microsoft Excel and open the Xltest.xls workbook in the Examples folder on drive C to view the results. Notice the numbers 2, 3 and the product of their multiplication, 6.

Back to the top

Create a New Microsoft Excel Worksheet by Using a Bound Control on a Form

1. Create a new table with the following properties:

      Table: tblTestExcel
      ----------------------
      Field Name: MyOleField
      Data Type : OLE Object

2. Create a new form based on the tblTestExcel table by using the AutoForm: Columnar Wizard.
3. Switch to Design view, and then add two text boxes to the form named Text1 and Text2. Place them below the MyOleField control.
4. Add a command button with the following properties to the form, and place it below the MyOleField control:

      Name: cmdMyButton
      Caption: My Button
      OnClick: [Event Procedure]

5. Set the command button's OnClick property to the following event procedure:

   Dim xlobject As Object, xlsheet As Object
   With myOleField
      .Class = "excel.sheet.8"
      .OLETypeAllowed = acOLEEmbedded
      .Action = acOLECreateEmbed
      .Verb = acOLEVerbInPlaceUIActivate
      .Action = acOLEActivate
   End With
   
   Set xlobject = Me!myOleField.Object.Application
   Set xlsheet = xlobject.Application.activeworkbook.sheets("sheet1")
   
   With xlsheet
      .range("a1").Value = CDbl(Me!Text1)
      .range("b1").Value = CDbl(Me!Text2)
      .range("c1").Value = xlsheet.range("a1").Value * _
      xlsheet.range("b1").Value
   End With
   
   xlobject.Parent.Quit
   Text1.SetFocus

6. Switch the form to Form view and enter numeric values into the Text1 and Text2 fields.
7. Click the cmdMyButton command button.

Back to the top