SQL (MS Query) is a NATIVE Excel feature. So why use an AddIn? Well.. Microsoft has done a good job of make the use of SQL in Excel as hard and as inconvenient as possible via the Data ribbon tab. Hence, creating MS QueryTables or executing simple SQL Commands like UPDATE is obscured and hard to use on a daily basis. This is were the Excel SQL Add-In comes in.
Excel is basically an easy to manipulate, loosely coupled database. We tend to forget that. Often many reach out to VBA macros, complicated PivotTables or simply complex Excel formulas to achieve tasks that would be otherwise simple if we looked at Excel from the perspective of an SQL database. Why not? Many of you probably don’t know that Excel actually supports SQL queries! If you still don’t understand the opportunities – read on, as you are in for a treat. Using SQL in Excel will allow you to process data queries in seconds compared to VBA code which might even process data in an hour! To make SQL more easily accessible in everyday tasks I created this Excel SQL Add-In, which basically replaces the somewhat cumbersome From Microsoft Query wizard available from the Data ribbon tab.
So let’s jump right into this goodness…
Introduction
Let’s start by introducing the Ribbon controls:
The Excel SQL Add-In adds an additional ribbon tab to your Excel ribbon. It is an (entirely VBA) Add-In with no locked features, that facilitates the process of creating custom SQL queries (MS Query) based on the OLEDB Data Drivers. What is more, the SQL AddIn is equipped with predefined quick SQL queries for certain jobs e.g. like merging worksheets or comparing worksheets (diff – finding differences).
What is important to remember – All SQL queries created with the SQL Add-In will work WITHOUT the AddIn!
SQL SELECT Query
The AddIn supports multiple OLEDB drivers. The following types of queries can be executed:
Run SQL from Excel (Jet.OLEDB) (xls) – allows you to run SQL queries on Excel data in the older format of e.g. Excel 2003 (also based on data in other Excel worksheets or workbooks)
Run SQL from Excel (Ace.OLEDB) (xlsx,xlsm,xlsb) – allows you to run SQL queries on Excel data in the new format of Excel files e.g. Excel 2007 and above (also based on data in other Excel worksheets or workbooks)
Run SQL from MS SQL (SQLOLEDB) – allows you to run SQL queries on MS SQL data (from MS SQL databases to an Excel query table)
Run SQL (UPDATE or INSERT) on current Excel file (Ace.OLEDB) – allows you to run SQL UPDATE or INSERT queries on the current workbook e.g. add additional rows from a SELECT or UPDATE rows in a worksheet based on data in other worksheets or workbooks
Merge worksheets
The features allows you to merge any number of Worksheets (by multiselecting) into a single joint worksheet. The condition is that all worksheets need to have identical columns. The SQL AddIn will create a SQL query that can be refresh at any time.
Example
Click the Merge worksheets button to open the following UserForm:
Next provide the Output range where you want the query to be created, select the worksheets you want to merge and click Merge.
Find differences between worksheets
The feature allows you to compare 2 worksheets as long as their columns are identical (and the header names are identical).
Example
Click the Find differences between worksheets button to open the following UserForm:
Provide the Output range where you want the query to be created, select 2 worksheets you want to compare. Next select a key column on which the 2 worksheets need to be join/cross-referenced and click Diff.
The output will include values of 1 in columns where there a differences between the worksheets and values of 0 where both worksheets are identical.
Link external Excel worksheet
To import (and link) an external worksheet simply click on Link external Excel worksheet. The following form will appear:
Provide the Output range where you want the query to be created, provide the file path to the Excel file (or click ... to open a file dialog) and hit Load worksheets. Next select the worksheet you want to import and hit Import.
Link external Access worksheet
To import (and link) an external Access table simply click on Link external Access table. The following form will appear:
Provide the Output range where you want the query to be created, provide the file path to the Access database file (or click ... to open a file dialog) and hit Load tables. Next select the table you want to import and hit Import.
Updating/editing the queries
As I mentioned all queries are native Excel QueryTables hence they can be editted / updated without the SQL AddIn. To do that simply…
Right-click the query table to edit/refresh:
Hit Refresh to refresh (reload) the query or Edit Query to modify the SQL query or modify the query properties.
Excel SQL Examples
Although much underestimated, SQL is powerful stuff, especially in Excel. All these features are native to Excel, the AddIn simply makes life easier when creating the MS Queries. Assuming you have the SQL AddIn installed let’s play with some basic examples…
Hello World in SQL
Let’s start with an opening example. Let’s assume we have a simple worksheet containing 2 columns – Col1 and Col2. If you want to create a query that will filter only items in which the Col2 value is ‘Name1’ proceed as follows:
Click on the Run button and fill the form:
Click Run query to run the query and paste the results:
Other examples
Excel SQL example 1: Loading data from an external Excel file
SELECT * FROM `C:Data.xlsx`.`Sheet1$`
Excel SQL example 2: Joining 2 external files
Finding records repeated in both Workbooks
SELECT s1.Name FROM `C:Data1.xlsx`.`Sheet1$` as s1
INNER JOIN `C:Data2.xlsx`.`Sheet1$` as s2 ON s1.Name = s2.Name
Excel SQL example 3: Consolidating an internal worksheet with an external Excel file
Let’s consolidate data from Sheet2 with data from Sheet1 from an external Excel file
SELECT * FROM `C:Data.xlsx`.`Sheet1$`
UNION ALL
SELECT * FROM [Sheet2$]
Excel SQL example 4: Updating a Worksheet using LEFT OUTER JOIN
Let’s say we have a master worksheet with data [Sheet1] and another worksheet [Sheet2] where we keep only updated records. How to update the master worksheet? A simple as this:
SELECT S1.A, Iif(IsNull(S2.A),S1.B,S2.B)
FROM [Sheet1$] as S1
LEFT OUTER JOIN [Sheet2$] as S2 ON S1.A = S2.A
Summary: Excel SQL
I prefer using SQL in Excel over writing any VBA macro if possible. The reasons for this are the following:
Editable and refreshable like a PivotTable – The SQL Add-In will insert a query table into the worksheet. Which is editable and refreshable! That means that you refresh it just as you would with a PivotTable
Quick development – writing the same functionality in VBA would often take a lot of time and testing
Less errors – the SQL query is less prone to the usual VBA errors provided that the structure of the data does not change significantly
Performance – the Jet.Oledb driver is much more efficient than any written VBA code with loops. Try any simple example
