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.
Need More Help? Contact a Support professional by Email, Online or Phone.
Customer Service For non-technical assistance with product purchases, subscriptions, online services, events, training courses, corporate sales, piracy issues, and more.
Newsgroups Pose a question to other users. Discussion groups and Forums about specific Microsoft products, technologies, and services.
Back to the top
