Microsoft KB Archive/210148

= ACC2000: Using Automation to Create and Manipulate an Excel Workbook =

Article ID: 210148

Article Last Modified on 5/9/2003

-

APPLIES TO


 * Microsoft Excel 2000 Standard Edition
 * Microsoft Access 2000 Standard Edition

-



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).



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



Create a New Microsoft Excel Worksheet
 Create a new folder on drive C named Examples. Start Microsoft Access and open the sample database Northwind.mdb.  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  Save the module as basExcel. Press CTRL+G to open the Immediate window, type the following statement, and then press ENTER:

ExcelTest

 At the Enter 1st number prompt, enter 2. At the Enter 2nd number prompt, enter 3.</li> 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.</li></ol>

Create a New Microsoft Excel Worksheet by Using a Bound Control on a Form
<ol>  Create a new table with the following properties: <pre class="fixed_text">  Table: tblTestExcel --  Field Name: MyOleField Data Type : OLE Object </li> Create a new form based on the tblTestExcel table by using the AutoForm: Columnar Wizard.</li> Switch to Design view, and then add two text boxes to the form named Text1 and Text2. Place them below the MyOleField control.</li>  Add a command button with the following properties to the form, and place it below the MyOleField control: <pre class="fixed_text">  Name: cmdMyButton Caption: My Button OnClick: [Event Procedure] </li>  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 </li> Switch the form to Form view and enter numeric values into the Text1 and Text2 fields.</li> Click the cmdMyButton command button.</li></ol>

Note that the Microsoft Excel data is inserted into the object on the form.

Keywords: kbhowto KB210148

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.