This tutorial will discuss and explain about Ms Access TransferSpreadsheet function. TransferSpreadsheet function is the method to expoert and import data between Access Database, Ms. Project, or Ms. Excel. TransferSpreadsheet function is the action used in VBA module. To open Code Editor, press ALT + F11 to your keyboard.
The syntax for TransferSpreadsheet function
n:
DoCmd.TransferSpreadsheet (TransferType, SpreadsheetType, TableName, FileName, HasFieldNames, Range, UseOA)
Parameters (all optional):
TransferType: type of transfer you want to make. The settings are:
| Name | Value | Description |
| acExport | 1 | Export data |
| acImport | Imported data (default) | |
| acLink | 2 | Linked the database to the data source. |
SpreadsheetType: type of spreadsheet to import from, export to, or link to.
TableName: the name of the table that wanted to be imported, exported, or linked.
FileName: the file name and path of the spreadsheet you want to import from, export to, or link to.
HasFieldNames: argument to determine the first row of spreadsheet:
- True (-1): first row of the spreadsheet as field names
- False (0): first row of the spreadsheet as normal data, default value.
Range: valid range of cells or the name of a range in the spreadsheet. If omitted, it will import entire spreadsheet. This argument applies only to importing, for exporting or linking you need to leave it blank.
UseOA: The value is true or false.
Example in Export:
In this tutorial, we create a button for exporting to Excel. You can follow the steps here:
- Switch the form to Design View.
- On Design tab > Controls group > Click Buttons. Draw to the form layout to create it.
- On Command Button Wizard, click Cancel.
- Press F4 on your keyboard to open Properties Sheet.
- In All tab > Caption, rename it to Export.
- On Event > On Click, click the Builder. A dialog box will appear, choose Code Builder.
- Input the syntax to the Code:
Then save it.
NOTE: change the acExport to other settings for Linking or Importing.
- Back to Access, switch the view to Form View and test the Export button.
NOTE: For Importing, be careful in Primary Key value. Make sure that the imported Primary Key value is unique and not in records previously.