Matt J. Wilson

For any developers that are ever forced to develop in VBA, such as myself, I take pity on you for having to deal with some of Microsoft’s idiotic software kinks.  My latest gripe?  The use of any built-in driver to query an Excel sheet.

Here’s an example of my sheet:

   NumberOne
1  123456
2  987654
3  135791
4  246802
5  503513
6  546516
7  889846
8  984658
9  Some Text Here

I tried multiple data source connections, such as these two:

dbConnectionString = "DRIVER={Microsoft Excel Driver (*.xls)};Readonly=1;DBQ=" & SourceFile
dbConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & SourceFile & ";Extended Properties='Excel 8.0;HDR=Yes;IMEX=1'"

So the data I read in for my NumberOne column should be the following values: 123456, 987654, 135791, 246802, 503513, 546516, 889846, 984658, Some Text Here.

What do these actually produce: 123456, 987654, 135791, 246802, 503513, 546516, 889846, 984658, null.

Now why would a null result be produced? I specifically stated to use the IMEX parameter, but it is simply not enough to overcome the stupidity of Microsoft.  Any built-in Excel driver will query the first 8 rows of a sheet and then make a determination (without your permission or knowledge) as to what type of column it is, thereby ignoring anything that doesn’t meet this data type later in the sheet.  There are no exceptions, no warnings, and no way around it but to insert a “MICROSOFT_IS_STUPID” cell in one of these rows, that you then must explicitly ignore, to force the driver to stop it’s ridiculous assumptions and read all of your data.  Also, please note that having a header row won’t rectify the problem, you must have some intermixed data types in the first 8 rows of data.