Here's what I do to work around the limitations of Microsoft Query in Excel 2007:
- A produce a dummy query (
SELECT NULL AS Test
, for example) in Microsoft Query and insert it into the worksheet.
- Right-click on the table that MS Query just inserted and click Table->Edit External Data Properties.
- Click on the Connection Properties button, then click the Definition tab.
- In the Command Text section, write out or paste in the query that you want, using the usual '
?
' convention for parameters, then click OK.
- Click OK to exit the External Data Properties window.
- Right click on the table again, and select Table->Parameters to bind the parameters in the usual way.
The idea is the bypass the GUI that MS Query provides, which has some arbitrary limitations that the underlying engine does not.
This works for many complex queries, but not all. When I encounter a query that MS Query refuses to digest at all, I either refactor the query (when feasible) or create a VIEW
on the SQL server and query against that.
与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…