DetailPage-MSS-KB

Microsoft small business knowledge base

Article ID: 306125 - Last Review: April 19, 2012 - Revision: 7.0

This article was previously published under Q306125

On This Page

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

  1. Insert a new module into the project.
  2. Create a new Sub procedure called DataExtract.
  3. 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 = "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
    					

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 "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
				

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.

REFERENCES

You can find additional sample code by searching the following Microsoft Web site:
http://msdn.microsoft.com/ (http://msdn.microsoft.com/)

APPLIES TO
  • Microsoft Office Excel 2007
  • Microsoft Excel 2002 Standard Edition
Keywords: 
kbhowtomaster KB306125
Share
Additional support options
Ask The Microsoft Small Business Support Community
Contact Microsoft Small Business Support
Find Microsoft Small Business Support Certified Partner
Find a Microsoft Store For In-Person Small Business Support