Select the product you need help with

ACC: Using Automation to Create and Manipulate an Excel Workbook

Article ID: 142476 - View products that this article applies to.

Retired KB Content Disclaimer

This article was previously published under Q142476

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

Expand all | Collapse all

SUMMARY

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.

Back to the top | Give Feedback

MORE INFORMATION

Create a New Microsoft Excel Worksheet

Create a new folder on drive C named Examples.
Open the sample database Northwind.mdb.

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

Save the module as basExcel.
Press CTRL+G to open the Debug window, and then type the following word:
ExcelTest
When prompted to "Enter 1st number" enter 1. When prompted to "Enter 2nd number" enter 2.
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

Create a new table with the following properties:

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

Create a new form based on the tblTestExcel table by using the AutoForm: Columnar Wizard.
Add two text boxes to the form named Text1 and Text2.
Add a command button to the form with the following properties:
Name: cmdMyButton
Caption: My Button
OnClick: [Event Procedure]

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

Switch the form to Form view and enter numeric values into the Text1 and Text2 fields.
Click the cmdMyButton command button. Note that the Microsoft Excel data is inserted into the Object on the form.

Back to the top | Give Feedback

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.

Back to the top | Give Feedback

Properties

Article ID: 142476 - Last Review: January 19, 2007 - Revision: 3.3

APPLIES TO

Microsoft Access 95 Standard Edition
Microsoft Access 97 Standard Edition

kbhowto kbprogramming KB142476

Retired KB Content Disclaimer

This article was written about products for which Microsoft no longer offers support. Therefore, this article is offered "as is" and will no longer be updated.

Back to the top | Give Feedback