[FAQ's: OD] How do I transfer data between Access and Excel?

[RobDog888]

Using the TransferSpreadsheet method of the DoCmd object is the easiest method for transfering data between Access and Excel.

It only takes a few arguments.


Expression.DoCmd.TransferSpreadsheet TransferType, SpreadsheetType, TableName, FileName, HasFieldNames, Range, UseOA

TransferType: (acExport, acImport, acLink )
Specifies the direcion of the transfer.

SpreadsheetType: (acSpreadsheetTypeExcel3, acSpreadsheetTypeExcel4, acSpreadsheetTypeExcel5, acSpreadsheetTypeExcel7, acSpreadsheetTypeExcel8, acSpreadsheetTypeExcel9, acSpreadsheetTypeLotusWK1, acSpreadsheetTypeLotusWK3, acSpreadsheetTypeLotusWK4)
Specifies the filetype and version. This depends on the filters and converters that you may have optionally selected during Access/Office installation.

TableName: (Variant - Access Object)
This specifies the Access Object that will be used as the source/destination depending upon the first argument supplied.
It can be a Table or Query to be Exported from or just a Table to be imported to.

FileName: (Variant - filepath\name)
Depending on the first argument this will be either the file to export the database objects conetents into or the source file for use in an import operation.

HasFieldNames: (Variant - True/False)
Specifies that the first row of the spreadsheet as field names when importing or linking. Use False to identify the first row of the spreadsheet as normal data.

Range: (Variant - Range)
A valid range of cells or the name of a range in the spreadsheet. Applies only to importing. Leave this argument blank to import the entire spreadsheet. When you export to a spreadsheet, you must leave this argument blank.

UseOA: (Variant)
This argument is not supported.


Access 2003 VBA Export Code Example:
Export an Access Table to an Excel Sheet:

'Behind an Access VBA Form
Private Sub Command1_Click()
    Application.DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "Table1", "C:\Book1.xls", False, "Sheet2$"
End Sub

Access 2003 VBA Import Code Example:
Import an Excel Sheet to an Access Table:

'Behind an Access VBA Form
Private Sub Command1_Click()
    Application.DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, "Table1", "C:\Book1.xls", False, "A1:D8"
End Sub