sql addin

Excel SQL Add-In

1 Star2 Stars3 Stars4 Stars5 Stars (13 votes, average: 4.54 out of 5)
Loading...

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.

The Excel SQL Add-In, from AnalystCave.com, is a free and open source (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 AddIn
sql commands
SQL Commands (MS Query)
merge worksheets
Merge worksheets
diff worksheets
Find differences between worksheets
import excel
Link external Excel worksheet
import access
Link external Access worksheet

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.

If you want a quick peak into the SQL AddIn feel free to view this video first:

So let’s jump right into this goodness…

Introduction

Let’s start by introducing the Ribbon controls:

sql addin
The SQL AddIn Ribbon

The Excel SQL Add-In adds an additional ribbon tab to your Excel ribbon. It features the following types of features:

Excel SQL Commands (MS Query)

sql commands

  • 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

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
SQL AddIn: Merging worksheets

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
SQL AddIn: Diffing 2 worksheets (finding differences)

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
SQL AddIn: Import an external Excel worksheet

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
SQL AddIn: Import an external Access table

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
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
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
Run SQL from MS Excel: Step 2

Other examples

Excel SQL example 1: Loading data from an external Excel file

SELECT * FROM <code>C:Data.xlsx</code>.<code>Sheet1$</code>

Excel SQL example 2: Joining 2 external files
Finding records repeated in both Workbooks

SELECT s1.Name FROM <code>C:Data1.xlsx</code>.<code>Sheet1$</code> as s1 
INNER JOIN <code>C:Data2.xlsx</code>.<code>Sheet1$</code> 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 <code>C:Data.xlsx</code>.<code>Sheet1$</code>
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

Feel free to download the AddIn (Excel 2007 and newer):


The SQL AddIn is available under the MIT license.

Simply the best place to learn VBA!

Error: Please enter a valid email address

Error: Invalid email

Error: Please enter your first name

Error: Please enter your last name

Error: Please enter a username

Error: Please enter a password

Error: Please confirm your password

Error: Password and password confirmation do not match