ACC: Using Automation to Create and Manipulate an Excel Workbook

ID: Q142476

The information in this article applies to:

SUMMARY

Moderate: Requires basic macro, coding, and interoperability skills.

This article show you how to use Automation to create and manipulate a Microsoft Excel Worksheet.

This article assumes that you are familiar with Visual Basic for Applications and with creating Microsoft Access applications using the programming tools provided with Microsoft Access. For more information about Visual Basic for Applications, please refer to your version of the "Building Applications with Microsoft Access" manual.

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.

REFERENCES

For more information about Automation, type "Automation" in the Office Assistant, click Search, and then click to view "Automation with Microsoft Access."

For more information about Automation, search for "Ole Automation," and then "Ole Automation" using the Microsoft Access for Windows 95 Help Index.

Additional query words: 

Keywords          : IntpOlea 
Version           : 7.0 97
Platform          : WINDOWS
Hardware          : x86
Issue type        : kbhowto

Last Reviewed: November 21, 1998