如何在Excel中无法以图形方式显示的外部数据查询中添加参数? [英] How to add parameters to an external data query in Excel which can't be displayed graphically?

问题描述

我经常使用 MS Excel 的 Get External Data 来创建简单的报告 - 对数据库运行查询并在 Excel 中很好地显示.Excel 的强大功能，如过滤和数据透视表以及用户熟悉的界面，使其非常适合这一点.但是，Microsoft Query 的一个限制是您无法向无法以图形方式显示的查询添加参数，这极大地限制了您可以编写的 SQL.

I often use MS Excel's Get External Data to create simple reports - running queries against databases and displaying nicely in Excel. Excel's great features like filtering and pivot tables and familiar interface for users make it quite good for this. However, one limitation with Microsoft Query is you can't add parameters to queries that can't be displayed graphically, which considerably limits the SQL you can write.

不能以图形方式显示的查询中不允许使用参数"错误有什么解决办法吗?

Is there any solution to the error "parameters are not allowed in queries that can't be displayed graphically"?

推荐答案

如果您有 Excel 2007，您可以编写 VBA 来更改工作簿中的连接(即外部数据查询)并更新 CommandText 属性.如果您只是在需要参数的地方添加 ?，那么下次刷新数据时，它会提示输入连接值！魔法.当您查看 Connection 的属性时，Parameters 按钮现在将处于活动状态并且可以正常使用.

If you have Excel 2007 you can write VBA to alter the connections (i.e. the external data queries) in a workbook and update the CommandText property. If you simply add ? where you want a parameter, then next time you refresh the data it'll prompt for the values for the connections! magic. When you look at the properties of the Connection the Parameters button will now be active and useable as normal.

例如我会编写一个宏，在调试器中逐步执行它，并使其适当地设置 CommandText.完成此操作后，您可以删除宏 - 它只是更新查询的一种方式.

E.g. I'd write a macro, step through it in the debugger, and make it set the CommandText appropriately. Once you've done this you can remove the macro - it's just a means to update the query.

Sub UpdateQuery
    Dim cn As WorkbookConnection
    Dim odbcCn As ODBCConnection, oledbCn As OLEDBConnection
    For Each cn In ThisWorkbook.Connections
        If cn.Type = xlConnectionTypeODBC Then
            Set odbcCn = cn.ODBCConnection

            ' If you do have multiple connections you would want to modify  
            ' the line below each time you run through the loop.
            odbcCn.CommandText = "select blah from someTable where blah like ?"

        ElseIf cn.Type = xlConnectionTypeOLEDB Then
            Set oledbCn = cn.OLEDBConnection
            oledbCn.CommandText = "select blah from someTable where blah like ?" 
        End If
    Next
End Sub

这篇关于如何在Excel中无法以图形方式显示的外部数据查询中添加参数?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！