Import Excel Hit or Miss

[AJD10]

Hi,
Users import their excel sheet into an access db (95 and 2K). Sometimes the whole sheet imports, other times it doesn't and sometimes it adds information (!). If the users reimport, it usually imports correctly. So, the problem can't be duplicated necessarily.

I know I sound so vague about this, but I'm not sure where to look for clues. I use this function in my code:

DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel97, "TempTimesheet", filename, True

Do other people have this problem? It's really painful here because our network is so slow to begin with and having to reimport is not fun.

 

[sfm6s524]

I've had similar problems before. I think Access analyzes a few rows of data to take a guess at what type of data is being imported. Then perhaps, later on, a cell with a non-numeric value is encountered that's destined for a number field, which don't work. Sometimes I've seen formatting affect the accuracy of the import. Looking back at one of my projects where I had to have a bullitproof import from Excel, I gave up on the TransferSpreadsheet method, and created an ActiveX object (GetObject..), use the range.usedrange property to determine range to import, copy entire range into array (variant type), then loop through array and add to table via DAO or ADO. Actually, it was noticably faster then the TransferSpreadsheet method too.