header image
 

Method ‘CopyFromRecordset’ of object ‘Range’ failed

If you get the error – Method ‘CopyFromRecordset’ of object ‘Range’ failed

Do what this guy says
http://www.ozgrid.com/forum/showthread.php?t=32105&page=1

[vba]
Dim i as Long
i = 1
For Each fld In rst.Fields
‘ Print out column names
wsSheet.Cells(2, i).Value = fld.Name
i = i + 1
Next fld

Dim j As Long, k As Long

With wsSheet ‘ this is the sheet you want the output on…
For j = 1 To rst.RecordCount
For k = 1 To rst.Fields.Count
If IsNull(rst(k – 1)) Then
.Cells(j + 2, k) = Empty
Else
If Len(rst(k – 1)) > 255 Then
For i = 0 To Int(Len(rst(k – 1)) / 255)
.Cells(j + 2, k).Value = .Cells(j + 2, k).Value & Mid(rst(k – 1), (i * 255) + 1, 255)
Next i
Else
.Cells(j + 2, k).Value = rst(k – 1)
End If
End If
Next k
rst.MoveNext
Next j
End With
[/vba]

Just FYI to others using this code. You must set up a few properties in the Record Set before the .RecordCount property will be available or it will only return (-1) and the code will not run.

Specifically, you will need to add (rsMyRecordset.CursorType = adOpenStatic)
and (rsMyRecordset.CursorLocation = adUseClient) prior to opening the recordset.

enjoy!

~ by shoel on July 4, 2011 .



Leave a Reply