Special Considerations for Spreadsheets

The following sections describe special information that will help you understand how Microsoft Jet interacts with Lotus 1-2-3 spreadsheets and Microsoft Excel worksheets and workbooks. Both products have one common feature worth mentioning: the HDR argument in the connection string. Additional considerations for each product are covered separately in the sections that follow.

Using the HDR Argument to Suppress Headers

The registry keys that manage behavior in Lotus 1-2-3 spreadsheets and Microsoft Excel worksheets and workbooks contain a setting that determines whether the IISAM driver should use the data in the cells of the first row of the sheet as field names in the Microsoft Jet database. This setting, FirstRowHasNames, is found in the \HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Jet\3.5\Engines\Lotus key for Lotus 1-2-3 and the \HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Jet\3.5
\Engines\Excel key for Microsoft Excel. When the value of FirstRowHasNames is 01 (True, the default), all sheets of that product family are handled as if the first row contained field name information.

To override this registry setting on a sheet-by-sheet basis, you must use the HDR argument in the options portion of the connection string. Set HDR to True (HDR=YES) to instruct the IISAM driver to use the data in the first row of the sheet as field names, regardless of the registry setting. Set HDR to False (HDR=NO) to instruct the IISAM driver to treat the first row of the sheet as data, regardless of the registry setting.

The following procedure opens a worksheet in a Microsoft Excel 8.0 workbook and computes the number of records in the Products worksheet contained in the Products97.xls workbook. The HDR argument is set according to the value of the blnHDRParam argument passed to the procedure. When the HDR argument in the connection string is set to NO, the count is 80 records; when it’s set to YES, the count is 79.

Sub TestHDRConnectParameter(blnHDRParam As Boolean)
	Dim dbs As Database
	Dim rst As Recordset
	Dim strHDRParam As String

	If blnHDRParam = True Then
		strHDRParam = "YES"
	Else
		strHDRParam = "NO"
	End If

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

	' 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