I regularly use the OLEDB and ODBC connections to access Excel. The problem I always run into is that unless the user has a registry change accessing mixed content in rows is problematic.
Example connection: "string Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\MyExcel.xls;Extended Properties=""Excel 8.0;HDR=Yes;IMEX=1"""
Registry setting: [HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Jet\4.0\Engines\Excel] located registry REG_DWORD "TypeGuessRows"
Is there a way to force Excel to read the contents of a sheet as text or more accurately to scan all rows for the content type via the driver. I can't change the content of the XL sheet or force a registry key change on some users and this solution needs to be across all machines. Maxscanrows=0 has no effect on the connection string and Microsoft themselves say that it hasn't worked since ADO 2.1