Read XLS file from VB6

Question

How can I read an XLS file from VB6? I don't want to use ADO or Automation.

• ADO isn't going to work for my particular XLS files, because it guesses the data types of the columns from the data in the first few rows.
• Automation is out, because Excel may not be installed on the machine.

I'm hoping for recommendations based on experience - I know how to Google :)

Answer 1

ADO

For IMEX=1 setting HKLM\SOFTWARE\Microsoft\Jet\4.0\Engines\Excel\TypeGuessRows to 0 does miracles. I'm wondering if this can be supplied as a parameter of the connect string.

Answer 2

To make the ADO driver a bit more reliable, you can use add two pieces to your connection string:

Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\blah.xls;Extended Properties="Excel 8.0;HDR=YES;IMEX=1;MAXSCANROWS=12"

That will force the driver to scan 12 rows before making a decision on data types.