0
₹0.00

No products in the basket.

Create a SQL Query Executor Application in MS Excel – 3 Easy Steps

Introduction – SQL Query Executor (Run SQL Query in Excel)

No other tool can replace the usage and flexibilities of SQL. SQL statement provides the output of any simple or complex queries very quickly. You don’t need to click anywhere or follow any steps to query the data. You just need to write the SQL query and it will provide the output in fraction of seconds.

Just imagine, if we can use the SQL statement to query the data from Excel Workbook then it will save a lot of time and also, we can run complex queries which would be practically tough to process in Excel with inbuilt query functions/steps.

SQL Query Executor Application in MS Excel and VBA

In this post, we are going to learn How to create a SQL Query Executor Application in MS Excel and VBA to query data from Excel workbook?

With the help of this post, you can create your custom query application in Excel to run the SQL Query in Excel to and get data from Excel Workbook. You can use the same code after making some minor changes (data source), if required.

SQL Query Executor Application

Develop SQL Query Executor Application in MS Excel and VBA

Let’s start creating SQL Query Executor from scratch so that you can run SQL Query in Excel!

Create, Save and Design the User Interface of SQL Executor in Microsoft Excel

Open a new Excel Workbook and save the file with the name “SQL Query Executor” and extension should be .XLSM as we are going to write VBA code.

Note: if you will not select .XLSM then Microsoft Excel will not retain your VBA code once you will close the file.

image 1
Saving file

Now, rename the sheet1 to Query Executor in Excel Workbook.

image 2
Rename

Go to the View Tab and untick the Gridlines checkbox in Show group to hide the gridlines

image 3
Removing gridlines

Design the User Interface in Query executor sheet as per below image. This will be the user interface of SQL Query Executor application and through this, you can run SQL query in Excel.

User Interface SQL
User Interface

Writing Visual Basic Application Codes for SQL Query Executor in Excel

VBA Codes are required to make the SQL Query Executor actionable. We will write VBA codes in integrated development window of MS Excel i.e. Visual Basic for Application.

To open the VBA Window, just click on Developer tab and then click on VBA Button available in Code Group. You can also press Shortcut key as Alt + F11 to open the VBA window.

image 4
VBA button

Now, you can see that Visual Basic Application window is open.

image 5
VBA for Excel

Here, we need to insert a blank Module to write all the required Procedures and Functions to handle the process.

Let’s click on Insert menu and then click on Module. It will add a blank module to this project.

image 6
Blank Module

Before writing the code, we need to add the reference of Microsoft ActiveX Data Object 6.1 Library to this project. This library will help us in running the SQL query in Microsoft Excel and we can use the properties and methods while writing the codes.

To add the Microsoft ActiveX Data Object 6.1 reference, just click on Tools Menu and then click on Reference and select Microsoft ActiveX Data Object 6.1 from the available libraries reference.

image 7
Reference window

Let’s write the code on click event of Browse button.

Private Sub cmdBrowse_Click()
     Dim txtFullPath
    'Displays the standard Open dialog box and gets a file name from the user without actually opening any files.
     txtFullPath = Application.GetOpenFilename( _
               FileFilter:="Excel Files (*.xls; *.xlsx; *.xlsm), *.xls; *.xlsx; *.xlsm", _
               Title:="Select Excel File")
      If txtFullPath = False Then Exit Sub
      txtWorkbookPath.Value = txtFullPath
 End Sub

Write the below code on double click event of txtWorkbookPath

Private Sub txtWorkbookPath_DblClick(ByVal Cancel As MSForms.ReturnBoolean)     
   Dim txtFullPath
    'Displays the standard Open dialog box and gets a file name from the user without actually opening any files.
     txtFullPath = Application.GetOpenFilename( _
               FileFilter:="Excel Files (*.xls; *.xlsx; *.xlsm), *.xls; *.xlsx; *.xlsm", _
               Title:="Select Excel File")
      If txtFullPath = False Then Exit Sub
      txtWorkbookPath.Value = txtFullPath
 End Sub

Write the below code on click event of cmdReset
Private Sub cmdReset_Click()
   Dim iConfirmation As VbMsgBoxResult
   iconfimration = MsgBox("Do you want reset the Query Executor?", vbYesNo + vbQuestion, "Confirmation")
   If iConfirmation = vbNo Then Exit Sub
   'Deleting Previous Query Result
   ThisWorkbook.Sheets("Query Executor").Rows("24:" & Rows.Count).Clear
    ThisWorkbook.Sheets("Query Executor").txtWorkbookPath.Value = ""
    ThisWorkbook.Sheets("Query Executor").txtSQLQuery.Value = ""
  MsgBox "Done"
End Sub
Let’s move to Module window and start writing the code to run the SQL code.

Write the sub procedure in Module window.

Sub Run_SQL_Query()
   On Error GoTo err_handler 	
   
   Dim MyConnect As String  
   
   Dim MyRecordset As ADODB.Recordset

   Dim MySQL As String

   ExcelFile = ThisWorkbook.Sheets("Query Executor").txtWorkbookPath.Value

   MySQL = ThisWorkbook.Sheets("Query Executor").txtSQLQuery.Value
   
 
    MyConnect = "Provider=Microsoft.ACE.OLEDB.12.0;" & _
                "Data Source= " & ExcelFile & ";" & _
                "Extended Properties=Excel 12.0"
 
    Set MyRecordset = New ADODB.Recordset
 
    MyRecordset.Open MySQL, MyConnect, adOpenStatic, adLockReadOnly

   For i = 0 To MyRecordset.Fields.Count - 1
    
        With ThisWorkbook.Sheets("Query Executor").Cells(24, i + 2)
        	.Value = MyRecordset.Fields(i).Name
        	.Interior.Color = RGB(117, 113, 113)
        	.Font.Color = vbWhite
        End With
        
    Next
    
    ThisWorkbook.Sheets("Query Executor").Range("B25").CopyFromRecordset MyRecordset

    ThisWorkbook.Sheets("Query Executor").UsedRange.EntireColumn.AutoFit
     Exit Sub
    err_handler:
         MsgBox "Error! " & Err.Description
        	With ThisWorkbook.Sheets("Query Executor").Range("B25")
               	.Value = "Error! " & Err.Description
                	.Font.Color = vbRed
            End With
End Sub


Now, we are done with writing the VBA procedure to run the SQL Code. You can run the tool and execute any SQL Select query.

YouTube tutorial to develop SQL Query Executor in MS Excel

Download the Demo File

Please click on below button to download the sample file used for SQL Query Executor.

Download
Click to download

LEAVE A REPLY

Please enter your comment!
Please enter your name here

Captcha verification failed!
CAPTCHA user score failed. Please contact us!

Recommended Reads

Latest Articles