Dutch Gemini's Weblog Pages

April 21, 2011

Two Serious Flaws with Excel’s CopyFromRecordset Method

Filed under: Excel,Programming,VBA — dutchgemini @ 11:26 am
Tags: , , , , , , ,

I just bounced into 2 serious flaws using the CopyFromRecordset method for dropping an ADODB.RecordSet (based on Microsoft ActiveX Data Objects Library 2.7) on a worksheet running Excel 2007.

Flaw 1 – Dates are shifted by 1 day

The first flaw is related to a ‘adDBDate’ column containing “time-only” values.

These values, to be compatile with Excel’s Time values, include “01/01/1900” as a dummy date (Excel does not handle dates before Jan 1st, 1900). For example “01/01/1900 15:30:00”.

When the Recordset is copied to the worksheet, all dates are shifted by one day, thus “01/01/1900 15:30:00” (shown in the VBIDE Locals Window) becomes “02/01/1900 15:30:00” (I’m using the European format).

I have not yet discovered at what point Excel (or the CopyFromRecordset method) stops adding this extra day, since dates like July 16th, 2003 do not suffer from this problem.

To complete the story, the “1904” date setting is turned off.

Flaw 2 – NumberFormats on other sheets in the workbook are overruled

The second flaw is IMHO even more serious.

I noted that after using the CopyFromRecordset method, all cell (read: column) formats had changed.

In the attempt to discipline Excel’s bad behaviour to self-decide what cell format to use when dropping data, I pre-set the column’s NumberFormatLocal to “hh:mm:ss” before dropping the RecordSet. Unfortunately, this did not work as Excel forced the entire column back to “Date”. As a workaround, I now set the NumberFormatLocal to use the time string after I copy the RecordSet, and this appears to work.

Wouldn’t it that I copy the contents of the same RecordSet on another worksheet, after having applied a RecordSet Filter. And here I had a another bad surprise: the column’s format on the first sheet had “magically” reverted back to “Date”. This really bugs me.

I have tried all possible combinations of flags in Excel and on the worksheet(s), like turning events off, disabling calculations, and so on, without success.

The only solution that finally worked for me was scanning at the end of my routine all sheets where I used the CopyFromRecordset method on, and force the desired NumberFormatLocal on each column. But I still have the little voice in my head that if someone else, working with my workbook, performs a recalculation, all formats will again boom.

I presume both flaws are bugs and will verify if they also appear in other versions of Excel (2000, 2002, 2003 and 2010) as I have all versions on board.

Does anyone else have had this same experience?

Dutch.

Advertisements

Blog at WordPress.com.