Passing parameters from Excel spreadsheet to Microsoft Query

I use an ADO Connection to the DB and just piece together a string in Excel to query the DB, like so:

Dim sql As String
sql = "EXECUTE procLineLogEntriesDistinctLinesByDateSelect " & _
            "'" & dailys.Range("A1").Value & "', '" & dailys.Range("B1").Value & "';"

There is also a way to pass cell values to Microsoft Query.  It's been awhile and I forgot how, but I do remember that I found it very limiting on what you could do query-wise.  So depending on your query complexity you have two different options there.

HTH,
Charly

I'm not sure what you want to use as the criteria field, but for a query that simple, you can use query table parameters. Change the SQL in MSQuery to:
"Select DEPT, EMPLOYEE, AMOUNT from dbo.SALES_2007 WHERE Employee=?"
then supply a value when prompted (it doesn't matter what) and then choose File-Return data to MS Excel. Once there, right-click the data table, choose Parameters and you can then specify a range to use.
HTH
Rory

Sorry for responding so slowly.  I think Rory's example would work best for your situation, but here's a simple ADO setup:

Public Sub simpleQuery()

   'Connection Variables
    Dim dbConnection As ADODB.connection
    Dim connStr As String
   
    'Recordset variables
    Dim rsData As ADODB.Recordset
    Dim sql As String
   
    sql = "SELECT DEPT, EMPLOYEE, AMOUNT FROM dbo.SALES_2007 WHERE Employee = " & _
            sheet1.Range("A1").Value & ";"

    connStr = "Provider=SQLNCLI;" & _
              "Data Source=SUNNY\SQLEXPRESS;" & _
              "Database=line;" & _
              "Trusted_Connection=yes;"

    Set dbConnection = New ADODB.connection
   
    dbConnection.ConnectionString = connStr
   
    dbConnection.Open
   
    Set rsData = New ADODB.Recordset
   
    rsData.Open sql, dbConnection, adOpenStatic, adLockReadOnly, adCmdStoredProc
   
    If Not rsData.EOF Then
        Range("A1").CopyFromRecordset rsData
    End If
   
    'Cleanup
    Set rsData = Nothing
    Set dbConnection = Nothing
   
End Sub

You will need to determine the connection string for your database.  I have found www.connectionstrings.com to be a great site to do that.  For some general help with ADO, I found www.w3schools.com/ado useful as well.  If you end up using this setup, I would suggest that you move all of your sql into stored procedures for maintainability as well.  Hope that helps you out.

--Charly