Excel 2003 - 'CopyFromRecordset' Error

[Chris Wiley]

I have some VBA code that uses an ADO connection to query a SQL
server. It works fine under Office 2000, Office XP, but fails in
Office 2003.

I am receiving the following error message:

Method 'CopyFromRecordset' of object 'Range' failed.

Here is a snippet of my VBA code:

'' Initialize variables.
Dim cn As New ADODB.Connection
Dim rs As ADODB.Recordset
Dim strSQL As String

cn.Open DBConnectionString ''DBConnectionString is declared in
module

strSQL = "SQL code here..."

Set rs = cn.Execute(strSQL)

''Clear old values
Me.Range("rngData").ClearContents

If Not rs.EOF Then
''Dump contents of recordset onto worksheet
Sheet001.Range("Data_Start").CopyFromRecordset rs

rs.Close

Else
MsgBox "Error: No records returned.", vbCritical
End If

ExitMethod:
cn.Close
Set rs.ActiveConnection = Nothing

Exit Sub

ErrorHandler:
MsgBox Err.Description

Exit Sub

End Sub


Any help would be appreciated. Thanks.

 

[kkknie]

I use 2003 and have used the same code (which worked). I tried t
duplicate your error using the exact same string and all worked fine.
The only way I could duplicate it was to mis-type the Data_Start. I
it possible that there is no Data_Start range defined?

 

[Chris Wiley]

I use 2003 and have used the same code (which worked). I tried to
duplicate your error using the exact same string and all worked fine.
The only way I could duplicate it was to mis-type the Data_Start. Is
it possible that there is no Data_Start range defined?

K

Well I don't believe so because I can open up the same spreadsheet on
my other PC (Office 2000) just fine. I even tested it on another PC
running Office XP in our company.