ACC: Importing an Entire Sheet from a MS Excel WorkbookLast reviewed: August 29, 1997Article ID: Q115190 |
The information in this article applies to:
SUMMARYAdvanced: 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 INFORMATIONTo 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 FunctionIf 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:R15:C5NOTE: If the sheet name contains a special character, it must be enclosed in apostrophes or you receive an invalid range error. Keywords : kbinterop PgmHowTo Version : 2.0 7.0 97 Platform : WINDOWS Hardware : x86 Issue type : kbinfo |
================================================================================
© 1998 Microsoft Corporation. All rights reserved. Terms of Use. |