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

28 Comments »

  1. For flaw #1, I’ll bet it “corrects” at 28/1/1900 (or the 29th). Lotus 123 had a bug in that it recognized leap year for 1900, but there wasn’t one. Excel copied that bug for consistency, but Jet/Access (and the rest of the world) didn’t.

    For flaw #2, read this http://www.dailydoseofexcel.com/archives/2004/06/03/external-data-mixed-data-types/. I wonder if it’s reevaluating the data types every time like it does for a query table.

    Comment by Dick Kusleika — April 21, 2011 @ 1:50 pm | Reply

    • Flaw #1 appeared to me for the first time while programming an application in Excel 2007. I yet have to see whether this occurs also on earlier releases. I presume you mean Feb 29, 1900, and I’ll try simulating such a date in my recordset. For the moment I have no workaround available and I bet my customers won’t like it.

      Flaw #2 occurs to me only when dropping multiple instances of the same recordset in different parts of the workbook. After I correct the format and drop another recordset, the format seems to stay. I’ve read the link but don’t think it liases with my problem.

      Dutch

      Comment by dutchgemini — April 21, 2011 @ 2:30 pm | Reply

  2. I’m facing this annoying problem right now. It’s really a boring issue…

    Comment by Cristiano Galvão — June 28, 2011 @ 2:36 am | Reply

  3. Dutch, I can confirm that I too am suffering from flaw 2.

    I have a routine which in some cases needs a filtered recordset
    use rsTemp, set the filter
    set rsWork = rsTemp.openrecordset

    (I did it like that because copying from an actual filtered recordset just didn’t work properly at all)

    and use rsWork as the source. – rsWork has column M as a date,

    and column M of a completely different worksheet changes from a deliberately set two deicmal place number (with thousand separator) to the system short date format.
    At the end of my routine I have to re-open the sheet that’s been messed about with, and force that one column back to the format I want.

    And I confirmed that copyfromrecordset was the culprit by single stepping the accces code, and monitoring column M in the problem sheet.

    It is definitely something to do with the fitlering, because the exact same process without a filter does not exhibit the problem.

    So if a filter is not needed then I use

    set rsWork = rsTemp
    and all is well.

    And in my case, although the workbook uses copy from recordset three times , it is a different recordset each time.

    The cell that it changes by the the way is a formula “=Hx/8” , on a sheet that has no link to the one being copeid to, other than being in the same workbook.

    And I’m so glad that it’s not just me this is happening to.

    Of course I can’t see MS fixing it any time soon. they haven’t got the OS file copy working properly in forty years of ‘continual imporvement. (although it does look a lot prettier)

    David’

    Comment by David Harding — March 2, 2012 @ 11:45 am | Reply

  4. Hi Dutch,

    I am also experiencing flaw #2.

    The easiest solution to I found is to reset the formatting after using the method. Another solution I have considered, tedious as it may be, is to iterate through the recordset and paste in a row at a time. This might be a better solution if you have a lot of worksheets.

    I am looking for other solutions. I am trying to first understand the cause for the error.

    John

    Comment by ancien — March 7, 2012 @ 10:45 pm | Reply

    • Has anyone found a solution for flaw #2 (besides iterating through the recordset)?
      I have a procedure that runs from the Excel Ribbon and places the recordset data in a hidden tab. The workbook contains about 40 tabs and the user can run the procedure from any tab. Say the range that is changed in the hidden tab (where the recordset is being copied to) is A1:D60 and column B and C are dates, then Range B1:C60 of the current worksheet would adopt the date format.

      Comment by niel — March 15, 2012 @ 11:32 am | Reply

      • Besides [re-]applying the NumberFormat *after* dropping various instances of the recordset, I see no other solution. This is what works for me.

        Comment by dutchgemini — March 15, 2012 @ 11:55 am

      • That’s the only answer I’ve found as well.
        BUT I don’t get the problem if the recordset was created using a query, — It only happens if the original recordset was filtered.

        That is: Iif rs1 is filtered, then copy from recordset posts the entire (unfiltered) recordset.

        BUT set rs2 = rs1.openrecordset followed by copy from recordset rs2, copies only the filtered data, but then DOES mess up the column formats on other sheets.

        It IS (in my case) always the same sheet/column that is messed up. So at the end of the procedure I go back and re-set what Excel has messed up
        It may be worth writing some cleanuop code that sets all the cloumns the way you want??
        No sign of MS acknowledging this as an issue, let along ofering a solution.

        Comment by David Harding — March 15, 2012 @ 12:25 pm

      • If I got a penny for every day waiting for a sign from M$ I’d be rich since decades.

        From various sources I have heard, and you mention it as well, that filtering a RecordSet does the culprit, which is also true in my case. Why this is happening? I absolutely have no clue.

        I use both the unfiltered result and the filtered result of a RecordSet. I first CopyFromRecordset the unfiltered RecordSet and then I use the same RecordSet applying various RecordSet Filters one or more additional times (and on different sheets). When Excel dumps the filtered recordset, the previous destination ranges switch back to their “Excel-guessed” format, which of course is NOT what I wanted.

        What I do now, is that in my procedure I store the Ranges affected by the CopyFromRecordset in an array (using sheet name, record and field count) and walk this array back applying the format I want (by entire columns, not by individual cells), right after having processed the *last* copy.

        Comment by dutchgemini — March 15, 2012 @ 2:10 pm

      • Thanks for posting the thread Dutch, some of the replies in it are woefully wrong: The colum format gets changed to a a date REGARDLESS of wht it was before. Mine were specifically formatted to nueric with 2 DPs as part of the initial hunt for the problem.

        And don’t you jsut love the MS solution: – Post the data into another temporary sheet, and then past it into the sheet you actually want.

        Comment by David Harding — March 19, 2012 @ 2:57 pm

  5. I decided to paste the recordset row by row and that works for me – not ideal, but a workable solution.

    Comment by niel — March 19, 2012 @ 12:52 pm | Reply

    • Maybe it’s faster dropping the recordset by entire columns using:

      ArrayOfData = RecordSet.GetRows(RecordSet.RecCount, , ColumnNumber)
      Sheet.Range("Range").Value = TransposeTheArray(ArrayOfData)

      I’ve been forced to do so because of another problem with Excel and it’s almost as fast as CopyFromRecordset.

      Remember to transpose the array obtained from GetRows as these develop in column and not in rows. You need to write a custom function for it, since Application.Transpose() has a 255-character limit.

      Comment by dutchgemini — March 21, 2012 @ 4:52 pm | Reply

  6. Just move sheet on 1st place before CopyFromRecordset() and move it back after with restoring ActiveSheet if needed

    Comment by Sergy — July 4, 2012 @ 10:28 am | Reply

    • A very interesting “solution” which I may eventually try.

      Comment by dutchgemini — July 4, 2012 @ 10:38 am | Reply

  7. Flaw #2 experience: (Windows 7/Office 2007 SP3)
    Did a CopyFromRecordset to a hidden worksheet, 14 columns of which H-column had the short date format. Column H in the active worksheet was corrupted (set to short date format) regardless of previously defined formats.
    My solution was to drop the recordset on the hidden sheet far enough to the right to avoid the unwanted tampering with used columns on the active sheet. Now the corresponding column on the active sheet isn’t affected, most likely because it is outside the used area of the active worksheet.
    The only downside is that a bit more real estate than necessary is used on the hidden worksheet.

    Comment by tomsan — January 31, 2013 @ 12:13 am | Reply

    • What you describe is very interesting and I did not notice. I also use hidden worksheets to drop the RecordSet and use a wrapper function to get the information on one or more visible sheets. As it looks, Excel “forgets” which sheet the RecordSet is dropped on and formats the columns of the active worksheet instead.

      Your workaround is one of those pearls to include in an Excel programmer’s cheat-sheet. Thanks again for letting us know.

      Comment by dutchgemini — January 31, 2013 @ 9:22 am | Reply

  8. Remedy for Flaw #2: Before executing copyfromrecordset, select the worksheet where you intend to paste the data. It works for me 🙂

    Comment by Keke — August 9, 2013 @ 2:20 pm | Reply

    • You are right, Excel pastes the format on the active sheet instead of the sheet you copy the RecordSet on. This means that you need to make the destination sheet the active one if you want to avoid the issue. This is the code I now use, work also on [very]hidden sheets:

      Dim wActiveSheet As Excel.Worksheet
      Set wActiveSheet = Application.ActiveSheet
      wResultSheet.Activate
      wResultSheet.Cells(QRES_ROW_DATA, QRES_COL_FIRSTDATACOL).CopyFromRecordset adodbRecordSet
      wActiveSheet.Activate
      Set wActiveSheet = Nothing

      Dutch

      Comment by dutchgemini — August 9, 2013 @ 2:35 pm | Reply

      • Hello,

        for me the current workaround is working:
        Replace
        Activesheet.Range(tblName).CopyFromRecordset rst
        by
        Activesheet.Range(tblName).Cells(1,1).CopyFromRecordset rst

        Comment by alf — January 14, 2014 @ 9:58 am

      • Very late after the fact but the solution with the cells(1,1) DOES solve a lot of the issues that we are seeing also. I admit that “time” formatted cells are still made into short-date format, but at least the other cells are left untouched.

        Comment by jerrynorbury — November 5, 2015 @ 3:56 pm

  9. I can confirm this weird behaviour using Excel 2007 and copyfromrecordset together with an adodb.recordset connected to ms sql 2012. Some googling brought to possible solutions (none of which I have tested so far):
    1. Activate the destination sheet before using copyfromrecordset
    2. Turn off automatic recalculation

    I will try both of these and report on the success of the measures.

    Comment by Thomas — June 8, 2015 @ 10:38 pm | Reply

  10. Edit: Sorry, I overlooked that both solutions were already mentioned in the comments here. Nevertheless I will try them and report if I had any success for my configuration.

    Comment by Thomas — June 8, 2015 @ 10:44 pm | Reply

  11. Well the “activate sheet before copyFromRecrodset” method is doing wonders for me!!!

    Thanks!!

    Comment by Juan Pablo Tamayo (pablete) — January 13, 2016 @ 10:31 pm | Reply

  12. I found what I think is a new one last week:
    If you’re single stepping a process from Access, and you have copyfromrecordset, make sure that you have a break set in the code afterwards, because coyfromrecordset turns off your single step run, and continues with the rest of the code.
    Oh and I rarely use active sheet, I set up a ‘proper’ worksheet variable, (usually wsDest)
    And mine is done with DAO: I don’t know if ADODB works differently

    Comment by David — January 18, 2016 @ 11:52 am | Reply

    • With a break do you mean something like a ‘Stop’ or ‘Debug.Assert’ statement? I have seen many cases where particular Excel code at some point refuses to stop running code during debugging and wanting to run until the end or, occasionally, simply stop at the highlighted statement. Your example could be such a situation and it does not surprise me at all.

      I always explicitly associate statements to the object it refers to because I want to be sure the action is carried out on or with the item I selected but events show that sometimes you must revert to using ‘active elements’ in Excel because of a flaw not properly addressed at the developer’s side at Microsoft.

      Comment by dutchgemini — January 19, 2016 @ 4:41 pm | Reply

  13. Many thanks Alf – simple solution but effective – I have been struggling with this problem for a few days and the fix of adding .cells(1,1) to the table range worked a treat.

    Comment by Richard — March 29, 2016 @ 4:16 pm | Reply

    • Many thanks to Alf, too – simple solution but effective – I have been struggling with this problem for a few days too, and the fix of adding .cells(1,1) to the table range worked a treat.

      Comment by Betty — September 20, 2017 @ 6:15 pm | Reply


RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Blog at WordPress.com.

%d bloggers like this: