Reading Excel file as datasource in VB -- long fields issue
Posted on 2006-10-27
I am using the following code to open an excel doc and doing a join between 2 worksheets to get a single recordset.
ExcelCN.Provider = "Microsoft.Jet.OLEDB.4.0"
ExcelCN.ConnectionString = "Data Source=" & "myfile.xls" & ";Extended Properties=Excel 8.0;"
sSQL = "SELECT * FROM [table1$] T1 LEFT JOIN [table2$] T2 ON T1.Code=T2.code"
Set ExcelRS = New ADODB.Recordset
ExcelRS.Open sSQL, ExcelCN
This creates a recordset with all the fields that I require except that when I look at fields where the content is over a certain (don't exactly know how many char) size it contains a Null value.
I saw a solution suggested in a previous question where increasing the scan to 16 and defining that there is a header would help, however when I tried changing the connection params:
ExcelCN.ConnectionString = "Data Source=" & "myfile.xls" & ";Extended Properties=Excel 8.0;HDR=Yes;MAXSCANROWS=16"
I get an error saying "Could not find installable ISAM"