Using Microsoft Office for Mac as a Relational Database

By Jim Gordon, co-author of Office 2011 for Mac All-in-One For Dummies.

Part 20 - Editing queries

This page explains how to modify an existing query.

EXCEL 2016
  1. Select a cell in the QueryTable
  2. On the Data tab of the Ribbon click Connections > Properties
  3. On the Definition tab of the Connection Properties dialog click Edit Query
EXCEL 2011
If you made your query using Microsoft Query and you accepted the defaults when you brought your result set into Excel, you must first convert your result set to a range before you can edit the query in Microsoft Query. To change the result set to a range, right-click anywhere in the result set data and choose Convert to Range from the pop-up menu.

After you you have converted the result set to a range,
right-click a second time in the result set. The Edit Query option is available to you on the pop-up menu.

After converting to a range, from the contextual right-click menu you can also choose Data Range Properties (see Part 9), Parameters (see Part 19), and Refresh Data (see Part 8).

Customize this VBA code sample to change the query associated with a query table:

Sub AlterSQLQuery()
Set qt = Sheets("Sheet1").QueryTables(1)
qt.Sql = "SELECT * FROM employees  WHERE (city='London')"
End Sub

Here's another example. This code alters a parameter query:

Sub AlterParameterQuery()
Set qt = Sheets("sheet1").QueryTables(1)
qt.Sql = "SELECT * FROM authors WHERE (city=?)"
Set param1 = qt.Parameters.Add("City Parameter", _
param1.SetParam xlConstant, "Oakland"
End Sub

The first time you add a query to a worksheet, its name is Table1.
Each time you refresh the query with this code, the name of the table increments. Table1 becomes Table2, and so on. Your VBA query can be any valid SQL query supported by SQL LITE. As with Microsoft Query, these queries are read-only. You can't alter, make, or drop any tables, records, or rows with SQL in Office 2011. If this functionality is important to you, please let Microsoft know by telling them here.

You can make a parameter query in VBA by using a question mark in the query.
I haven't figured out the VBA syntax to change the prompt in the parameter dialog. As far as I can tell, you have to change the parameter prompt as shown in Part 19.

qt.Sql = "SELECT * FROM employees  WHERE (city=?)"

If your query table originated as the result of adding a query table via VBA, you can omit the ConverToRange line in the code sample above.
Part 1
Part 19 (Previous)
Part 21 (Next)