This page explains how to modify
an existing query.
EXCEL 2016
Select a cell in the
QueryTable
On the Data tab of the Ribbon
click Connections > Properties
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()
ActiveSheet.ListObjects("TableName").ConvertToRange
Set qt = Sheets("Sheet1").QueryTables(1)
qt.Sql = "SELECT * FROM employees WHERE
(city='London')"
qt.Refresh
End Sub
Here's another example. This code alters a parameter query: Sub AlterParameterQuery()
ActiveSheet.ListObjects("TableName").ConvertToRange
Set qt = Sheets("sheet1").QueryTables(1)
qt.Sql = "SELECT * FROM authors WHERE (city=?)"
Set param1 = qt.Parameters.Add("City Parameter", _
xlParamTypeVarChar)
param1.SetParam xlConstant, "Oakland"
qt.Refresh
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.