Return ADO recordset to worksheet

9 Jun 2006 3:05 PM

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

9 Jun 2006 3:06 PM

Terri

Sorry wrong newsgroup please ignore.
Show quote

"Terri" <te***@cybernets.com> wrote in message
news:e6c2n6$vlf$1@reader2.nmix.net...
> Any help would be appreciated.
>