How to Run SQL Query Using VBA Script
Here is a simple VBA macro code to run SQL Query from excel. And you can assign a 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 the Server name as sql_server_name, the Database as database_name, and TableName as table_name.
Just follow the 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 the new module to place the VBA code in it. Go to Insert >> Module menu.
Step 3: In the module, just copy and paste the below code in the code window:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 | 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= sql_server_name;" & _ "Initial Catalog= database_name;" & _ "Integrated Security=SSPI" oCon.Open '****************** Section 3 ****************** 'Define SQL Query to be Execute 'If you want to run the query only to modify, update and delete records, then run the query and skip Section 4 strSQL = "Delete from table_name Where EmpID='123456'" 'AND 'If you want to get output in Excel, then use Select query and proceed to Section 4 strSQL = "Select * from table_name Where EmpID='110001' " 'If you have a multi-line query then you can do 2 things. '1st is below: write query syntax like the below structure strSQL = "Select * from table_name" 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 Recordset 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 the header of raw data, so you have to add it manually in the 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 |
Below image reference to row number 37 in vba code.
Step 4: Now save the file. Go to the File menu and click on Save or press Ctrl+S.
Step 5: After saving the file, return to excel and you can run your macro from the macro options. So Go to the File menu and Click on the 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 Steps 4 and 5.
If you face compiling error issue like the below:
Go to Tools >> References… menu in the visual editor window.
You will see the “Microsoft Active X Data Object Library” option in a small window. Select the latest version library if there are many versions. See screenshots below:
Press OK and you are done.
0 Comments