CODEWITHSUNDEEP
sql-servervbaexcel
Richard
Richard

Reputation: 105

SQL query that uses a range in excel as the criteria

I use external data from an SQL query in excel to pull data onto a spreadsheet then the spreadsheet can be sent to one of my colleges in say the sales department and they can view the queried information.
I want to be able to have a cell that they can enter data into and press a refresh button.

So I need to do some thing like this:

SELECT     Customer.CustomerCode, Customer.Name, 
           OrderHeader.OrderType, OrderHeader.OrderNumber
FROM       Customer INNER JOIN
           OrderHeader ON Customer.CustomerID = OrderHeader.CustomerID
WHERE     (OrderHeader.OrderType = 2) AND (OrderHeader.OrderNumber = Range("A1").Value)

Not sure how or if this is possible and the reason I need to do this is because i'm going through lines from all the Quotes and if there is over 65536 then i'll have a problem.

This is what I have at the moment the SQL is different but that doesn't matter

enter image description here

Upvotes: 0

Views: 3744

Answers (2)

luke_t
luke_t

Reputation: 2985

Further to @OWSam's example using ODBC, we can also use ADO to pull back query results from SQL Server. Using ADO, you cannot use windows verification and you will need the user to input their password somehow, to be able to run the query.

Personally I create a userform which has UserName and Password. Username pre-populated using Environ, and then they can type their password into the relevant TextBox.

Sub sqlQuery()
    Dim rsConn As ADODB.Connection, rsData As ADODB.Recordset
    Dim strSQL As String, winUserName As String, pwd As String

    winUserName = UCase(Environ("username"))
    pwd = "mypassword" 'password needed here.
    strSQL = "SELECT * FROM mydatabase" 'query

    Set rsConn = New ADODB.Connection
    With rsConn
        .ConnectionString = "Provider = sqloledb;" & _
                            "Data Source = [server name here];" & _
                            "Initial Catalog = [initial database];" & _
                            "Integrated Security=SSPI;" & _
                            "User ID = " & winUserName & ";" & _
                            "Password = " & pwd & ";"
        .Open
    End With
    Set rsData = rsConn.Execute(strSQL)

    Range("A1").CopyFromRecordset rsData
End Sub

Edit: You must go into references and turn on the Microsoft ActiveX Data Objects Recordset 6.0 library (or equivalent within your VBE).

Upvotes: 1

SQLSam
SQLSam

Reputation: 537

The easiest way is to pull the values for the parameters into VBA, and then create the SQL statement as a string with the parameter values, and then populate the commandtext of the query.

Below is a basic example of the parameter in cell A1. 

Sub RefreshQuery()

Dim OrderNo As String
Dim Sql As String

' Gets value from A1
OrderNo = Sheets("Sheet1").Range("A1").Value

'Creates SQL Statement
Sql = "SELECT     Customer.CustomerCode, Customer.Name, " & _
    "OrderHeader.OrderType , OrderHeader.OrderNumber " & _
    "FROM       Customer " & _
    "INNER Join  OrderHeader ON Customer.CustomerID = OrderHeader.CustomerID " & _
    "WHERE (OrderHeader.OrderType = 2) And (OrderHeader.OrderNumber = " & OrderNo & ") "


With ActiveWorkbook.Connections(1).ODBCConnection
    .CommandText = Sql
    .Refresh
End With

End Sub

This assumes you have the query in Excel to begin with, and that it's the only query. Otherwise you'll have to define the name of the query as below:

With ActiveWorkbook.Connections("SalesQuery").ODBCConnection

I hope that helps :)

Upvotes: 1

Related Questions