Excel to VB bug?

From my limited testing (I had to find a workaround pronto) seems to be some kind of bug when using VB6, ADO 2.7, ODBC text driver to read from an Excel spreadsheet. Recordsets with date fields come in with the dates encoded as integers. This is normally no problem, as you can use IsDate to check if it’s valid, then CDate to convert it to a date.

But what I’ve found is that IsDate has stopped working… that is, it’s stopped returning True for field values that can be converted to dates, eg CDate works. Whether this is something to do with the new year, or a new version of something on my machine, I haven’t yet figured out, but I ended up writing a wrapper IsDate function that just tries the CDate regardless, and returns a False (eg not a date) if it errors.

An initial dig in the MSKB found nothing about it, but I’ll do some more exhaustive digging and try to get a definite answer later. The answer being, I suspect, “Switch to VB.Net, you luddite!”

If you enjoyed this post, please consider leaving a comment or subscribing to the RSS feed to have future articles delivered to your feed reader.