Avatar of David Megnin
David MegninFlag for United States of America asked on

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

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

Open in new window

Microsoft Excel

Avatar of undefined
Last Comment
David Megnin

8/22/2022 - Mon
MWGainesJR

Check out this link.  There may be character limit.

http://www.mrexcel.com/forum/showthread.php?t=160922
ASKER
David Megnin

Hmmm, yes, there are several comments type fields in the table.  The third record has one that 1600 characters in length.  Excel 2007 and 2010 handle it just fine, but 2003 chokes.  

I hope there is a different workaround... ;-)

I'm not sure what to do with that code or if it would even "fit" into my VBA.
Dim i As Integer
    Dim tempString As String
    i = 1
    Do While Not importsRS.EOF
        tempString = importsRS.Fields(0)
        Cells(i, 31) = tempString
        importsRS.MoveNext
        i = i + 1
    Loop

Open in new window

MWGainesJR

you would need something like: (this is without headers)
i = 1
do while Not rst.EOF
     for j = 0 to rst.fields.count - 1
           cells(i, j + 1) = rst(j)
     next
i = i + 1
loop
Experts Exchange has (a) saved my job multiple times, (b) saved me hours, days, and even weeks of work, and often (c) makes me look like a superhero! This place is MAGIC!
Walt Forbes
ASKER
David Megnin

Where would I put that?  Would it replace some existing code or be added somewhere specific?  (Sorry, I'm a beginner) ;-)
MWGainesJR

Just use that instead of the copyfromrecrodset line.
ASKER
David Megnin

Ewwwww ;-)  Infinite loop... oh, no, it ended with an error:
 "Cells(i, j + 1) = <Application-defined of Object-defined error>"

Do I need a "Set rst = NEW ADODB.Recordset" or is it something else?
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
ASKER
David Megnin

It did fill the entire worksheet to capacity (all 65536 rows) with a single record.
MWGainesJR

oops....forgot the movenext

i = 1
do while Not rst.EOF
     for j = 0 to rst.fields.count - 1
           cells(i, j + 1) = rst(j)
     next
i = i + 1
rst.movenext
loop
ASKER
David Megnin

Oh, good.  ;-)  I'm glad that's all it was.  :-)  Let me give it a try.  I just got back from lunch.
Your help has saved me hundreds of hours of internet surfing.
fblack61
ASKER
David Megnin

That works on my computer just fine.  I still need to test it on the user's computer.  

I had column header text "hard coded" in the first row in Excel, but his overwrote my first row.  To make it start on the second row would I just start it with i=2 instead of i=1?

I also have a column of formulas in column A, so I need the table to populate from B2 instead of A1.  How do I adjust it for that as well?  for j = 1 to rst.fields.count -1 ?

Thanks.
ASKER
David Megnin

Works on my computer, but the user (with Excel 2003) is still gets this error:

"Cells(i, j + 1) = <Application-defined of Object-defined error>"

MWGainesJR

to Start on 2nd row and 2nd column:  Make sure the below do loop is inside your WITH block.  Use this DO code:

With ....
.....
i = 2
do while Not rst.EOF
     for j = 0 to rst.fields.count - 1
           .cells(i, j + 2) = rst(j)
     next
i = i + 1
rst.movenext
loop
end with
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
ASKER
David Megnin

Do you know why the user is still getting that error?

with:

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

i = 2
Do While Not rst.EOF
     For j = 0 To rst.Fields.Count - 1
           .Cells(i, j + 2) = rst(j)
     Next
i = i + 1
rst.MoveNext
Loop

End With

I get an error:  Object doesn't support named arguments

on  .Cells(i, j + 2) = rst(j)
ASKER CERTIFIED SOLUTION
MWGainesJR

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
See how we're fighting big data
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
ASKER
David Megnin

It worked on my computer.  I still need to test it on the user's computer.  She was still getting that error on the last try.
ASKER
David Megnin

Nope, the user is still getting the error on the third line which is the first record with a "comments" field that exceeds that 256 word import limit.  

So, that workaround is not solving the limit that Excel 2003 has on importing data into a single cell.

On my Excel 2007, it works fine.  On a regular user's Excel 2010 it works fine.

On Excel 2003, not so much.
This is the best money I have ever spent. I cannot not tell you how many times these folks have saved my bacon. I learn so much from the contributors.
rwheeler23
MWGainesJR

would it be acceptable to split the field into two columns?

If not, then I think you're only solution would be to isntall 2007+ on the user's machine.
Norie

How many records are in the recordset?

In an earlier post you mention 65536, that's the no of rows in 2003, later versions eg 2007, 2010 have 1M+.

If there are more than 65536 records then in 2003 you would need to split them across worksheets.
ASKER
David Megnin

There are only a couple of hundred records.  When I mentioned 65536 it was because I had left out "rst.movenext" and it had just repeated the first record until it ran out of row to repeat it in.  

Yes, I think the only option is to install Office 2007.  I just wish my Org would hurry up and upgrade everyone.  We are still using Windows 95... no, just kidding; XP.  We are planning to skip 2007 and upgrade everyone straight to Office 2010 by attrition.  When new computers come in to replace aging hardware they should have Windows 7 and Office 2010.  I think I want to hibernate until that happens.  ;-)

I appreciate everyone's input.  MWGainesJR, I'm going to give you the points for the excelent code examples.   I'm going to try and push for the earliest upgrade possible, at least for some people.  I'm tired of trying to code around Office 2003's limitations.  ;-)
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
ASKER
David Megnin

Thank you very much.
Norie

Glad you seem to have a solution, I was going to suggest using GetRows instead of CopyFromRecordset.

I don't know if that would make a difference if the problem is to do with the field size.
ASKER
David Megnin

It seems to be the field size.  I found something during one Google search that said Excel could only import a block of 256 words even though it could hold more in a cell.
I started with Experts Exchange in 2004 and it's been a mainstay of my professional computing life since. It helped me launch a career as a programmer / Oracle data analyst
William Peck
MWGainesJR

It wouldn't.  The character limit of a cell in 2003 is going to cause a problem no matter which method is used.  
Norie

megnin

Just out of curiousity and if you have a moment can you try this?

It works in Excel 2000 with the field 'comment' being of type nvarchar(MAX) and containing approx. 3000 characters.
Option Explicit

Private Sub SQLSrvADOEx()

Dim conn As ADODB.Connection
Dim wsData As Worksheet
Dim rs As ADODB.Recordset
Dim fld As ADODB.Field
Dim strConn As String
Dim strSQL As String
Dim rngData As Range
Dim arrData
Dim NoFields As Long
Dim NoRecords As Long

    Set wsData = Worksheets("Data")

    wsData.Cells.ClearContents

    Set rngData = wsData.Range("A1")

    'connect to SQL Server

    Set conn = New ADODB.Connection

    strConn = "Driver={SQL Server};Server=NORIE-TOSH;Database=TESTPADDBSQL"

    conn.ConnectionString = strConn

    conn.Open

    strSQL = strSQL & "SELECT account_code,    mobile_no,   comment"
    strSQL = strSQL & " FROM tblaccounts;"

    Set rs = New ADODB.Recordset

    rs.Open strSQL, conn

    For Each fld In rs.Fields

        rngData.Value = fld.Name

        Set rngData = rngData.Offset(, 1)

    Next fld

    Set rngData = wsData.Range("A2")

    arrData = rs.GetRows

    NoFields = rs.Fields.Count

    NoRecords = UBound(arrData, 1) + 1
    
    rngData.Resize(NoRecords, NoFields) = arrData


    rngData.Resize(NoRecords, NoFields).Copy

    rngData.Offset(NoRecords).PasteSpecial Transpose:=True

    rngData.Resize(NoRecords).EntireRow.Delete

    Set rs = Nothing

    Set conn = Nothing

End Sub

Open in new window

ASKER
David Megnin

From what I read, the issue is only in Excel 2003.  Not in earlier or later versions.  In the post I found the information, the individual said that his macro was working before he went on vacation and while he was gone the IT staff upgraded his computer to Office 2003 and then it did not work.

I appreciate your effort.  I wish I could give you some points.  ;-)
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
Norie

No problem, sorry to have wasted your time when you already had a solution.:)
ASKER
David Megnin

No, not a waste of time at all.  I'm still learning, so more information is better.  :-)  I appreciate the time you took to try it from a different approach.