ACC: How to Import an Entire Sheet from an Excel Workbook

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, you must enclose it in apostrophes (' '); otherwise, you receive an invalid range error.

Additional query words:

Keywords          : kbinterop
Version           : 2.0 7.0 97
Platform          : WINDOWS
Hardware          : x86
Issue type        : kbinfo

Last Reviewed: November 20, 1998