Query set a parameter in subquery

How do I add a parameter inside of the Excel query? This situation is not as simple as put ? at the spot. The query is:

SELECT * FROM crm.dbo.Meetings mt
left outer join crm.dbo.Cases cs on mt.meet_CaseId = cs.Case_CaseId
left outer join CRM.dbo.Customer cust on mt.meet_companyid =cust.cust_CustomerID
left outer join crm.dbo.users on mt.meet_launcher = user_userid
WHERE mt.meet_companyid in  (select * from crm.dbo.customer_tree(7587,0))

In the last clause, the work is basically set that four digits numbers as a parameter, which reads cell contents from another sheet in the current workbook.

in  (select * from crm.dbo.customer_tree( ? ,0))

(FYI, the customer_tree is not a simple table/view name in database, while the customer is a table name.)

However, Excel 2007 does not like this format, with error messages showing as:

[Microsoft][ODBC SQL Server Driver]Syntax error or access violation
[Microsoft][ODBC SQL Server Driver]Invalid Descriptor Index

Does anyone know how to figure it out?

Solution

There is a hint to solve this question by assigning the cell content into a variable then reset whole query command box.

STEP 1

Create a ActiveX Controls Command Button by Developer -> Insert -> ActiveX Command Button (the first one)

Draw a button in your sheet, and set properties for Name, Caption, and any others(optional).

STEP 2

Double click on the button, or right click choose view code, or click Visual Basic on the Developer bar to open the VBA editor.

Modify the code for the sheet as below (my button is called Refresh):

Private Sub Refresh_Click() 

    'Declare a variable to content the cell, here is a number in my case
    Dim CustomerId As Integer

    'Declare variables for my ODBC connection, "sql01" is the table name   
    Dim awc As WorkbookConnection
    Set awc = ActiveWorkbook.Connections("sql01")

    'Get the cell content which has the target number
    CustomerId = Sheets("Sheet1").Range("B2").Value


    'Pass the Parameters values 
    With awc.ODBCConnection

        .CommandText = "mt.meet_companyid IN " & _ 
               "( select * from crm.dbo.customer_tree('" & **CustomerId** & "', 0)) " 

        'Refresh the sheet
        awc.Refresh

    End With
End Sub

Once users click the button, the query command will be changed with the current cell content number.