Spreadsheet Import Problem

[PantoKing]

Hello,

I am importing an excel spreadsheet into access using docmd.transferspreadsheet. The spreadsheet is in the same format every week and usually runs with no problems. However on trying to import it this week it is somehow picking up an extra field and therefore showing an error message "Field 'F9' doesn't exist in destination table". I've checked the excel spreadsheet and I can't see any changes and there are no extra columns.
I've imported the sheet into a new table and there is an extra field when I do this but there's no data in it and every record for this field is blank.

Has anyone any ideas. I'm totally baffled.

Cheers,

Steve Make things as simple as possible — but no simpler.

 

[TFinn]

I think I saw this problem a couple of years ago and it was caused by a lack of formatting in the original Excel spreadsheet. I seem to remember that it was resolved by formatting the column explicitly within Excel.
Sorry I can't be more specific about the cause, but I hope this helps a little !

 

[nathanstevenson]

Hey SteveCarey,

The problem is that somehow the column header F9 (i.e. column header 9) is missing. This causes the import process to stop, and it also slows things down a lot.

Solution: Simply type in the appropraite column headin, and it will work. You also need to investigate why this header is not there anymore. It could have something to do with other macros improperly referencing.

Cheers,
Nathan

 

[PantoKing]

Hello,

Thanks for the responses. I've managed to sort it out. For some reason the excel spreadsheet must have had something in one of the cells to the right of the ususal dataset, I suspect a space or something. I deleted all the columns to the right of it in excel and tried again and it worked perfectly.

Cheers,

Steve. Make things as simple as possible — but no simpler.