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.

Saving file

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

Rename

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

Removing gridlines

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

User Interface

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.

VBA button

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

VBA for Excel

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.

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.

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.

Reference window

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

Write the below code on double click event of txtWorkbookPath

Write the below code on click event of cmdReset

Let’s move to Module window and start writing the code to run the SQL code.

Write the sub procedure in Module window.

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.

Download
Click to download

LEAVE A REPLY

Please enter your comment!
Please enter your name here