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

• Microsoft Access versions 7.0, 97

SYMPTOMS

Novice: Requires knowledge of the user interface on single-user computers.

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.

In Microsoft Access 97

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.

In Microsoft Access 7.0

Couldn't find object '<name of selected range>'.

DoCmd.TransferSpreadsheet acExport, 8, "MyTable", "C:\MyFile.xls",
   True, "MySheet!MyRange" 

CAUSE

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

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

RESOLUTION

There are two ways to work around this behavior.

Method 1

Method 2

   Function ImpByAddress()
      Docmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel97, _
         "test", "c:\My Documents\Book1.xls", , "A1:E32"
   End Function 

MORE INFORMATION

Steps to Reproduce Behavior

1. Open a new, blank workbook in Microsoft Excel. In cell A1 type the word test and in cell B1 type the word Excel.



2. Select cells A1 and B1, and on the Insert menu, point to Name, and then 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 Microsoft Access, and open the Northwind sample database.



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. Select this range:

   Access_the_Microsoft_Knowledge_Base_from_the_Microsoft_web_site

   Note that the Wizard continues because you selected a named range fewer than 64 characters long.

REFERENCES

For more information about Microsoft Access table specifications, search the Access Help Index for specifications, Microsoft Access.

For more information about the TransferSpreadsheet method or action, search the Help Index for TransferSpreadsheet method or TransferSpreadsheet action.

For more information about range names in Microsoft Excel, search the Excel Help Index for naming, ranges.

Additional query words: xl xls attach

Keywords : kbinterop AccCon IsmExl5
Version : WINDOWS:7.0,97
Platform : WINDOWS
Issue type : kbprb