Microsoft KB Archive Search

Notice: This website is an unofficial Microsoft Knowledge Base (hereinafter KB) archive and is intended to provide a reliable access to deleted content from Microsoft KB. All KB articles are owned by Microsoft Corporation. Read full disclaimer for more details.

  1. Home
  2. ACC2000: "Could Not Find Object" Error Importing MS Excel File

ACC2000: "Could Not Find Object" Error Importing MS Excel File

View products that this article applies to.

This article was previously published under Q209924
Novice: Requires knowledge of the user interface on single-user computers.

This article applies to a Microsoft Access database (.mdb) and to a Microsoft Access project (.adp).

↑ Back to the top

Symptoms

When you try to import or link a Microsoft Excel spreadsheet, if you click Show Named Ranges in the Import Spreadsheet Wizard or the Link Spreadsheet Wizard, you may receive the following error message:
The Microsoft Jet database engine could not find the object '<name of selected range>'. Make sure the object exists and that you spell its name and the path name correctly.
You may also receive this error if you use a macro or a Visual Basic for Applications procedure to import or link the spreadsheet.

NOTE: This error message may occur if you convert a Microsoft Access 2.0 database to Access 2000, and the database includes a TransferSpreadsheet macro action or Access Basic code with a Range argument that contains the sheet name along with a named range. For example: 
DoCmd.TransferSpreadsheet acExport, 8, "MyTable", "C:\MyFile.xls", True, "MySheet!MyRange"

↑ Back to the top

Cause

The named range you selected in the Import Spreadsheet Wizard or the Link Spreadsheet Wizard is longer than 64 characters. Although Excel allows a name of up to 255 characters, the Microsoft Excel ISAM driver that Access uses to import or link spreadsheets truncates a range name to 64 characters. The import or link fails because Access cannot find the truncated range name in the spreadsheet.

The reason for the 64-character truncation is that Access uses the name of the range as the table name in your database, and table names are limited to 64 characters.

↑ Back to the top

Resolution

There are two ways to work around this behavior.

Method 1

Open the spreadsheet in Excel and define a name for the range that has 64 or fewer characters. You do not have to delete the existing longer name. Then when you import the spreadsheet into Access, select the shorter defined name.

Method 2

Use the TransferSpreadsheet action in a macro or the TransferSpreadsheet method in a Visual Basic for Applications function to import or link a specific range in the spreadsheet. In the Range argument, specify the range by address rather than by name, for example: "A1:E32." The following sample Visual Basic for Applications function demonstrates this method: 
Function ImpByAddress()
   Docmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, _
     "test", "c:\My Documents\Book1.xls", , "A1:E32"
End Function

↑ Back to the top

More information

Steps to Reproduce Behavior

  1. Open a new, blank workbook in Excel. In cell A1 type the word test and in cell B1 type the word Excel.
  2. Select cells A1 and B1, and then on the Insert menu, point to Name, and click Define.
  3. In the Define Name dialog box, type the following in the Names In Workbook box:
    You_can_access_the_Microsoft_Knowledge_Base_from_the_Microsoft_Web_site
    Click OK.
  4. With cells A1 and B1 still selected, again point to Name on the Insert menu, and then click Define. In the Define Name dialog box, type the following in the Names In Workbook box:
    Access_the_Microsoft_Knowledge_Base_from_the_Microsoft_Web_site
    Click OK.
  5. Save the workbook as C:\Test.xls, and close it.
  6. Start Access, and open the sample database Northwind.mdb.
  7. On the File menu, point to Get External Data, and then click Import.
  8. In the Import dialog box, select Microsoft Excel (*.xls) in the Files of Type box, select C:\Test.xls, and then click Import.
  9. In the Import Spreadsheet Wizard, click Show Named Ranges, and then select this range:
    You_can_access_the_Microsoft_Knowledge_Base_from_the_Microsoft_Web_site
    Note that you receive the error message described in the "Symptoms" section of this article.
  10. Select this range:
    Access_the_Microsoft_Knowledge_Base_from_the_Microsoft_web_site
    Note that the error does not occur because the second selection is a named range that has fewer than 64 characters.

↑ Back to the top

References

In Access:

For more information about Access table specifications, click Microsoft Access Help on the Help menu, type table specifications in the Office Assistant or the Answer Wizard, and then click Search to view the topic.

For more information about the TransferSpreadsheet method, click Microsoft Access Help on the Help menu, type TransferSpreadsheet method in the Office Assistant or the Answer Wizard, and then click Search to view the topic.

For more information about the TransferSpreadsheet action, click Microsoft Access Help on the Help menu, type TransferSpreadsheet action in the Office Assistant or the Answer Wizard, and then click Search to view the topic.

In Excel:

For more information about range names, click Microsoft Excel Help on the Help menu, type naming, ranges in the Office Assistant or the Answer Wizard, and then click Search to view the topic.

↑ Back to the top

Keywords: KB209924, kbprb, kbdta, kbinterop

↑ Back to the top

Article Info
Article ID : 209924
Revision : 1
Created on : 12/12/2002
Published on : 12/12/2002
Exists online : False
Views : 499