ACC: Importing an Entire Sheet from a MS Excel Workbook

Last reviewed: August 29, 1997
Article ID: Q115190

The information in this article applies to:

Microsoft Access versions 2.0, 7.0, 97
Microsoft Excel for Windows, versions 5.x, 97

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:R15:C5

NOTE: 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

================================================================================

THE INFORMATION PROVIDED IN THE MICROSOFT KNOWLEDGE BASE IS PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND. MICROSOFT DISCLAIMS ALL WARRANTIES, EITHER EXPRESS OR IMPLIED, INCLUDING THE WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE. IN NO EVENT SHALL MICROSOFT CORPORATION OR ITS SUPPLIERS BE LIABLE FOR ANY DAMAGES WHATSOEVER INCLUDING DIRECT, INDIRECT, INCIDENTAL, CONSEQUENTIAL, LOSS OF BUSINESS PROFITS OR SPECIAL DAMAGES, EVEN IF MICROSOFT CORPORATION OR ITS SUPPLIERS HAVE BEEN ADVISED OF THE POSSIBILITY OF SUCH DAMAGES. SOME STATES DO NOT ALLOW THE EXCLUSION OR LIMITATION OF LIABILITY FOR CONSEQUENTIAL OR INCIDENTAL DAMAGES SO THE FOREGOING LIMITATION MAY NOT APPLY.

Last reviewed: August 29, 1997
© 1998 Microsoft Corporation. All rights reserved. Terms of Use.