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

Linking Microsoft Excel Worksheets

To link data from a Microsoft Excel worksheet to your Microsoft Access database, use the CurrentDb function to return an object variable that represents the current Microsoft Access database. Then create a TableDef object and set the connection string and source table name: set the TableDef object’s Connect property to specify the source database type and the full path to the Microsoft Excel worksheet or workbook, and set its SourceTableName property to the name of the worksheet or range of cells.

The following example links a Microsoft Excel version 5.0 worksheet named January Sales in a workbook named Q1Sales.xls located in the Sales folder to a Microsoft Access database:

Sub LinkExcelSheet()
	Dim dbs As Database
	Dim tdf As TableDef
	Dim rstSales As Recordset

	' Open the Microsoft Access database.
	Set dbs = CurrentDb

	' Create a TableDef object.
	Set tdf = dbs.CreateTableDef("Linked Microsoft Excel Worksheet")

	' Set the connection string to specify the source database type and the
	' path to the workbook that contains the data you want to link.
	tdf.Connect = "Excel 5.0;DATABASE=C:\Sales\Q1Sales.xls"

	' Set the SourceTableName property to the worksheet you want to access.
	tdf.SourceTableName = "January Sales$"

	' Append the TableDef object to the TableDefs collection to create a link.
	dbs.TableDefs.Append tdf

	' Create a Recordset object from the linked Microsoft Excel worksheet.
	Set rstSales = dbs.OpenRecordset("Linked Microsoft Excel Worksheet")
End Sub

The following code shows the connection information you would use to link a range of cells named ThirdQuarter in a Microsoft Excel version 8.0 worksheet called Sales.xls which is located on C:\Excel\Examples to a Microsoft Access database.

tdfThirdQtr.Connect = "Excel 8.0; DATABASE=C:\Excel\Examples\Sales.xls"
tdfThirdQtr.SourceTableName = "ThirdQuarter"

Tip   Instead of specifying the connection string and source table name by setting properties of the TableDef object, you can specify them by using the connect and source arguments of the CreateTableDef method. You can also use the Microsoft Access user interface to link external data. For more information on using the CreateTableDef method or the user interface to link external data, see “Linking External Tables” earlier in this chapter.

Opening Microsoft Excel Worksheets

To open a Microsoft Excel worksheet directly from your Microsoft Access database, create a Database object and set the arguments of the OpenDatabase method to specify the full path to the worksheet or workbook, whether to open the worksheet or workbook exclusively, whether to open it with read/write or read-only permissions, and the source database type. Specify the name of the worksheet or range of cells as the source argument of the OpenRecordset method.

The following example opens a Microsoft Excel version 4.0 worksheet named Data.xls directly from a Microsoft Access database, creates a Recordset object from the worksheet, and counts the number of records that appear in the Recordset object.

Sub OpenExcel4Sheet()
	Dim dbs As Database
	Dim rst As Recordset
	Dim intNumRecords As Integer

	' Open the Microsoft Excel worksheet named Data.xls without exclusive access
	' and with read/write permissions. 
	Set dbs = OpenDatabase("C:\Excel\Data.xls",False, False, "Excel 4.0;HDR=No;")

	' Create a Recordset object from the worksheet Data.xls. The number sign
	' character (#) tells the installable ISAM that the code is referencing
	' the entire worksheet.
	Set rst = dbs.OpenRecordset("Data#xls")

	' Use the MoveLast method to populate the Recordset; use the RecordCount 
	' property to count the records and display a message that gives the results.
	rst.MoveLast
	intNumRecords = rst.RecordCount
	MsgBox "There are " & intNumRecords & " rows in this worksheet."
	rst.Close
End Sub

This next example opens a worksheet named SampleSheet in a Microsoft Excel version 7.0 workbook named Data.xls, creates a Recordset object from the worksheet, and counts the number of records that appear in the Recordset object.

Note   Use the source database type Excel 5.0 in the connection string for both Microsoft Excel version 5.0 and Microsoft Excel version 7.0. Excel 7.0 isn’t a valid source database type.

Sub OpenExcel7Sheet()
	Dim dbs As Database
	Dim rst As Recordset
	Dim intNumRecords As Integer

	' Open the Microsoft Excel workbook named Data.xls without exclusive access
	' and with read/write permissions.
	Set dbs = OpenDatabase("C:\Excel\Data.xls",False, False, "Excel 5.0;HDR=No;")

	' Create a Recordset from the worksheet. The dollar sign character following 
	' the sheet name tells the installable ISAM that the code is referencing the
	' entire sheet.
	Set rst = dbs.OpenRecordset("SampleSheet$")

	' Use the MoveLast method to populate the Recordset; use the RecordCount
	' property to count the records and display a message that gives the results.
	rst.MoveLast
	intNumRecords = rst.RecordCount
	MsgBox "There are " & intNumRecords & " rows in this worksheet."
	rst.Close
End Sub

Note Microsoft Excel version 8.0 supports the ability to create files that contain formatting information for both Microsoft Excel version 7.0 and Microsoft Excel version 8.0. You can create files that can be opened by either Microsoft Excel version 7.0 or Microsoft Excel version 8.0 without having to convert the file format. You can access these files only in read-only mode from Microsoft Access.

Using the HDR Argument to Suppress Headers

Both Microsoft Excel and Lotus 1-2-3 include the HDR argument in the connection string. The HDR argument determines whether the first row of the range to be opened should be treated as a record or as field names when the Microsoft Access table is created. The following table describes the settings for the HDR argument.