SQL 如何在 Excel 外部数据请求中使用参数？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/612664/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How do I use a parameter in an Excel external data request?
提问by witttness
I can already use Excel (2007) to import data from SQL Server 2005. I add a data connection and I enter a custom SQL query that retrieves my desired data. Cool.
我已经可以使用 Excel (2007) 从 SQL Server 2005 导入数据。我添加了一个数据连接并输入了一个自定义 SQL 查询来检索我想要的数据。凉爽的。
But what I'd like to add is the ability to parameterize that query based on a value found in a known cell on the spreadsheet.
但我想补充的是，能够根据在电子表格的已知单元格中找到的值来参数化该查询。
My query would go from
我的查询将来自
SELECT * FROM dbo.MyDataTable WHERE Col1 = 'apples'
to something like
像
SELECT * FROM dbo.MyDataTable WHERE Col1 = 'Cell("B2")'
Is this possible? If so, how?
这可能吗？如果是这样，如何？
采纳答案by nekomatic
If you're using MS Queryto get the data into Excel, this pageshows how to use a value from a cell on the worksheet as a parameter in your query.
如果您使用 MS Query将数据导入 Excel，此页面显示如何将工作表上单元格中的值用作查询中的参数。
回答by Robert Mearns
Try your structuring your code as follows:
尝试按如下方式构建代码：
Dim strSQL as String
Dim strFruit As String
strFruit = CStr(ThisWorkbook.Sheets("Sheet1").Range("A1").FormulaR1C1)
strSQL = "SELECT * FROM dbo.MyDataTable WHERE Col1 = '" & strFruit & "'"
I have found it useful to load the parameter into a variable first, before merging it into the SQL query.
我发现先将参数加载到变量中很有用，然后再将其合并到 SQL 查询中。
It makes your SQL string more readable and allows you to check/clean the parameter before using it.
它使您的 SQL 字符串更具可读性，并允许您在使用前检查/清除参数。
回答by JD Long
I have not been using Excel 2007, but in previous versions of Excel I really hate the SQL Query integration. In particular I found it hard to do exactly what you ask about without resorting to hacking together a bunch of VBA code. I did some research and finally decided to use Rob Van Gelder's Excel query add in:
我没有使用过 Excel 2007，但在以前版本的 Excel 中，我真的很讨厌 SQL 查询集成。特别是我发现很难完全按照你的要求去做，而不用把一堆 VBA 代码拼凑在一起。我做了一些研究，最后决定使用 Rob Van Gelder 的 Excel 查询插件：
http://vangelder.orconhosting.net.nz/excel/queryeditor.html
http://vangelder.orconhosting.net.nz/excel/queryeditor.html
The 'parameters' section in his GUI allows you to set up a parameter and have the utility either prompt the user for the value or pull the value from a cell reference. I believe the latter is exactly what you want to do. It also has the ability to auto-refresh when a cell value changes. I use the auto refresh function a lot when validating data in my DB.
他的 GUI 中的“参数”部分允许您设置参数并让实用程序提示用户输入值或从单元格引用中提取值。我相信后者正是你想要做的。它还具有在单元格值更改时自动刷新的能力。在验证数据库中的数据时，我经常使用自动刷新功能。
I have had a couple of situations where my queries became corrupt and I could no longer open them or see the text. As a result, I recommend keeping a tab in the excel spreadsheet for your queries and copying the text of the queries into cells on that special page. That way if the query tool eats your queries you won't have to rebuild them from scratch.
我遇到过几种情况，我的查询已损坏，我无法再打开它们或查看文本。因此，我建议在 Excel 电子表格中为您的查询保留一个选项卡，并将查询的文本复制到该特殊页面上的单元格中。这样，如果查询工具吃掉了您的查询，您就不必从头开始重建它们。
A third party tool, albeit free, might not be what you are looking for, but it works well for me. Good luck!
第三方工具虽然是免费的，但可能不是您想要的，但它对我来说效果很好。祝你好运！
回答by Russ Cam
Dim SQLString as String
SQLString = "SELECT * FROM dbo.MyDataTable WHERE Col1 = '" & Cells("B2") & "'"
It would be worth looking at SQL Injection attacksto see the downsides of this approach, but it may not necessarily be a consideration for what you need to do
值得一看SQL 注入攻击以了解这种方法的缺点，但它可能不一定是您需要做的事情的考虑因素
