Notice: This website is an unofficial Microsoft Knowledge Base (hereinafter KB) archive and is intended to provide a reliable access to deleted content from Microsoft KB. All KB articles are owned by Microsoft Corporation. Read full disclaimer for more details.

ACC2000: Using Automation to Create and Manipulate an Excel Workbook


View products that this article applies to.

This article was previously published under Q210148
Moderate: Requires basic macro, coding, and interoperability skills.

This article applies only to a Microsoft Access database (.mdb).

↑ Back to the top


Summary

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

↑ Back to the top


More information

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.

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

↑ Back to the top


Keywords: KB210148, kbhowto

↑ Back to the top

Article Info
Article ID : 210148
Revision : 2
Created on : 5/9/2003
Published on : 5/9/2003
Exists online : False
Views : 324