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.