How to Query SQL in Excel with VBA and Dynamic Parameters
There are many tools designed to build dashboards and business intelligence tools; however, there may be occasions when it is more sensible to build your dashboard in Microsoft Excel. Such occasions might include when:Your company does not have licenses for other tools Your audience is more familiar with Excel You need to have a quick turnaround time for your deliverable You want to have more customization options for graphs and text
The first step to building a dashboard is retrieving the relevant data. Often, this is achieved using an SQL query. You can query SQL in Excel using one of two methods. The first method is to use the data tab and create a data connection with an SQL query. This will handle most scenarios, but it can be cumbersome for complex queries with parameters. The second method is to embed an SQL query into Microsoft Excel using VBA, or Visual Basics for Applications.
Embedding the query with VBA has several advantages. First, you will be able to utilize the features of VBA before and after your query. You can create variables, manipulate your spreadsheet, adjust screen refresh and updates, etc. Second, you can easily work with dynamic parameters and SQL conditions. This means you can read information from your dashboard and use the data in the WHERE or GROUP BY clauses of your query. Now we’re starting to sound like a dashboard!
You’ll need to enable the Developer tab on your Excel ribbon. You can do this by clicking on File –> Options –> Customize Ribbon –> Developer –> Add.
Next, click the Developer tab and select the first option: Visual Basic. This will open a new window where you can write VBA. In this area, we will create a connection to SQL, provide our query, and perform any other unique tasks to customize our data collection for the dashboard. The VBA code for this post can be found on the Actionable Business Analytics GitHub account.
Excel SQL Query in VBA – Establish Variables
In the Visual Basic Editor hotbar, click on Tools ->References and scroll down to “Microsoft ActiveX Data Objects 2.8 Library”. Check the box and click OK to enable the reference.
The first section of the Visual Basic code begins the Sub and initializes the necessary variables:
‘Process to Query SQL using VBA (Excel) and storing data in the worksheet
Dim cnn As New ADODB.Connection
Dim rst As New ADODB.Recordset
Dim ConnectionString As String
Dim StrQuery As String
Dim Start_Date As String
Dim End_Date As String
This portion is fairly intuitive. We establish cnn to connect to SQL in Excel. We will provide the connection information later to ConnectionString. The variable rst will store the data retrieved from the query. StrQuery will be our actual SQL query. Start_Date and End_Date are examples of SQL conditions or dynamic parameters that you could use in your query to specify a from date and end date.
Excel SQL Query in VBA – Use Dynamic Parameters
The next section of the code provides an example of reading data from your spreadsheet or dashboard into VBA to be used as dynamic parameters.
‘Read conditions off the worksheet
Start_Date = Sheets(“Data Pull”).Range(“B1”).Value
End_Date = Sheets(“Data Pull”).Range(“B2”).Value
This code simply tells VBA to store the value from cell B1 from the worksheet “Data Pull” to the variable Start_Date.
Excel SQL Query in VBA – Establish Connection and Query
We will now move forward to establishing the connection to SQL in Excel and storing the SQL query.
‘Setup the connection string for accessing MS SQL database using Windows Authentication
‘Make sure to change:
ConnectionString = “Provider=SQLOLEDB.1;Data Source=SERVER;Packet Size=4096;Use Encryption for Data=False;Initial Catalog=DATABASE;Trusted_connection=yes;”
‘Opens connection to the database
‘Timeout error in seconds for executing the entire query; this will run for 15 minutes before VBA timesout, but your database might timeout before this value
cnn.CommandTimeout = 900
‘This is your actual MS SQL query as a string that you need to run; you should check this query first using a more robust SQL editor (such as SQL Server) to ensure your query is valid
StrQuery = “select * from Table1 a left outer join Table2 b on b.ID = a.ID” & _
“where dateColumn > ” & Start_Date & ” and dateColumn < ” & End_Date & ” and Variable3 = condition3″
In this section, we first provide connection details to the ConnectionString variable. You’ll need to provide your specific SERVER and DATABASE. This code uses a trusted connection (Windows Authentication). You can also specify User ID=myUsername; and Pasword=myPassword; in order to provide your specific information.
Next, we open the ConnectionString with our connection variable, cnn. We provide a timeout value in case something is not functioning properly. Lastly, we update the StrQuery variable with the actual SQL query used to gather the data. Note that you continue to a new line in a VBA string by typing & _. It is extremely difficult to update and debug a query in VBA because it is simply a string that is passed to the connection. Therefore, you should always use a separate editor, such as SQL Server, to troubleshoot and test your query before importing it to Visual Basic.
Excel SQL Query in VBA – Run the Excel SQL Query
Our final step is to run the query and place the results in a specific location.
‘Performs the actual query
rst.Open StrQuery, cnn
‘Dumps all the results from the StrQuery into cell A2 of the specified worksheet
Sheets(“Data Pull”).Range(“A2”).CopyFromRecordset rst
We have VBA run the Excel SQL query and store all the data in the result variable, rst. Then, we tell VBA to dump the Excel SQL data into cell A2 of the Data Pull worksheet. It is also a good idea to clear the data from the worksheet before storing new data.
Now we have our data with dynamic parameters stored in Excel, and it is ready to be used in pivot tables, graphs and custom filters for our dashboard.
As always, try it out and let us know if you have any questions or comments in the comments section below.