ACC: Importing an Entire Sheet from a MS 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.0, 5.0a, 5.0c

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 : WINDOWS:2.0,5.0,5.0a,5.0c,7.0,97
Platform : WINDOWS
Issue type : kbinfo