Expert One-on-One Microsoft Access Application Development

When you import data from Excel, you are working with either an entire worksheet or a range of data. Since Excel worksheets often have titles, explanatory text, and perhaps charts or other graphics, you will rarely have a worksheet to import that contains nothing but a row of headings and data, so in order to export just the data, you need to define a range and import data from the range. You can also link to an Excel worksheet, which is an option that allows you to work with an Excel worksheet whose contents may change over time.

Importing from Worksheets and Ranges

To test importing data from worksheets, first copy the sample worksheets to your \My Documents\Access Merge folder, then, to import data from the entire Customers.xls worksheet, click the Import Data from Worksheet button on the Worksheet page of the Import from Excel form. The data is imported, and displayed in a subform, as shown in Figure 13.12.

Figure 13.12

The Import Data from Worksheet’s Click event procedure follow, with commentary.

Private Sub cmdImportDatafromWorksheet_Click() On Error GoTo ErrorHandler

Set variables for the workbook and table names.

If CheckDocsDir = False Then GoTo ErrorHandlerExit End If strWorkbook = GetDocsDir & "Customers.xls" strTable = "tblCustomers"

Clear old data from the table to be filled with data from the workbook.

strSQL = "DELETE * FROM " & strTable DoCmd.SetWarnings False DoCmd.RunSQL strSQL

Import data from the entire workbook into the Access table, using the TransferSpreadsheet method.

DoCmd.TransferSpreadsheet transfertype:=acImport, spreadsheettype:=acSpreadsheetTypeExcel9, tablename:=strTable, FileName:=strWorkbook, hasfieldnames:=True

Assign the fsubCustomers form as the subform’s source object.

Me![subCustomers].SourceObject = "fsubCustomers" ErrorHandlerExit: Exit Sub ErrorHandler: MsgBox "Error No: " & Err.Number & "; Description: " & Err.Description Resume ErrorHandlerExit End Sub

For the case where you need to define a data range for importing, the procedure listed below does the job. It adds the Range argument to the TransferSpreadsheet method, specifying the CategoryData range for importing data into the Access table.

Private Sub cmdImportDatafromRange_Click() On Error GoTo ErrorHandler If CheckDocsDir = False Then GoTo ErrorHandlerExit End If strWorkbook = GetDocsDir & "Categories.xls" Debug.Print "Workbook to import: " & strWorkbook strTable = "tblCategories" ‘Clear old data from table strSQL = "DELETE * FROM " & strTable DoCmd.SetWarnings False DoCmd.RunSQL strSQL

Import data from the CategoryDate range in the workbook into table, using the TransferSpreadsheet method.

DoCmd.TransferSpreadsheet transfertype:=acImport, spreadsheettype:=acSpreadsheetTypeExcel9, tablename:=strTable, FileName:=strWorkbook, hasfieldnames:=True, Range:="CategoryData" Me![subCategories].SourceObject = "fsubCategories" ErrorHandlerExit: Exit Sub ErrorHandler: MsgBox "Error No: " & Err.Number & "; Description: " & Err.Description Resume ErrorHandlerExit End Sub

Figure 13.13 shows the Range page of the Import from Excel form, with Northwind food category data imported from a worksheet range.

Figure 13.13

Linking to Excel Worksheets and Ranges

As well as importing data from Excel worksheets into Access tables, you can also link to worksheets. If you need to work with data in Excel worksheets that may be updated from time to time, linking can be useful, though there are limitations on what you can do with linked worksheets. To link to a worksheet in the interface, follow the steps below.

Select File|Get External Data|Link Tables, as shown in Figure 13.14.

Figure 13.14

In the Link dialog (shown in Figure 13.15), select Microsoft Excel (*xls) as the file type, browse to the folder where the worksheet is stored, select the worksheet to link, and click the Link button.

Figure 13.15

The Link Spreadsheet Wizard opens; you can generally accept the defaults. When the Wizard is done, you will have a linked table whose data source is the Excel worksheet you selected. You can’t delete records in a linked Excel worksheet, but you can modify data in its cells.

I use the tag txls for linked Excel worksheet tables.

You can also link an Access table to an Excel worksheet in VBA code, using the TransferSpreadsheet method, as follows:

Public Function LinkToExcel() On Error GoTo ErrorHandler If CheckDocsDir = False Then GoTo ErrorHandlerExit End If strWorkbook = GetDocsDir & "Customers.xls" Debug.Print "workbook to link: " & strWorkbook strTable = "txlsCustomers" ‘Clear old data from table, if there is one On Error Resume Next strSQL = "DELETE * FROM " & strTable DoCmd.SetWarnings False DoCmd.RunSQL strSQL On Error GoTo ErrorHandler ‘Link to the worksheet, using the TransferSpreadsheet method. DoCmd.TransferSpreadsheet transfertype:=acLink, spreadsheettype:=acSpreadsheetTypeExcel9, tablename:=strTable, FileName:=strWorkbook, hasfieldnames:=True ErrorHandlerExit: Exit Function ErrorHandler: MsgBox "Error No: " & Err.Number & "; Description: " & Err.Description Resume ErrorHandlerExit End Function

For convenience, this function can be run from the mcrLinkToExcel macro.