Create a 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?

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.

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

Let’s start creating this interesting application from scratch!

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.

If you will not select .XLSM then Excel will not retain your VBA code once you will close the file.

Now, let’s rename the sheet1 to Query Executor.

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

Design the User Interface in Query executor sheet as per below image.

Now, we need to write the code in Visual Basic of 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.

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

Here, we need to insert a blank module to write all the Procedure 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.

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.

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

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.

Please watch the YouTube tutorial.

Please click on below button to download the sample file.