Microsoft KB Archive/115190

= ACC: Importing an Entire Sheet from a MS Excel Workbook =

Article ID: 115190

Article Last Modified on 1/19/2007

-

APPLIES TO


 * Microsoft Access 2.0 Standard Edition
 * Microsoft Access 95 Standard Edition
 * Microsoft Access 97 Standard Edition
 * Microsoft Excel 5.0 Standard Edition
 * Microsoft Excel 5.0a
 * Microsoft Excel 5.0c

-



This article was previously published under Q115190



SUMMARY
Advanced: Requires expert coding, interoperability, and multiuser skills.

This article describes how to use Visual Basic for Applications (or Access Basic in version 2.0) to import an entire worksheet from a Microsoft Excel workbook without specifying a range.



MORE INFORMATION
To import an entire worksheet, refer to the worksheet without a named range, but include an exclamation point (!). For example, the following sample code will import Sheet5 from a workbook named T.XLS in C:\.

NOTE: In the following sample code, an underscore (_) at the end of a line is used as a line-continuation character. Remove the underscore from the end of the line when re-creating this code in Access Basic.

In Microsoft Access 7.0 or 97:

Function ImportXL5 DoCmd.TransferSpreadsheet _ acImport,5,"TestTable","C:\T.XLS",True,"Sheet5!" End Function

In Microsoft Access 2.0:

Function ImportXL5 DoCmd TransferSpreadsheet _ A_IMPORT,5,"TestTable","C:\T.XLS",True,"Sheet5!" End Function If you do not specify a value for the last argument, Microsoft Access will import the first worksheet that it finds in the workbook. If you specify a range, that range will be imported from the first worksheet in the workbook. To specify a range from a specific worksheet, use the syntax in the following example:

Sheet5!R2C1:R15C5

NOTE: If the sheet name contains a special character, you must enclose it in apostrophes (' '); otherwise, you receive an invalid range error.

Keywords: kbinfo kbinterop kbprogramming KB115190

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.