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

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.


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.

'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
End With

Set rsPubs = Nothing
Set cnPubs = Nothing

End Sub


Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question