Example: Opening a Table in a Microsoft Excel 8.0 Workbook

This example steps you through opening a table that is stored in a Microsoft Excel 8.0 workbook. The example follows the steps outlined in the previous section, “Opening an External Table.”

The first step is to open the Microsoft Excel 8.0 workbook:

Dim dbs As Database, rst As Recordset

Set dbs = OpenDatabase("C:\JetBook\Samples\Excel\Products97.xls", _
	False, False, "Excel 8.0;HDR=NO;")

Note that the example uses HDR=NO to suppress the column header row from the count of records. If you don’t suppress headers in this example, the RecordCount property value displayed in the message box will incorrectly include the first row of column headings in the count of records.

The second step is to create a Recordset object for the external data by using code similar to the following:

Set rst = dbs.OpenRecordset("Products97$")

The argument for the OpenRecordset method is the name of a particular worksheet in the Microsoft Excel workbook. The dollar sign character following the sheet name tells the IISAM driver that you’re referencing the entire sheet, rather than just a range on the sheet.

The following example establishes the connection, creates a Recordset object, and displays the total number of records in a message box:

Sub OpenExcelWorkbook()
	Dim dbs As Database
	Dim rst As Recordset

	' Open the Microsoft Jet sample database.
	Set dbs = OpenDatabase("C:\JetBook\Samples\Excel\Products97.xls", _
		False, False, "Excel 8.0;HDR=NO;")

	' Create a Recordset object for the Microsoft Excel Products worksheet.
	Set rst = dbs.OpenRecordset("Products$")

	' Move to the last record and display the RecordCount property value.
	With rst
		.MoveLast
		MsgBox "There are " & .RecordCount & " records in this worksheet."
		.Close
	End With
	dbs.Close
End Sub

See Also The sample workbook discussed in this chapter can be found in the JetBook\Samples\Excel sample file folder on the companion CD-ROM.