sql addin

Excel SQL Add-In

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:
sql addin
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

sql commands
The AddIn supports multiple OLEDB drivers. The following types of queries can be executed:

  1. 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)
  2. 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)
  3. 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)
  4. 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
  5. Merge worksheets

    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:
    merge worksheets excel
    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

    diff 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:
    comparing worksheets excel
    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

    import excel
    To import (and link) an external worksheet simply click on Link external Excel worksheet. The following form will appear:
    import excel
    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

    import access
    To import (and link) an external Access table simply click on Link external Access table. The following form will appear:
    import access excel
    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:
    Excel SQL: Refreshing or editing the SQL query

    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:

    Excel SQL: Run SQL from MS Excel: Step 1

    Click Run query to run the query and paste the results:

    Excel SQL: Run SQL from MS Excel: Step 2

    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

    Download the Excel SQL AddIn

    Have questions or issues with purchasing/downloading the AddIn? Write directly to me at analystcave(at)gmail.com!

Comments are closed.