Creating parameterized SQL queries in Excel 2010 using VBA

Question

I came across the following link:

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

In it, they list relatively simple code for querying an 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. 

It works great. However, I want to be able to output the value back as a variable instead of dumping it in Excel.

Can someone help me with this? I tried looking for Excel VBA SQL Tutorials, but it seems that half of the code I find does not work (perhaps because I do not understand it well enough).

Answer 1

You can use ADO, for example:

 ''Reference: Microsft ActiveX Data Objects xx 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 

Additional Information: http://w3schools.com