Transferspreadsheet Error 1

[lameid]

I am using the following code to import a spreadsheet...

DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, strTable, strFilePath, blHasHeaderRow, strSheet

Nothing about the variables being passed is remarkable nor the data itself. Noteworthy is that strSheet has a $ on the end to specify the sheet for the range.

I am receiving error 3709. "The search key was not found in any record".

Help for the error says...

This error occurs when an ISAM SEEK is being executed and there are no matching values in the index.

Since I am importing, I am not sure what the ISAM is using as an index.

Every post that I found with google points to incorrect syntax, memo fields, special characters or is simply unanswered. Using an eyeball check, none of the cells would be memo fields or have special characters that I have not already seen imported (a bunch of asterisk in a text field). My code works for many Excel files. Even those that seem to be the same layout. The only thing that may I can think of being odd is that the file may have been generated with a different region verison of Excel. However another sheet in that file will import as will several other files/sheets from the same person. In total I have 2 files and 3 sheets producing this error. Any ideas other than working around it by dumping to text first?

Thanks for any insight.

 

[lameid]

In my case this was caused by leading spaces in the column field names in Excel. I run a cleanup process which amongh other things is supposed to trim the column names.

The catch is that it selects the range to use as columns by the equivalent of ctrl + <Right Arrow> in Excel which moves to the last column before an empty column. Each of the problem sheets had empty columns in them, causing not all of the columns to have their name trimmed. So if you receive this error related to ISAM seek, check your field names.

Not a day goes by that I don't hate Excel more for transferring data.