Microsoft Query allows you use SQL directly in Microsoft Excel, treating Sheets as tables against which you can run choice statements with JOINs, UNIONs and more. Often Microsoft Query statements will be more effective than Excel formulas or a VBA Macro. A Microsoft Query ( aka MS Query, aka Excel Query ) is in fact an SQL SELECT Statement. Excel angstrom well as Access use Windows ACE.OLEDB or JET.OLEDB providers to run queries. Its an incredible often untapped tool underestimated by many users !
What can I do with MS Query ?
Using MS Query in Excel you can extract data from diverse sources such as :
- 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
step by Step – Microsoft Query in Excel
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.
Bạn đang đọc: How to create a Microsoft Query in Excel (Excel Query)
I will extract data from an External Excel file called MOCK DATA.xlsx. In this file I have a number of Male/Female mock-up customers. I will want to create a dim-witted question to calculate how many are Male and how many Female.
Open the MS Query (from Other Sources) wizard
Go to the DATA Ribbon Tab and cluck From other Sources. Select the last option From Microsoft Query.
Select the Data Source
Next we need to specify the Data Source for our Microsoft Query. Select Excel Files to proceed .
Select Excel Source File
now we need to select the Excel file that will be the source for our Microsoft Query. In my example I will select my current Workbook, the same from which I am creating my multiple sclerosis Query .
Select Columns for your MS Query
The Wizard now asks you to select Columns for your MS Query. If you plan to modify the MS Query manually late plainly snap OK. Otherwise select your Columns.
Return Query or Edit Query
now you have two options :
- 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 question in Microsoft Query option you can now open the SQL Edit Query window by hitting the SQL clitoris. When you are done hit the return clitoris ( the one with the open door ).
When you are done modifying your SQL affirmation ( as I in former step ). Click the Return data push button in the Microsoft Query window.
This should open the Import Data windowpane which allows you to select when the data is to be dumped.
Lastly, when you are done click OK on the Import Data window to complete running the question. You should see the consequence of the question as a new Excel mesa :
As in the window above I have calculated how many of the records in the original mesa where Male and how many Female .
AS you can see there are quite a lot of steps needed to achieve something potentially pretty simpleton. Hence there are a couple of alternatives thanks to the might of VBA Macro… .
MS Query – Create with VBA
If you don ’ t want to use the SQL AddIn another direction is to create these queries using a VBA Macro. Below is a quick macro that will allow you write your question in a simple VBA InputBox at the selected crop in your worksheet.
Just consumption my VBA Code Snippet :
Sub ExecuteSQL() Attribute ExecuteSQL.VB_ProcData.VB_Invoke_Func = "S\n14" 'AnalystCave.com 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) With qt .CommandType = xlCmdSql .CommandText = SQL .Name = Int((1000000000 - 1 + 1) * Rnd + 1) .RefreshStyle = xlOverwriteCells .Refresh BackgroundQuery:=False End With Exit Sub ErrorHandl: MsgBox "Error: " & Err.Description: Err.Clear End Sub
merely 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 .
Learning SQL with Excel
Creating MS Queries is one thing, but you need to have a pretty good clasp of the SQL lyric to be able to use it ’ second true electric potential. I recommend using a childlike Excel database ( like Northwind ) and practicing versatile queries with JOINs .
Alternatives in Excel – Power Query
Another way to run queries is to use Microsoft Power Query ( besides known in Excel 2016 and improving as Get and Transform ). The AddIn provided by Microsoft does require cognition of the SQL Language, preferably allowing you to click your way through the data you want to tranform.
MS Query five Power Query Conclusions
MS Query Pros : Power Query is an amazing joyride, however, it doesn ’ t wholly cancel Microsoft Queries. What is more, sometimes using Microsoft Queries is quicker and more convenient and here is why :
Xem thêm: Add or Subtract Time in Excel
- 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 …