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

Question

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.

Answer 1

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

Answer 2

You can manipulate the connections in your workbook using vba. Eg you can build a sql query string and apply it to the CommandText property and then Refresh

Here's a taster

Sub ExamineConnections()
    Dim con As WorkbookConnection

    For Each con In ActiveWorkbook.Connections
        Select Case con.Type
        Case xlConnectionTypeODBC
            Debug.Print con.Name
            Debug.Print "ODBC Connection"
            Debug.Print con.ODBCConnection.Connection
            Debug.Print con.ODBCConnection.CommandText
            Debug.Print
        Case xlConnectionTypeOLEDB
            Debug.Print con.Name
            Debug.Print "OLEDB Connection"
            Debug.Print con.OLEDBConnection.Connection
            Debug.Print con.OLEDBConnection.CommandText
            Debug.Print
        End Select
    Next
End Sub