VBA - 带有单元格引用的 SQL 查询
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/19816674/
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
VBA - SQL Query with Cell Reference
提问by Murgy
I have been working on a new tool and I am needing it to run the query based on what the cells equal.
我一直在研究一个新工具，我需要它来运行基于单元格相等的查询。
For instance,
例如，
I have 3 cells in excel that have Data Validation. C4:Department C5:Group C6:Category
我在 excel 中有 3 个具有数据验证的单元格。C4：部门 C5：组 C6：类别
Basically if (All) is selected, run the first query, otherwise run the second. I need Group to run a query based on what the cell is like this:
基本上，如果选择了 (All)，则运行第一个查询，否则运行第二个。我需要 Group 根据单元格的内容运行查询：
If Sheets("Rollup").Range("C5").Value = "(All)" Then
Sheets("Codes").Range("B3:XFD1048576").Clear
cn.Open SQLServerConnString
strQuery = "Select DEPT_CATG_GRP_DESC from dbo.Rollup GROUP BY DEPT_CATG_GRP_DESC ORDER BY DEPT_CATG_GRP_DESC"
Set rs = cn.Execute(strQuery)
Sheets("Codes").Range("B3").CopyFromRecordset rs
rs.Close
cn.Close
Else Sheets("Codes").Range("B3:XFD1048576").Clear
cn.Open SQLServerConnString
strQuery = "Select DEPT_CATG_GRP_DESC WHERE DEPT_CATG_GRP_DESC = C5.Value from dbo.Rollup GROUP BY DEPT_CATG_GRP_DESC ORDER BY DEPT_CATG_GRP_DESC"
Set rs = cn.Execute(strQuery)
Sheets("Codes").Range("B3").CopyFromRecordset rs
rs.Close
cn.Close
End If
回答by Dick Kusleika
Your SQL string is just a string. You have to concatenate in values to build the string you want.
您的 SQL 字符串只是一个字符串。您必须连接值以构建所需的字符串。
strQuery = "Select DEPT_CATG_GRP_DESC WHERE DEPT_CATG_GRP_DESC = " & Sheets("Rollup").Range("C5").Value & " from dbo.Rollup GROUP BY DEPT_CATG_GRP_DESC ORDER BY DEPT_CATG_GRP_DESC
回答by Brian M
I have SQL code embedded in a Power Query. I have a named cell in an Excel Sheet. I have successfully referenced that cell and used it as a filter in my power queries:
我在 Power Query 中嵌入了 SQL 代码。我在 Excel 工作表中有一个命名单元格。我已成功引用该单元格并将其用作电源查询中的过滤器：
YAWafer = Excel.CurrentWorkbook(){[Name="YAWafer"]}[Content]{0}[Column1]
I cannot figure out how to "feed" this parameter into the embedded SQL code - ie code that is embedded into the same Power Query that uses this parameter successfully as a filter.
我无法弄清楚如何将此参数“提供”到嵌入式 SQL 代码中 - 即嵌入到成功使用此参数作为过滤器的同一个 Power Query 中的代码。
I have tried many different ways based on ideas I have seen in these posts, but unsuccessful. My main motivation is to get that parameter into the embedded SQL code and use it as an upfront filter.
根据我在这些帖子中看到的想法，我尝试了许多不同的方法，但都没有成功。我的主要动机是将该参数放入嵌入式 SQL 代码并将其用作前期过滤器。
