Microsoft KB Archive/306125

= How to import data from Microsoft SQL Server into Microsoft Excel =

Article ID: 306125

Article Last Modified on 1/23/2007

-

APPLIES TO


 * Microsoft Office Excel 2007
 * Microsoft Office Excel 2003
 * Microsoft Excel 2002 Standard Edition

-



This article was previously published under Q306125





SUMMARY
This step-by-step guide describes how to import data into Microsoft Excel from the Pubs database, a sample database that is included with Microsoft SQL Server.

ActiveX Data Objects (ADO) provide access to any type of data source. It is a flat object model with few objects. The main objects in the ADO object model are:   Object          Description ---  Connection      Refers to the connection to the data source. Recordset      Refers to the data extracted. Command        Refers to a stored procedure or SQL statements that need to be executed. Although there are many ways to return a Recordset by using ADO, this article concentrates on the Connection and the Recordset objects.

Requirements
You must have a local server running Microsoft SQL Server and containing the Pubs database.

Microsoft recommends you have knowledge of the following:
 * Creating Visual Basic for Applications procedures in Office programs.
 * Working with Object variables.
 * Working with Excel objects.
 * Relational Database Management Systems (RDBMS) concepts.
 * Structured Query Language (SQL) SELECT statements.

Referencing the ADO object library

 * 1) Start Excel. Open a new workbook and then save it as SQLExtract.xls.
 * 2) Start the Visual Basic Editor and select your VBA project.
 * 3) On the Tools menu, click References.
 * 4) Click to select the most recent version of the Microsoft ActiveX Data Objects Library check box.

Creating the connection
 Insert a new module into the project. Create a new Sub procedure called DataExtract.  Type or paste the following code: ' 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 = &quot;PROVIDER=SQLOLEDB;&quot;

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

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

'Now open the connection. cnPubs.Open strConn 

Extracting the data
Type or paste the following code to extract your records: ' 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 &quot;SELECT * FROM Authors&quot; ' Copy the records into cell A1 on Sheet1. Sheet1.Range(&quot;A1&quot;).CopyFromRecordset rsPubs ' Tidy up   .Close End With

cnPubs.Close Set rsPubs = Nothing Set cnPubs = Nothing

Verifying that the code works

 * 1) Run the code.
 * 2) Switch to Excel and look at Sheet1 in the workbook to view the data.

Troubleshooting
If your code appears to hang and you receive a run-time error, your database server may be down. You can use the ConnectionTimeout property to control the time it takes to return a run-time error. Set this property to a value greater than zero. If you set the value to zero, the connection will never time out. The default value is 15 seconds.

