Editing parameters of a connection file, but "edit query" is grayed out

[CoolerWater]

I have an Excel spreadsheet that displays information from a data connection file. My boss wants me to find a way to put in parameters for the file so it doesn't search the entire database every time it's pulled up. He wants the users of the file to be prompted to enter the parameters that they choose.

Under Connection Properties on the definition tab, the buttons for "Edit Query" and "Parameters" are both grayed out, otherwise this is where I would go to set parameters. I do have access to modify the command text, which is set to SQL. Currently it reads:

/*PayablesTransactions Default**/
select
[Voucher Number],
[Vendor ID],
[Document Type],
[Document Date],
[Document Number],
[Current Trx Amount]
from PayablesTransactions
order by [Voucher Number]

Is there a way to enter parameters that the user is prompted to enter in this command text? If so, how do I do that?

Also, is there another way to enter parameters that will bypass the fact that the edit query and parameters buttons are both grayed out? I am not an advanced Excel user, and any help that could be offered would be greatly appreciated!

 

[CoolerWater]

Still no luck with this issue. Does anyone have any ideas?

 

[John_w]

I don't know why the buttons are greyed out - this happens if the active cell isn't data which has been returned by the query. Your information is a bit sparse: what Excel version, what type of database, why type of connection?

I've no idea if this will work on your sheet, but try something like this to prompt for parameters and generate a custom SQL SELECT statement for the database query embedded in the active sheet:

Sub SQL_test()
    
    Dim documentDate As Date, vendorID As String
    Dim sql As String
    Dim qt As QueryTable
    
    If ActiveSheet.QueryTables.Count > 0 Then
    
        documentDate = InputBox("Enter document date from")
        vendorID = InputBox("Enter vendor ID")
        
        sql = "select [Voucher Number], [Vendor ID], [Document Type], [Document Date], [Document Number], [Current Trx Amount]" & _
            "from PayablesTransactions " & _
            "where ([Document Date] >= '@DOCUMENTDATE@') " & _
            "and ([Vendor ID] = '@VENDORID@') " & _
            "order by [Voucher Number]"

        sql = Replace(sql, "@DOCUMENTDATE@", Format(documentDate, "YYYY-MM-DD"))
        sql = Replace(sql, "@VENDORID@", vendorID)
        
        Set qt = Sheet1.QueryTables(1)
    
        With qt
            .CommandType = xlCmdSql
            .CommandText = sql
            .Refresh BackgroundQuery:=False
        End With
    
    Else
        MsgBox "No database query exists on sheet " & ActiveSheet.Name
    End If
    
End Sub

Something similar works fine for a SQL Server 2005 AdventureWorks Employee table OLE DB query in Excel. NB - the @DOCUMENTDATE@ and @VENDORID@ text isn't SQL syntax, but something I've invented to make substitution of parameter values in the SQL string easier.