Solved

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

Posted on 2010-11-15
27
2,703 Views
Last Modified: 2012-06-27
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

0
Comment
Question by:megnin
  • 15
  • 8
  • 4
27 Comments
 
LVL 13

Expert Comment

by:MWGainesJR
ID: 34137335
Check out this link.  There may be character limit.

http://www.mrexcel.com/forum/showthread.php?t=160922
0
 
LVL 1

Author Comment

by:megnin
ID: 34137551
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

0
 
LVL 13

Expert Comment

by:MWGainesJR
ID: 34137607
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
0
 
LVL 1

Author Comment

by:megnin
ID: 34137652
Where would I put that?  Would it replace some existing code or be added somewhere specific?  (Sorry, I'm a beginner) ;-)
0
 
LVL 13

Expert Comment

by:MWGainesJR
ID: 34137673
Just use that instead of the copyfromrecrodset line.
0
 
LVL 1

Author Comment

by:megnin
ID: 34137754
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?
0
 
LVL 1

Author Comment

by:megnin
ID: 34137766
It did fill the entire worksheet to capacity (all 65536 rows) with a single record.
0
 
LVL 13

Expert Comment

by:MWGainesJR
ID: 34138373
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
0
 
LVL 1

Author Comment

by:megnin
ID: 34138928
Oh, good.  ;-)  I'm glad that's all it was.  :-)  Let me give it a try.  I just got back from lunch.
0
 
LVL 1

Author Comment

by:megnin
ID: 34139097
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.
0
 
LVL 1

Author Comment

by:megnin
ID: 34139146
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>"

0
 
LVL 13

Expert Comment

by:MWGainesJR
ID: 34145062
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
0
 
LVL 1

Author Comment

by:megnin
ID: 34146219
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)
0
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
LVL 13

Accepted Solution

by:
MWGainesJR earned 500 total points
ID: 34147867
Dang, I thought your With was the WsSheet.....try this:

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

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

Author Comment

by:megnin
ID: 34147952
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.
0
 
LVL 1

Author Comment

by:megnin
ID: 34148934
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.
0
 
LVL 13

Expert Comment

by:MWGainesJR
ID: 34150574
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.
0
 
LVL 33

Expert Comment

by:Norie
ID: 34151554
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.
0
 
LVL 1

Author Comment

by:megnin
ID: 34154550
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.  ;-)
0
 
LVL 1

Author Closing Comment

by:megnin
ID: 34154556
Thank you very much.
0
 
LVL 33

Expert Comment

by:Norie
ID: 34154635
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.
0
 
LVL 1

Author Comment

by:megnin
ID: 34154649
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.
0
 
LVL 13

Expert Comment

by:MWGainesJR
ID: 34154698
It wouldn't.  The character limit of a cell in 2003 is going to cause a problem no matter which method is used.  
0
 
LVL 33

Expert Comment

by:Norie
ID: 34155153
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

0
 
LVL 1

Author Comment

by:megnin
ID: 34156933
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.  ;-)
0
 
LVL 33

Expert Comment

by:Norie
ID: 34157003
No problem, sorry to have wasted your time when you already had a solution.:)
0
 
LVL 1

Author Comment

by:megnin
ID: 34157028
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.
0

Featured Post

Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

Join & Write a Comment

Convert between Excel file formats (.XLS, .XLSX, .XLSM) with/without macro option David Miller (dlmille) Intro Over this past Fall, I've had the opportunity to see several similar requests and have developed a couple related solutions associate…
How to quickly and accurately populate Word documents with Excel data, charts and images (including Automated Bookmark generation) David Miller (dlmille) Synopsis In this article you’ll learn how to use ExcelToWord! to copy data,charts, shapes …
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.

744 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now