Import data from Microsoft SQL Server into Microsoft Excel

[Joe K.]

I am trying to create a Excel VBA script that I can use to import data from
SQL Server into MS Excel.

I used the MS support article listed below to create a Sub DataExtract
procedure.

I completed the instructions from the Microsoft article but was unable to
import data from the authors table.

No errors were output when running the macro.
I am using Excel version 2003 with Microsoft ActiveX Data Object Library
version 2.8.

Please help resolve the problem with this script or another simple script
that will import data from SQL Server into MS Excel.

Thanks,

http://support.microsoft.com/default.aspx/kb/306125/EN-US/


Sub DataExtract()
' Create a connection object.
Dim cnPubs As ADODB.Connection
Set cnPubs = New ADODB.Connection

' Provide the connection string.
Dim strConn As String

'Use the SQL Server OLE DB Provider.
strConn = "PROVIDER=SQLOLEDB;"

'Connect to the Pubs database on the local server.
strConn = strConn & "DATA SOURCE=(local);INITIAL CATALOG=pubs;"

'Use an integrated login.
strConn = strConn & " INTEGRATED SECURITY=sspi;"

'Now open the connection.
cnPubs.Open strConn

' Create a recordset object.
Dim rsPubs As ADODB.Recordset
Set rsPubs = New ADODB.Recordset

With rsPubs
' Assign the Connection object.
.ActiveConnection = cnPubs
' Extract the required records.
.Open "SELECT * FROM Authors"
' Copy the records into cell A1 on Sheet1.
Sheet1.Range("A1").CopyFromRecordset rsPubs

' Tidy up
.Close
End With

cnPubs.Close
Set rsPubs = Nothing
Set cnPubs = Nothing

End Sub