Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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.

share|improve this question
    
Actually, it would be perfect if the excel file (with the data) and the sql query could be controlled from the function too. Like that all I need to do happens in one place. Thanks! –  Terco Jun 8 '11 at 23:45
    
Make sure and check out the limitations of UDFs here. It may not matter for what you're doing but they're not being specific enough about which methods can't be called. –  Lance Roberts Jun 9 '11 at 6:52
    
I left a short answer to get you going. I'll try and find time tomorrow to flesh it out tomorrow when I can run some tests. –  Lance Roberts Jun 9 '11 at 7:12
    
putting together a query string is one thing, but how elaborate will it need to be? Do you want to parameterize the table name, the fields and criteria? Would seem like 3 parameters might not cover much, and it will be difficult to create a function without more information –  datatoo Jun 9 '11 at 11:57

2 Answers 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
share|improve this 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
share|improve this answer
    
Hello Lance, Thanks for the link. Unfortunately, I saw it before and tried but wasn't very successful. The problem was this line "Set oQuery = Sheet3.QueryTables(1)" and I couldn't move forward because I didn't really know much about QueryTables and couldn't tell if the one I created through the external source was really inside Sheet3. –  Terco Jun 9 '11 at 8:10
    
@Terco, OK, I tested and got this to work just fine. I'm curious to get it working the "correct" way with queries and parameters, so I may add that method later. –  Lance Roberts Jun 9 '11 at 20:23

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.