使用VBA在Excel 2010中创建参数化SQL查询 [英] Creating Parameterized SQL Queries in Excel 2010 with VBA

问题描述

我遇到了以下链接：

http://www.informit.com/guides/content.aspx?g=sqlserver&seqNum=135

在其中列出了相对简单的代码来从Excel VBA查询SQL数据库。

In it, they list relatively simple code to query the SQL Database from Excel VBA.

' 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.

这工作正常。但是，我希望能够将值作为变量提取，而不是将其转储到Excel。

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 VBA SQL教程，但似乎我找到的一半代码不起作用（也许是因为我不太了解它）。

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).

推荐答案

您可以使用ADO，例如：

You can use ADO, for example:

''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

更多信息： http://w3schools.com

这篇关于使用VBA在Excel 2010中创建参数化SQL查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！