Working with Microsoft Excel Worksheets and Workbooks

The Microsoft Excel product line includes the following single-sheet worksheet and multiple-sheet workbook versions supported by the Microsoft Jet IISAM driver for Microsoft Excel: Microsoft Excel 3.0 and 4.0 (single-sheet) and Microsoft Excel 5.0, 7.0, and 8.0 (multiple-sheet workbooks). There are a few operations that you cannot perform on Microsoft Excel worksheets or workbooks through the Microsoft Excel IISAM driver:

You cannot delete rows from Microsoft Excel worksheets or workbooks.
You can clear data from individual cells in a worksheet, but you cannot modify or clear cells that contain formulas.
You cannot create indexes on Microsoft Excel worksheets or workbooks.
You cannot read encrypted data through the Microsoft Excel IISAM driver. The PWD argument in the connection string cannot be used to open an encrypted worksheet or workbook, even if you supply the correct password. You must decrypt all Microsoft Excel worksheets or workbooks through the Microsoft Excel user interface if you plan to link or open them in your Microsoft Jet database.

Specifying Sheets and Ranges in Microsoft Excel Worksheet and Workbook Files

You can specify a subset of the available data when you open a Microsoft Excel worksheet or workbook. In worksheet files, you can open the entire sheet, a named range of cells, or an unnamed range of cells. In a workbook file, you can open a single worksheet, a named range anywhere in the workbook, or an unnamed range in a single worksheet. The following table lists the conventions for the DATABASE and source connection string arguments you must supply to access each of these objects.

To access this object	In this version of Microsoft Excel	Use this syntax

Entire sheet in a worksheet file	Microsoft Excel 3.0 and 4.0	Set the DATABASE argument to the fully qualified network or directory path to the worksheet file, without a file name. Set the OpenRecordset method’s source argument to filename#xls, where filename is the name of the worksheet.
Entire worksheet in a workbook file	Microsoft Excel 5.0, 7.0, and 8.0	Set the DATABASE argument to the fully qualified network or directory path to the workbook file, including the workbook’s file name. Set the OpenRecordset method’s source argument to sheetname$, where sheetname is the name of the worksheet. Important You must follow the worksheet name with a dollar sign ($).
Named range of cells in a worksheet or workbook file	Microsoft Excel 3.0, 4.0, 5.0, 7.0, and 8.0	Set the DATABASE argument to the fully qualified network or directory path to the worksheet or workbook file, including the worksheet’s or workbook’s file name. Set the OpenRecordset method’s source argument to namedrange, where namedrange is the name previously assigned to the range in Microsoft Excel.
Unnamed range of cells in a worksheet file	Microsoft Excel 3.0 and 4.0	Set the DATABASE argument to the fully qualified network or directory path to the worksheet file, including the worksheet’s file name. Set the OpenRecordset method’s source argument to the range of cells you want to access, such as A1:D12.
Unnamed range of cells in a single worksheet in a workbook file	Microsoft Excel 5.0, 7.0, and 8.0	Set the DATABASE argument to the fully qualified network or directory path to the workbook file, including the workbook’s file name. Set the OpenRecordset method’s source argument to the sheet you want to link or open as sheetname$range. For example, to access cells A1 through D12 in a worksheet named 1996 Sales, you would use the following in the source argument: 1996 Sales$A1:D12.

Note You cannot specify a value in a range that exceeds the maximum number of rows, columns, or sheets for the worksheet or workbook. See your Microsoft Excel documentation for these values.

Microsoft Excel Sheet and Range Specification Examples

The following code examples illustrate how to specify an entire worksheet, a named range, and an unnamed range of cells in a Microsoft Excel 4.0 worksheet and in a Microsoft Excel 8.0 workbook.

This first example opens a Microsoft Excel 4.0 worksheet from a Microsoft Jet database three times to demonstrate how to open an entire worksheet, how to open a named range in a worksheet, and how to open an unnamed range in a worksheet. The ranges are tested by counting the number of records that appear in the Recordset object.

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

	' Open the Microsoft Excel 4.0 sample spreadsheet.
	Set dbs = OpenDatabase("C:\JetBook\Samples\Excel\Supplier.xls", _
		False, False, "Excel 4.0;HDR=NO;")
	' Create a Recordset object for the worksheet Supplier.xls.
	Set rst = dbs.OpenRecordset("Supplier$")
	' Move to the last record and then count the records.
	rst.MoveLast
	MsgBox "There are " & rst.RecordCount & " rows in this worksheet."
	rst.Close

	' Open an existing named range.
	Set rst = dbs.OpenRecordset("FirstTenRows")
	' Move to the last record and then count the records.
	rst.MoveLast
	MsgBox "There are " & rst.RecordCount & " rows in named range FirstTenRows."
	rst.Close

	' Create a Recordset object for the range A1 through G5
	' in the worksheet Supplier.xls.
	Set rst = dbs.OpenRecordset("A1:G5")
	' Move to the last record and then count the records.
	rst.MoveLast
	MsgBox "There are " & rst.RecordCount & " rows in this range."

	rst.Close
	Set rst = Nothing
	dbs.Close
	Set dbs = Nothing
End Sub

This next example opens a worksheet in a Microsoft Excel 8.0 workbook from a Microsoft Jet database three times to demonstrate how to open a worksheet in a workbook, how to open a named range in a workbook, and how to open an unnamed range in a worksheet in the workbook. The ranges are tested by counting the number of records that appear in the Recordset object.

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

	' Open the Microsoft Excel 97 sample spreadsheet.
	Set dbs = OpenDatabase("C:\JetBook\Samples\Excel\Products97.xls", _
		 False, False, "Excel 8.0;HDR=NO;")
	' Create a Recordset object for the Products worksheet.
	Set rst = dbs.OpenRecordset("Products$")
	' Move to the last record and then count the records.
	rst.MoveLast
	MsgBox "There are " & rst.RecordCount & " rows in this worksheet."
	rst.Close

	' Create a Recordset object for the named range "SecondTenRows".
	Set rst = dbs.OpenRecordset("SecondTenRows")
	' Move to the last record and then count the records.
	rst.MoveLast
	MsgBox "There are " & rst.RecordCount & " rows in this named range."
	rstSales.Close

	' Create a Recordset object for the range A1 through E5 in the
	' Products worksheet. 
	Set rst = dbs.OpenRecordset("Products$A1:E5")
	' Move to the last record and then count the records.
	rst.MoveLast
	MsgBox "There are " & rst.RecordCount & " rows in this range."

	rst.Close
	Set rst = Nothing
	dbs.Close
	Set dbs = Nothing
End Sub