Microsoft Excel Drivers and IMEX
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.
Hey Matt, thanks for the post. If it helps at all, I did find this on connectionstrings.com:
Check out the [HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Jet\4.0\Engines\Excel] located registry REG_DWORD “TypeGuessRows”. That’s the key to not letting Excel use only the first 8 rows to guess the columns data type. Set this value to 0 to scan all rows. This might hurt performance.
However the performance hit they speak of is considerable. Hope that helps.
Joe
Joe,
Thanks for the comment, it’s very much appreciated. Hopefully someone else can use that registry setting instead of having to do as ridiculous of a workaround as I was forced to do.
I wish I had known about the registry setting, but couldn’t have used it anyway as it’s against company policy (or at least ridiculously hard to process a registry setting modification through change control for something as insignificant as an MS Access/Excel application). I’ll have to keep this in mind for anytime it comes up again outside of the corporate world
.