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.