Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

Troubleshooting
Research
Professional Opinions
Ask a Question
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE

troubleshooting Question

Method 'CopyFromRecordset' of object 'Range' failed - while ADOing from SQL to Excel 2003

Avatar of megnin
megninFlag for United States of America asked on
Microsoft Excel
27 Comments1 Solution5105 ViewsLast Modified:
I'm trying to populate an Excel 2003 worksheet from a SQL 2005 table using ADO.

I've got the VBA code below in the Workbook.Open event of "ThisWorkbook".

On my computer (Windows XP, Excel 2007, my DomainAdmin login) it works great.  The worksheet populates with the table data.
On my test computer (Window 7, Excel 2010, my test common user login) it works great.

On the users computer that needs it (Windows XP, Excel 2003, her login) it loads three rows of the data and she gets a Run-time error '-2147467259 (80004005)':
Method 'CopyFromRecordset' of object 'Range' failed

Is there something different about Excel 2003 or am I doing something else wrong?
I don't mind rewriting the whole thing if there's a better way to do it.
Private Sub Workbook_Open()
'This was set up using Microsoft ActiveX Data Objects 2.8 Library (In Microsoft Visual Basic, select Tools, References)
Worksheets("Sheet1").Range("B2:BZ65535").Clear

Dim cnt As ADODB.Connection
Dim rst As ADODB.Recordset
Dim stSQL As String
Dim wbBook As Workbook
Dim wsSheet As Worksheet
Dim rnStart As Range


Const stADO As String = "Provider=SQLOLEDB.1;Integrated Security=SSPI; " & _
"Persist Security Info=False; " & _
"Initial Catalog=CustomerComplaints; " & _
"Data Source=SQLSERVER"

Set wbBook = ActiveWorkbook
Set wsSheet = wbBook.Worksheets(1)

With wsSheet
Set rnStart = .Range("B2")
End With

stSQL = "SELECT * FROM Complaints ORDER BY Location, DateReceived"

Set cnt = New ADODB.Connection

With cnt
.CursorLocation = adUseClient
.Open stADO
.CommandTimeout = 0
Set rst = .Execute(stSQL)
End With

'Here we add the Recordset to the sheet from A1
rnStart.CopyFromRecordset rst

'Cleaning up.
rst.Close
cnt.Close
Set rst = Nothing
Set cnt = Nothing

End Sub
ASKER CERTIFIED SOLUTION
Avatar of MWGainesJR
MWGainesJRFlag of United States of America image

Our community of experts have been thoroughly vetted for their expertise and industry experience.

Commented:
This problem has been solved!
Unlock 1 Answer and 27 Comments.
See Answers