You can use Microsoft Query in Excel to retrieve data from an Excel Workbook as well as External Data Sources using SQL SELECT Statements. Excel Queries created this way can be refreshed and rerun making them a comfortable and efficient tool in Excel
A Microsoft Query (aka MS Query, aka Excel Query) is in fact an SQL SELECT Statement. Excel as well as Access use Windows ACE.OLEDB or JET.OLEDB providers to run queries. Its an incredible often untapped tool underestimated by many users!
You can extract data from:
- Excel Files – you can extract data from External Excel files as well as run a SELECT query on your current Workbook
- Access – you can extract data from Access Database files
- MS SQL Server – you can extract data from Microsoft SQL Server Tables
- CSV and Text – you can upload CSV or tabular Text files
How to Create a Microsoft Query
In this step by step tutorial I will show you how to create an Microsoft Query to extract data from either you current Workbook or an external Excel file.
I will extract data from an External Excel file called Data Source.xlsx situated in C:\.
Open the MS Query (from Other Sources) wizard
Select the Data Source
Select Excel Source File
Select Columns for your MS Query
Return Query or Edit Query
Return Data to Microsoft Excel this will return your query results to Excel and complete the Wizard
View data or edit query in Microsoft Query this will open the Microsoft Query window and allow you to modify you Microsoft Query
Optional: Edit Query
If you select the View data or edit query in Microsoft Query option you can now open the SQL Edit Query window by hitting the SQL button. When you are done hit the return button (the one with the open door).
MS Query Trick
Attribute ExecuteSQL.VB_ProcData.VB_Invoke_Func = "S\n14"
On Error GoTo ErrorHandl
Dim SQL As String, sConn As String, qt As QueryTable
SQL = InputBox("Provide your SQL Query", "Run SQL Query")
If SQL = vbNullString Then Exit Sub
sConn = "OLEDB;Provider=Microsoft.ACE.OLEDB.12.0;;Password=;User ID=Admin;Data Source=" & _
ThisWorkbook.Path & "/" & ThisWorkbook.Name & ";" & _
"Mode=Share Deny Write;Extended Properties=""Excel 12.0 Xml;HDR=YES"";"
Set qt = ActiveCell.Worksheet.QueryTables.Add(Connection:=sConn, Destination:=ActiveCell)
.CommandType = xlCmdSql
.CommandText = SQL
.Name = Int((1000000000 - 1 + 1) * Rnd + 1)
.RefreshStyle = xlOverwriteCells
ErrorHandl: MsgBox "Error: " & Err.Description: Err.Clear
Just create a New VBA Module and paste the code above. You can run it hitting the CTRL+SHIFT+S Keyboardshortcut or Add the Macro to your Quick Access Toolbar.
Power Query vs Microsoft Query
Lastly, I would like to tackle the question of Why use MS Queries when I have Power Query? Microsoft has done a good job of understanding that users need a tool to transform data, but often don’t have the knowledge to use the SQL Language to create SELECT queries. That is why they created the Power Query AddIn (as part of Power BI Suite).
MS Query Pros: Power Query is an awesome tool, however, it doesn’t entirely invalidate Microsoft Queries. What is more, sometimes using Microsoft Queries is quicker and more convenient and here is why:
- Microsoft Queries are more efficient when you know SQL. While you can click your way through to Transform Data via Power Query someone who knows SQL will likely be much quicker in writing a suitable SELECT query
- You can’t re-run Power Queries without the AddIn. While this obviously will be a less valid statement probably in a couple of years (in newer Excel versions), currently if you don’t have the AddIn you won’t be able to edit or re-run Queries created in Power Query
MS Query Cons: Microsoft Query falls short of the Power Query AddIn in some other aspects however:
- Power Query has a more convenient user interface. While Power Queries are relatively easy to create, the MS Query Wizard is like a website from the 90’s
- Power Query stacks operations on top of each other allowing more convenient changes. While an MS Query works or just doesn’t compile, the Power Query stacks each transform operation providing visibility into your Data Transformation task, and making it easier to add / remove operations
In short I encourage learning Power Query if you don’t feel comfortable around SQL. If you are advanced in SQL I think you will find using good ole Microsoft Queries more convenient. I would compare this to the Age-Old discussion between Command Line devs vs GUI devs…