Home  |   French  |   About  |   Search  | mvps.org  

What's New
Table Of Contents
Credits
Netiquette
10 Commandments 
Bugs
Tables
Queries
Forms
Reports
Modules
APIs
Strings
Date/Time
General
Downloads
Resources
Search
Feedback
mvps.org

In Memoriam

Terms of Use


VB Petition

General: Importing Excel spreadsheets from code

Author(s)
Dev Ashish &
Terry Kreft

Use the TransferSpreadsheet method. For example, to import the entire 'tab' or worksheet, use the following syntax

    DoCmd.TransferSpreadsheet transfertype:=acImport, _
            tablename:="tmpTableName", _
            FileName:="SomeExcelFile", Hasfieldnames:=True, _
            Range:="'WorkSheet Name'!", SpreadsheetType:=5
            'The Spreadsheet type = 5 specifies an Excel 5.0/7.0 file
            'format

Note the quote within the range. If your worksheet name as a space, you have
to use a quote, otherwise you can just pass the name.
Range:="WorksheetName!"

To import a specific range within a worksheet, use the following syntax

    DoCmd.TransferSpreadsheet transfertype:=acImport, SpreadsheetType:=5, _
            tablename:="tmpTableName", FileName:="SomeExcelFile", _
            Hasfieldnames:=False, Range:="WorkSheet!B1:B11"
            'This will import the range B1 through B11
            'The Spreadsheet type = 5 specifies an Excel 5.0/7.0 file
            'format

Note:

The range can also be a number of columns e.g.

Range:="WorkSheet!B:D"

which will import the data in columns B, C, D.


1998-2010, Dev Ashish & Arvin Meyer, All rights reserved. Optimized for Microsoft Internet Explorer