使用 VBA 在 Excel 2010 中创建参数化 SQL 查询
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/4806409/
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
Creating Parameterized SQL Queries in Excel 2010 with VBA
提问by DavidStein
I came across the following link:
我遇到了以下链接：
http://www.informit.com/guides/content.aspx?g=sqlserver&seqNum=135
http://www.informit.com/guides/content.aspx?g=sqlserver&seqNum=135
In it, they list relatively simple code to query the SQL Database from Excel VBA.
在其中，他们列出了从 Excel VBA 查询 SQL 数据库的相对简单的代码。
' Declare the QueryTable object
Dim qt As QueryTable
' Set up the SQL Statement
sqlstring = "select au_fname, au_lname from authors"
' Set up the connection string, reference an ODBC connection
' There are several ways to do this
' Leave the name and password blank for NT authentication
connstring = _
"ODBC;DSN=pubs;UID=;PWD=;Database=pubs"
' Now implement the connection, run the query, and add
' the results to the spreadsheet starting at row A1
With ActiveSheet.QueryTables.Add(Connection:=connstring, Destination:=Range("A1"), Sql:=sqlstring)
.Refresh
End With
'Save and close the macro, and run it from the same menu you accessed in step 2.
This works fine. However, I want to be able to pull a value(s) back as a variable instead of dumping it to Excel.
这工作正常。但是，我希望能够将值作为变量拉回，而不是将其转储到 Excel。
Can someone assist me with that? I've tried looking for Excel VBA SQL Tutorials, but it seems that half the code I find doesn't work (perhaps because I don't understand it well enough).
有人可以帮助我吗？我试过寻找 Excel VBA SQL 教程，但似乎我找到的一半代码不起作用（可能是因为我不太了解它）。
采纳答案by Fionnuala
You can use ADO, for example:
您可以使用 ADO，例如：
''Reference: Microsft ActiveX Data Objects x.x Library
Dim cmd As New ADODB.Command
Dim cn As New ADODB.Connection
Dim param1 As New ADODB.Parameter
Dim rs As ADODB.Recordset
With cn
.Provider = "SQLOLEDB"
''See also http://connectionsstrings.com
.ConnectionString = "Data Source=Server;Initial Catalog=test;Trusted_Connection=Yes"
.Open
End With
Set param1 = cmd.CreateParameter("@SiteID", adBigInt, adParamInput)
param1.Value = 1
cmd.Parameters.Append param1
With cmd
.ActiveConnection = cn
''Stored procedure
.CommandText = "spSiteInformation_Retrieve"
.CommandType = adCmdStoredProc
Set rs = .Execute
End With
For Each f In rs.Fields
Debug.Print f.Name; " "; f
Next
rs.Close
Set rs = Nothing
cn.Close
Set cn = Nothing
Further information: http://w3schools.com
更多信息：http: //w3schools.com
