ACC: Importing an Entire Sheet from a MS Excel Workbook (115190)


The information in this article applies to:

  • Microsoft Access 2.0
  • Microsoft Access for Windows 95 7.0
  • Microsoft Access 97
  • Microsoft Excel for Windows 5.0
  • Microsoft Excel for Windows 5.0a
  • Microsoft Excel for Windows 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.
Modification Type:MinorLast Reviewed:10/11/2006
Keywords:kbinfo kbinterop kbProgramming KB115190
©2004 Microsoft Corporation. All rights reserved.