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.

Blog at WordPress.com.