Implementing DAO and Microsoft Excel
In this exercise, you will use DAO to enter data from a Jet database into a Microsoft Excel spreadsheet.
* To make a reference to DAO in Microsoft Excel
- Start Microsoft Excel.
- On the Tools menu, click Macro and then click Visual Basic Editor .
This will start the Office 97 Visual Basic Editor.
- On the Visual Basic Editor's Tools menu, click References .
- Select Microsoft DAO 3.5 Object Library and click OK .
* To code the data extraction
- On the Insert menu, click Module to add a new module to the application.
- In the General Declaration section, add the following declarations:
Public db as Database Public rs as Recordset
- On the Insert menu, click Procedure to open the Add Procedure dialog box.
- Enter the following information:
Field | Value |
Name | LoadData |
Type | Sub |
Scope | Public |
- Click OK .
- Enter the following Visual Basic code in the LoadData subprocedure:
Dim iCounter As Integer Dim iRecordNum As Integer Set db = OpenDatabase("C:WA\Practice\NWind.mdb") Set rs = db.OpenRecordset("Employees") rs.MoveLast iRecordNum = rs.RecordCount rs.MoveFirst Range("A1").Select For iCounter = 1 To iRecordNum Cells(iCounter, 1).Value = rs.Fields("FirstName") Cells(iCounter, 2).Value = rs.Fields("LastName") rs.MoveNext Next iCounter rs.Close db.Close
* To test your application
- Close the Visual Basic Editor and save the workbook as Ex1.xls
- On the Tools menu, click Macro and then click Macros to open the Macro dialog box.
- Select LoadData from the list and then click Run .
You should now see the list of employees entered into the spreadsheet.
- Save changes and close the workbook. Leave Microsoft Excel running.