Not able to paste recordset in excel spreadsheet

[pratik4891]

Hi,

Below is my code -(Excel 2013)

Sub Button1_Click()
Dim cnn As ADODB.Connection
   Set cnn = New ADODB.Connection

Dim sSQLQry As String
Dim ReturnArray
Dim mrs As New ADODB.Recordset
Dim DBPath As String, sconnect As String


   cnn.ConnectionString = "DSN=ABC_32;UID=xxx@xxx.com;PWD=xxxx;"
   cnn.Open

   ' Find out if the attempt to connect worked.
   If cnn.State = adStateOpen Then
      MsgBox "Welcome to Pubs!"
   Else
      MsgBox "Sorry. No Pubs today."
   End If
    Set mrs = cnn.Execute("select rowid from schema.table where accnumber = 'ABC'")
    MsgBox mrs("rowid")

ReturnArray = mrs.GetRows
Sheet1.Range("A2").CopyFromRecordset mrs

mrs.Close

   ' Close the connection.
   cnn.Close
End Sub

From the above code I am able to get the record in Msgbox but not able to paste in a excel cells
Please let me know what I am doing wrong ?

[grimes0332]

Probably because after using GetRows, you are at the end of the recordset and there's nothing more to get.

Comment only, Not tested.

Also, perhaps you need to check your logic. If the connection is not opened, you display a message box but allow the code to continue as if it was.

[pratik4891]

Ok , please suggest how can I paste the recordset in excel

[grimes0332]

Do you understand the concept of recordsets and record pointers?

[pratik4891]

No I dont ,in fact I am just starting with VBA from yesterday.
I have googled and seemed it work like the result set can be pasted via CopyFromRecordset command.

If you point to anything useful that will be great.

[grimes0332]

I am just starting with VBA from yesterday

http://www.excelforum.com/showthread...t=#post3470996

I would suggest that, instead of blindly copying something you do not understand, you would learn more by studying the Properties, Methods and Events available for a recordset.

Basic introduction to recordsets: https://msdn.microsoft.com/en-us/lib...(v=vs.85).aspx
Recordset Properties/Methods: https://msdn.microsoft.com/en-us/lib...=vs.85%29.aspx

[pratik4891]

Sorry but not helpful.

[pratik4891]

This is urgent for me so I have posted and in all these threads back and forth if I could have get the specific answer that would have been a great help.
I would definitely study about it more but now I need a quick solution to this .
So please if anyone else could guide me I will really appreciate that.
And all of you thanks for taking your time out .

[grimes0332]

OK, sorry if you decided those links were not helpful. They contain all the information you need, particularly with regards to moving the record pointer.

You're not asking for 'guidance', you've got that, and I know I could simply say what to do, but that assertion that you're "starting with VBA since yesterday" wasn't true so I'm out of this.

[pratik4891]

No issues , thanks .

[pratik4891]

Not the smartest query but this will work in case anyone has the same problem

Sub Button1_Click()
Dim cnn As ADODB.Connection
   Set cnn = New ADODB.Connection


Dim Cmd1 As New ADODB.Command
Dim ReturnArray As DAO.Recordset
Dim mrs As New ADODB.Recordset
Dim rsTest As ADODB.Recordset
Dim DBPath As String, sconnect As String

   ' Open a connection using an ODBC DSN "Pubs".
   cnn.ConnectionString = "DSN=XXX;UID=XXX@xxx.com;PWD=xxxx;"
   cnn.Open

   ' Find out if the attempt to connect worked.
   If cnn.State = adStateOpen Then
      MsgBox "Echo!"
   Else
      MsgBox "Sorry."
   End If
   
       Cmd1.ActiveConnection = cnn
       Cmd1.CommandText = "sql query"
       mrs.Open Cmd1
       
       

 If Not mrs.EOF Then
        Sheets("Sheet2").Range("A1").CopyFromRecordset mrs
    End If

mrs.Close

   ' Close the connection.
   cnn.Close
End Sub

Any improvements/suggestions are most welcome.

Thanks,