Importing an Excel Worksheet 
Author Message
 Importing an Excel Worksheet

I am importing an Excel worksheet into MS Access. I would like to include
specific worksheets within the workbook in the docmd.TransferSpreadsheet
method.

What is the syntax to include worksheets within the Import Range?

Thanks in advance!

John



Sun, 11 Feb 2001 03:00:00 GMT  
 Importing an Excel Worksheet
HI John,

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: **Posted by Terry Kreft**

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.

HTH

--
Dev Ashish (Just my $.001)
---------------
The Access Web ( http://home.att.net/~dashish )
---------------

:I am importing an Excel worksheet into MS Access. I would like to include
:specific worksheets within the workbook in the docmd.TransferSpreadsheet
:method.
:
:What is the syntax to include worksheets within the Import Range?
:
:Thanks in advance!
:
:John
:
:
:



Sun, 11 Feb 2001 03:00:00 GMT  
 
 [ 2 post ] 

 Relevant Pages 

1. Import Excel Worksheet(2)

2. Import multiple excel files with inconsistent number of worksheets in each into ACCESS

3. Import Excel worksheets via VB and DAO?

4. Newby question: import Excel worksheet

5. VB 4.0 Importing Data from Excel Worksheet into Access 2.0

6. Import Excel Worksheet into an Access Database

7. Newby question: import Excel worksheet

8. OLE: Excel worksheet without using Excel

9. How can I open Excel workbook without using Excel.Worksheet

10. In VB 6, Excel.Worksheet becomes Excel.WorkSheet (note capital 'S'). Whey

11. Importing from Excel to Excel using VB.

12. Enumerating Excel worksheets within Access

 

 
Powered by phpBB® Forum Software