.NET Tutorials, Forums, Interview Questions And Answers
Welcome :Guest
Sign In
Win Surprise Gifts!!!

Top 5 Contributors of the Month
Lee Hnetinka

Home >> Articles >> DataBase >> Post New Resource Bookmark and Share   

 Subscribe to Articles

How to connect Excel with SQL server data base using VBA

Posted By:Kemal AL GAZZAH       Posted Date: June 27, 2011    Points: 200    Category: DataBase    URL: http://www.dotnetspark.com  

This article shows how to write VBA code in Excel (or in any Microsoft Office solution) to connect with SQL SERVER Data base

Excel is a a wonderful tool which helps dealing with data.

If you want for example to check if some data written in excel are existing in a particular table in a sql server data base or make queries in sql server based on data criteria written in excel and write back the result in excel itself, then the best solution is to make  direct OLEDB connection between EXCEL and sql server data base

Suppose I have table of employees written in Excel , the name is written in column A and I want to write in column B the birth data which will be extracted from sql server.

1) Open you execel sheet containing the data

2) Go To Tools-Macros-Visual Basic editor

3)Goto this workbook (left side) and double click

4)Tools-references: add Microsoft Activex Dat Object x.x Libarary (x.x=2.8 in my case)

Write the following procedure in VBA windows


Sub requete()

Dim cn As New ADODB.Connection
Dim rs As New ADODB.Recordset
Dim cmd As New ADODB.Command

cn.ConnectionString = "Provider=SQLOLEDB;Data Source=.;Initial Catalog=dna;User ID=sa; Password=kamel;"

cmd.ActiveConnection = cn
cmd.CommandType = adCmdText

 i = 2

cmd.CommandText = "select birth from employees where name='" & Cells(i, 1) & "'"

Set rs.Source = cmd

If Not rs.EOF Then
Cells(i, 8) = rs(0)
Cells(i, 8) = "NOT FOUND"

End If


loop until cells(i,1)=""



End Sub

 Subscribe to Articles


Further Readings:


No response found. Be the first to respond this post

Post Comment

You must Sign In To post reply
Find More Articles on C#, ASP.Net, Vb.Net, SQL Server and more Here

Hall of Fame    Twitter   Terms of Service    Privacy Policy    Contact Us    Archives   Tell A Friend