Dutch Gemini's Weblog Pages

March 21, 2012

Range from Array – Limits on length of Strings

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

In a recent post (click here) I discussed an error I ran into using CopyFromRecordSet. While trying to resolve the problem, I reverted to using the ADODB GetRows method but ended up the same situation: incomplete data set.

Lucky enough, I ran into a strictly related problem: if you write to ranges using arrays, the method will fail if the array contains a string element sized larger than 8203 characters.

Note: CopyFromRecordSet method fails for the same reason and on the same limit.

Paste the above code in a VBA module to see this happen:

Sub RangeFromArrayFails()
On Error Resume Next
    Dim i As Long, a As Variant

    ReDim a(1 To 2, 1 To 1) As Variant     ' Need an array with 2 rows

    For i = 1 To 32767 Step 2              ' 32767 is Excel's cell limit
        a(1, 1) = String(i, "*")           ' Create a string
        a(2, 1) = String(i + 1, "*")       ' As above
        ActiveSheet.Range("A1:A2").Clear   ' clear the area
        ActiveSheet.Range("A1:A2") = a     ' Assign array to range
        If (Err.Number <> 0) Then Exit For ' Error, quit.
    Next i

    Debug.Print IIf(i > 32767, "No limit", "Limit reached at: " & i)
    Debug.Print "Length A1", Len(ActiveSheet.Range("A1"))
    Debug.Print "Length A2", Len(ActiveSheet.Range("A2"))
End Sub

This should be your result:

Limit: 8203
Length A1      8203
Length A2      0

You can try the above code with only 1 array element and see it end without problems.

Have a nice day.


Error -2147467259 Method ‘CopyFromRecordset’ of object ‘Range’ failed

Filed under: Excel,Programming,VBA — dutchgemini @ 10:28 am
Tags: , , , , , , , ,

Recently I ran into error number ‘-2147467259‘ with description “Method ‘CopyFromRecordset’ of object ‘Range’ failed” while transferring a data set from SQL Server into Excel using the aforementioned method on an ADODB.RecordSet.

Of course I Googled for the error but the answers were vague and often pointed to Null values in the RecordSet’s data, but I had other sets that had such empty values and these were dropped without problems, so it had to be somewhere else, in particularly in the data.

The reason for this error is not so easy to find, since CopyFromRecordset may indeed copy data to the worksheet (in my case 31,655 rows of data were copied out of 41,899). From Excel’s point of view, the RecordSet is like a huge array and CopyFromRecordset drops the values by columns and by rows (“Over, then down” in Excel terms). At row 31,655 data was dropped in columns 1 til 4 but no data from column 5 til the last column (31).

After investigation I have been able to isolate the problem and discovered that a very long string in column 5 of the RecordSet —retrieved from a MEMO data type— generated the error. The offending string was 10366 characters long, about a third of Excel’s maximum cell size of 32767 characters. I also discovered that other records having very large text —about 8100 characters—  in the same field in the RecordSet were copied to the worksheet successfully. This made me curious.

I prepared a small test using Excel and Access (2007) and finally was able to establish the upper limit at 8203 character that CopyFromRecordset could handle without problems. Any string length above 8203 characters inevitably fails.

Note: this error is likely generated only when there are at least 2 rows or columns in the RecordSet. A RecordSet with a single value may not even trigger it, or at least I was unable to.

If you have control on the way the RecordSet is generated and you want to use CopyFromRecordset then you should make sure no strings are retrieved large than 8203 characters. If not, then you should revert to use the GetRows method from ADODB and walk your way through the array chopping off any excess characters from the string values before dropping the array to the Range, otherwise this will fail also.

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?


Blog at WordPress.com.