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
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"
loop until cells(i,1)=""