Microsoft Access TransferSpreadsheet Function with Ms Excel Examples

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.

Read Also:  Ms Access Input Mask in Query Form or Report

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:

    1. Switch the form to Design View.
    2. On Design tab > Controls group > Click Buttons. Draw to the form layout to create it.
    3. On Command Button Wizard, click Cancel.
    4. Press F4 on your keyboard to open Properties Sheet.
    5. In All tab > Caption, rename it to Export.
    6. On Event > On Click, click the Builder. A dialog box will appear, choose Code Builder.
    7. Input the syntax to the Code:
Ms Access TransferSpreadsheet-1

Ms Access TransferSpreadsheet-1

Then save it.

NOTE: change the acExport to other settings for Linking or Importing.

    1. Back to Access, switch the view to Form View and test the Export button.
Ms Access TransferSpreadsheet-2

Ms Access TransferSpreadsheet-2

NOTE: For Importing, be careful in Primary Key value. Make sure that the imported Primary Key value is unique and not in records previously.

Related Access Database

  • access 2013 transferspreadsheet example
  • access 2016 transferspreadsheet method
  • access transferspreadsheet for excel
  • ms access vba transferspreadsheet function excel data sheet
  • transfer spreadsheet in microsoft access
  • TransferSpreadsheet acLink useOA


Access Database Tags: #ms access transferspreadsheet #ms access transferspreadsheet acimport specification #ms access transferspreadsheet csv #ms access transferspreadsheet range #ms access transferspreadsheet table already exists #ms access transferspreadsheet vba #ms access transferspreadsheet xlsx

MS Access VBA Close Workbook and Form
MS Access VBA Close Workbook and Form
In this tutorial, we use Ms Access
MS Access DB and MDB Viewer Software
MS Access DB and MDB Viewer Software
Access DB Viewer is the software to
MS Access Validation Rule Lookup Table Examples
MS Access Validation Rule Lookup Table Examples
The Validation Rule in Microsoft Access is


Must read×

Top