Subscribe

SQLServer-DBA.com Links

Subscribe to newsletter

Dba_db2_button

Powered by TypePad
Excel Connect to SQL Server

09 July,2011 by Jack Vamvas

Excel connect to SQL Server

Connecting to SQL Server from Excel is an easy process. 
Users want to distribute an Excel document , for example , within
a network. Particuarly as there are plenty of libraries the user can access from Excel
as a presentation layer.

To implement this solution you will need :

Microsoft Excel
Visual Basic Editor

Step 1: Create your Excel workbook
Step 2: In Visual Basic Editor create a new project
and within Tools | References , select "Microsoft ActiveX Data Objects Library"
Step 3: Insert a module into your new project , and create a subprocedure
Step 4: Within that sub procedure copy and paste the follwoing code:

 

 

 

####################Start Code##########################

' Create a connection object.
Dim cnMyDB As ADODB.Connection
Set cnMyDB = 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=MyDB;"

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

'Now open the connection.
cnMyDB.Open strConn

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

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

' Tidy up
.Close
End With

cnMyDB.Close
Set rsMyDB = Nothing
Set cnMyDB = Nothing

########################End Code################################### 

Step 5:  Run the code , and check your Excel worksheet
Step 6 : If you have problems , check connection and permissions

Author: Jack Vamvas (http://www.sqlserver-dba.com)


Author: Jack Vamvas (http://www.sqlserver-dba.com)

******** *******

Subscribe to SQLServer-DBA.com

Latest posts delivered to you daily

Delivered by FeedBurner

Verify your Comment

Previewing your Comment

This is only a preview. Your comment has not yet been posted.

Working...
Your comment could not be posted. Error type:
Your comment has been posted. Post another comment

The letters and numbers you entered did not match the image. Please try again.

As a final step before posting your comment, enter the letters and numbers you see in the image below. This prevents automated programs from posting comments.

Having trouble reading this image? View an alternate.

Working...

Post a comment


sqlserver-dba.com | SQL Server Performance Tuning | SQL Server DBA:Everything | FAQ | Contact|Copyright & Disclaimer