Excel – Pass a cell contents as a parameter to an excel query in Excel 2007

microsoft excelmicrosoft-excel-2007queryvba

I have tried really hard to solve this issue but I think I am going to need a little help. I can program in different languages but I don't have any experience with Excel, VBA or the queries you can make so please feel free to treat me like a little kid and mention all the little details.

What I would like to do is to take the contents in three different cells, and use them as parameters in a SQL query.

Right now I have no problems creating the query as mentioned here Run an SQL Query With a Parameter from Excel 2007 and I can choose the parameters from the cells ONCE (by replacing the strings with '?').

But I would like to call the query multiple times as a normal function, just by putting it in a cell like '=MyQuery($A$1,$A$2,$A$3)'

The "Database" is just another Excel file chosen from External sources so I don't know if things change in the VBA code.

I would be really helpful if you can point me in the right direction, even if they are just pieces of the puzzle. I need a way to get to the query, a way to modify it and a way to execute… but as usual, the devil is in the details.

Best Answer

Here's the Dynamic SQL, ADODB way to do it (make sure and add the ADODB reference). This was built for string values in the cells. Also there's no error-catching code, please add that as needed:

Public Function Test(p1 As Range, p2 As Range, p3 As Range) As Integer

Dim cnt As New ADODB.Connection
Dim rst As New ADODB.Recordset
Dim strSQL As String

cnt.Open "Driver={SQL Server};Server=YY;Database=ZZ;Trusted_Connection=yes"


strSQL = "Select col4 from Table_1 where col1='" & p1.Value & _
          "' and col2='" & p2.Value & _
          "' and col3='" & p3.Value & "'"

rst.Open strSQL, cnt, adOpenStatic, adLockReadOnly, adCmdText

Test = rst("col4")

rst.Close
cnt.Close
Set rst = Nothing
Set cnt = Nothing

End Function

Here's the ADODB, Command-Parameter way, a little trickier. This one is just setup up for single chars:

Public Function Test(p1 As Range, p2 As Range, p3 As Range) As Integer

Dim cnt As New ADODB.Connection
Dim rst As New ADODB.Recordset
Dim ccmd As New ADODB.Command
Dim PA1 As New ADODB.Parameter, PA2 As New ADODB.Parameter  
Dim PA3 As New ADODB.Parameter

cnt.Open "Driver={SQL Server};Server=YY;Database=ZZ;Trusted_Connection=yes"
ccmd.ActiveConnection = cnt
ccmd.CommandText = _
    "SELECT col4 FROM Table_1 WHERE col1=? AND col2=? AND col3=?"
ccmd.CommandType = adCmdText

Set PA1 = ccmd.CreateParameter("first", adChar, adParamInput, 1, p1.Value)
Set PA2 = ccmd.CreateParameter("second", adChar, adParamInput, 1, p2.Value)
Set PA3 = ccmd.CreateParameter("third", adChar, adParamInput, 1, p3.Value)

ccmd.Parameters.Append PA1
ccmd.Parameters.Append PA2
ccmd.Parameters.Append PA3

Set rst = ccmd.Execute

Test = rst("col4")

rst.Close
Set rst = Nothing
cnt.Close
Set cnt = Nothing

End Function
Related Question