Return ADO recordset to worksheet

[Terri]

Any help would be appreciated.

I am trying to return a recordset to a worksheet using ADO. I want to go
dsn-less.

I confident that my recordset returns data, I've tested this in Access and
can return records there. I am a new excel programmer.

I am trying to use CopyFromRecordset to return the records. When I am in
the VBA editor I choose Run Sub but no records are returned to Sheet1.

I'd like the recordset to be refreshed every time the sheet is activated.

I've created a reference to Microsoft ActiveX Data Objects 2.8 library.

Am I missing something?

Sub ImportData()
Dim cn As ADODB.Connection
Set cn = New ADODB.Connection

Dim strConn As String
strConn = "PROVIDER=SQLOLEDB;"
strConn = strConn & "SERVER=MYSERVER;INITIAL CATALOG=MYDATABASE;"

strConn = strConn & " INTEGRATED SECURITY=sspi;"
cn.Open strConn

Dim rs As ADODB.Recordset
Set rs = New ADODB.Recordset

With rs
.ActiveConnection = cn
.Open "SELECT * FROM MyTable"
Sheet1.Range("A1").CopyFromRecordset rs
.Close
End With

cn.Close
Set rs = Nothing
Set cn = Nothing
End Sub

 

[Ron de Bruin]

See if this help you

closed Excel books
http://www.rondebruin.nl/ado.htm

Or Access data
http://www.rondebruin.nl/accessexcel.htm

 

[Terri]

Ron, Thanks for your response.

I've changed my line of code to
Worksheets("Sheet1").Range("A2").CopyFromRecordset rs

I've also attached my code to a command button and that works. My next task
is to run the code everytime a worksheet becomes active. There will be
several worksheets and each time the users clicks to a new one I want the
data to be refreshed.

 

[Ron de Bruin]

Hi Terri

There is a event in the sheet module named Activate

Private Sub Worksheet_Activate()
YourMacroName
End Sub