ok im trying to load an Excel spreadsheet into a recordset.

Im aware that Excel doesnt know what datatypes the columns of data are so it has a best guess by reading in 8 rows of each column.

Now the problem im having is that i have some columns iwth very little data in them and the ODBC driver seems to think that the column is Null so it doesnt import anything for that column. I can test this by sorting the spreadsheet on the column in question so that the few bits of data are at the top and they import fine, when i leave the spreadsheet unsorted the data doesnt import.

Now ive set the MaxScanRows property to 0 as this supposedly forces ODBC to check every row but still the data doesnt import. This is my connection string:

strSourceConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & strFilename & ";Extended Properties='Excel 8.0;HDR=Yes;MaxScanRows=0'"

Then im just doing a "Select * from ......" to retreive the data.

Anyone know of a workaround? I dont really want to tell the users to resort the spreadheet on various columns to get all the data to import, id rather just have them press a single "Import" button.