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.
