sql addin

Excel SQL Add-In

1 Star2 Stars3 Stars4 Stars5 Stars (9 votes, average: 4.56 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.

21 thoughts on “Excel SQL Add-In”

    1. Hi @Tomislav, glad to help. See an example below to start you off. It completes the [Financial Status] column. You can probably do it better using some LEFT JOIN, but this is quick and dirty and still should be fast. I encourage you to like my FB or sign up to Twitter for more cool VBA updates!

      SELECT
      [Name],
      Iif(
      [Financial Status] IS NULL,
      (SELECT TOP 1 input2.[Financial Status] FROM [Input$] as input2 WHERE input2.[Name] = input1.[Name] AND input2.[Financial Status] <> ''),
      [Financial Status]
      ) FROM [Input$] as input1

  1. So this is not the ExcelSQL add-in provided by Noromaa Solutions, last version was 3.1, August 12th, 2002?

    It’s still available here, at least: ###

    I’m not sure if there’s copyright issues on the name, but you might want to tweak it just to avoid confusion.

    Of course, Mikko Noromaa seems to have abandoned the product; you can only find downloads at other sites and Noromaa solutions is only selling some brainwave-stimulating screen saver. This person was trying hard to find him in 2013 because his whole company was using it and wanted some customizations:
    ###

    If you can get it touch with him, maybe you could take over this product! It’s hard to find, but has loyal fans…really saved my butt on many occasions.

    1. Thanks for your input! No, this is by no means associated with Mr Mikko, you mention. I had a look at your link and it seems that it’s a Shareware solution. Unfortunately, the link seems to transfer me to some advertising page so I can’t say anything else about this.

      My SQL AddIn is a free open source solution (simple VBA project were you can edit the VBA code), but I am open for ideas to extend it. Let’s just keep in mind that Microsoft has introduced the Power Query Excel AddIn which is also addressing this space quite extensively – so it would not make sense to duplicate any features already available in that solution.

      Feel free to suggest any specific extensions!

      1. I just realized with a start that there may be some overlap here with an add-in I wrote for folks in the office around me to use for the huge amounts of database work we do.
        It can do both “QueryTables” and “PivotTables” , but anyway can create both from SQL queries against any database you’ve got an ODBC or OLE driver for. Your add-in is focused on SQL queries from internal Excel tables, so mine may not be of interest, or it might be a good compliment.
        Either way, if you’re interested, I’m happy to turn over the code to you, as long as you give it away for free like yours. I’d do it myself, but you’re the one with the very cool website and some readers.
        Feel free to contact at my first name dot my last name at gmail dot com!

  2. Rather than branding this as an Addin, I think you should refer to it as a wizard. Wizards are great as they are independent of any addin code. Something like “Data Range Wizard – for using SQL in Excel” .
    note that the Excel GUI refers to QueryTables as “Data Ranges”. VBA programmers might know them as “QueryTables. But to me “Data Range” sounds more friendly add “SQL” as this is what it;’s all about.

    eg In essence you sales pitch is: Using the “Data Range Wizard” allows you to more easily use built in Excel features to manipulate data stored in Excel and elsewhere using SQL. It compliments the more advanced features provided by Microsofts PowerQuery by allowing simple SQL statements to be used that access and write to Excel Worksheets. It can save considerable work as it reduces the need to write complex VBA code to manipulate sets of data stores in your excel files and elsewhere.

    Also I would improve the video demonstration as it is very slow to watch, dull and does not demo enough examples. nice tool though so a big thanks.

    1. Thanks Harvey! I will definitely consider this idea. Maybe it would make sense to rename some of the buttons. My aim with the AddIn is simply to encourage VBA/Excel users to start leverage SQL instead of writing horrid / long / erroneous macros.

      Considering the video – thanks for the comment, indeed it was a quick and dirtly venture :). I might need to spend more time on a decent demo, although I making a good demo actually takes more time than writing some of the underlying code (lol)!

  3. Hey

    I want to export excel data to sql server. And i want to do it from excel side ( importing from sql server we dont want) can you please tell me if it is possible with this add-in

    As i am able to just the queries to import the data from database

    Thanks in anticipation !

  4. Dear all,

     

    I have this errors :

     

    Run-time error ‘-2147418105 (80010007)’
    Automation error
    The callee (server [not server application]) is not available and disappeared; all connections are invalid. The call may have executed.

     

    How to fix ?Plz advice.

Leave a Reply

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