ACC: Using Automation to Create and Manipulate an Excel Workbook

MORE INFORMATION

Create a New Microsoft Excel Worksheet

1. Create a new folder on drive C named Examples.
2. Open the sample database Northwind.mdb.
3. Create a new module and type the following function in the Module windows:

        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 Debug window, and then type the following word:

   ExcelTest

6. When prompted to "Enter 1st number" enter 1. When prompted to "Enter 2nd number" enter 2.
7. Start Microsoft Excel and open the workbook c:\examples\xltest.xls to view the results.

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. Add two text boxes to the form named Text1 and Text2.
4. Add a command button to the form with the following properties:

   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.5"
              .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. Note that the Microsoft Excel data is inserted into the Object on the form.