How to Run SQL Query Using VBA Macro - Step by Step Guide

Here is a simple VBA macro code, so you can run SQL Query from excel. And you can assign command button to execute your query. Also you can get the output of that query in your excel sheet very easily.

So for example let Server name as SQLServer, Database as TestingDatabase and TableName as tbTesting.

Just follow simple step by step instructions and you are done.

Step 1: Open Excel Application and Press key Alt+F11 (Shortcut Key). It will open Microsoft Visual Basic for Applications Window.

Step 2: Then insert new module in to place VBA code in it. Go to Insert >> Module menu.

Step 3: In the module, just copy and paste below code in the code window:

Public Sub Run_SQLQuery ()

'****************** Section 1 ******************

'Define variables

Dim oCon As ADODB.Connection

Dim oRS As ADODB.Recordset

Dim strSQL As String

Set oCon = New ADODB.Connection

Set oRS = New ADODB.Recordset

'****************** Section 2 ******************

'Open SQL Connection (Server and Database info)

'This will connect SQL with Windows Authentication

oCon.ConnectionString = "Provider=SQLOLEDB;" & _    

        "Data Source= SQLServer;" & _                           

        "Initial Catalog= TestingDatabase;" & _               

        "Integrated Security=SSPI"

oCon.Open

'****************** Section 3 ******************

'Define SQL Query to be Execute

'If you want to run query only to modify, update and delete records, then run query and skip Section 4

strSQL = "Delete from tbTesting Where EmpID='123456'"

'AND

'If you want to get output in Excel, then use Select query and proceed to Section 4

strSQL = "Select * from tbTesting Where EmpID='110001' "

'If you have multi-line query then you can do 2 things.
'1st is below: write query syntax like below structure 
strSQL = "Select * from tbTesting"
strSQL = strSQL & " Where EmpID='110001' "
strSQL = strSQL & " Order By EmpID "

'2nd is to give excel cell value and put your query in that cell in excel. You can use any sheet and any cell for your query and even if you hide that sheet, it will work.

strSQL = Worksheets("Sheet1").Range("A1").Value

'****************** Section 4 ******************

'Open Record set and insert query data in Excel

Set oRS.ActiveConnection = oCon

oRS.Open strSQL

'Provide sheet name and cell value where you want to insert query data.
'Query will not insert header of raw data, so you have to add it manually in excel sheet.

Worksheets("Sheet1").Range("A1").CopyFromRecordset oRS

'****************** Section 5 ******************

'Close SQL Connection

oRS.Close

oCon.Close

If Not oRS Is Nothing Then Set oRS = Nothing

If Not oCon Is Nothing Then Set oCon = Nothing

End Sub

Step 4: Now save the file. Go to File menu and click on Save or press Ctrl+S.

Step 5: After saving file, return to excel and you can run your macro from macro options. So Go to File menu and Click on last option "Close and Return to Microsoft Excel" or press Ctrl+Q.

Note:- If you want to edit vba code, then follow Step 1 and after editing, follow Step 4 and 5.

If you face compile error issue like below:

Go to Tools >> References… menu in visual editor window. 

You will see "Microsoft Active X Data Object Library" option in small window. Select latest version library if there are many versions. Press OK and you are done. See screenshots below:

***** End *****