This article was previously published under Q142476
This article has been archived. It is offered "as is" and will no longer be updated.
Moderate: Requires basic macro, coding, and interoperability skills.
This article show you how to use Automation to create and manipulatea Microsoft Excel Worksheet.
This article assumes that you are familiar with Visual Basic forApplications and with creating Microsoft Access applications using theprogramming tools provided with Microsoft Access. For more informationabout Visual Basic for Applications, please refer to your version of the"Building Applications with Microsoft Access" manual.
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:
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.
For more information about Automation, type "Automation" in the OfficeAssistant, click Search, and then click to view "Automation with MicrosoftAccess."
For more information about Automation, search for "Ole Automation," andthen "Ole Automation" using the Microsoft Access for Windows 95 HelpIndex.