Excel Connect to SQL Server

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)