Dear Friends,
Using Excel Macros (VBA) you can connect to Databases like SQL, Oracle or Access DB. In this Article, I am going to teach you, how you can do a connection with a SQL Server.
SQL databases has mainly two type of authentication :
1. Windows Authentication
2. SQL Server authentication.
Here in this article, I will share VBA code for both type of connection – Windows authentication and SQL Server Authentication.
Before you start running the below code, make sure that reference for ADODB Connection is added in your Excel Workbook.
If you do not know how to add references to your excel workbook, refer the below steps:
How to add ADODB Connection references in Excel
1. Go to VB Editor Screen (Alt+F11)
2. Tools –> References…
3. From the List of Available References Select “Microsoft ActiveX Data Objects 2.0 Library” You can select 2.0 Version or any other higher version of this reference.
4. Click OK
SQL Connection String – Using SQL Server Authentication
SQL Connection String – Using Windows Authentication
Using SQL Server Authentication:
Sub SQL_Connection()
Dim con As ADODB.Connection
Dim rs As ADODB.Recordset
Dim query As String
Set con = New ADODB.Connection
Set rs = New ADODB.Recordset
'---- Replace below highlighted names with the corresponding values
strCon = "Provider=SQLOLEDB; " & _
"Data Source="your ServerName"; " & _
"Initial Catalog=database name;" & _
"User ID=uid; Password=pwd; Trusted_Connection=yes"
'--- Open the above connection string.
con.Open (strCon)
'--- Now connection is open and you can use queries to execute them.
'--- It will be open till you close the connection
End Sub
Using Windows Authentication:
Sub SQL_Connection()
Dim con As ADODB.Connection
Dim rs As ADODB.Recordset
Dim query As String
Set con = New ADODB.Connection
Set rs = New ADODB.Recordset
'---- Replace below highlighted names with the corresponding values
strCon = "Provider=SQLOLEDB; " & _
"Data Source="your ServerName"; " & _
"Initial Catalog=database name;" & _
"Integrated Security=SSPI"
'--- Open the above connection string.
con.Open (strCon)
'--- Now connection is open and you can use queries to execute them.
'--- It will be open till you close the connection
End Sub
Hi Viswa,
some what i know in excel i have few queries in SQL connection am very week in SQL connection part now my requirement is not only connect to the SQL i want to fetch from that could you please help on that,thanks for your support.
i need to fetch defect status and pass count using SQL in Excel.
commands is correct
SELECT Count( BUG.BG_BUG_ID) FROM BUG WHERE BUG.BG_STATUS=’New’
but i donot know how to make connection with QC-alm and get the defect and pass count etc from Defect tab and test Lab
Hi vishwa,
I am not able to connect to sql server through the code you mentioned . It is throwing an automation error when I try to open the connection string.
Runtime error’-2147467259(80004005)
Thank you, you helped me a lot.