How to add parameters to an external data query in Excel which can't be displayed graphically?

Question

I often use MS Excel's Get External Data to create simple reports - running queries against databases and displaying nicely in Excel. Excel's great features like filtering and pivot tables and familiar interface for users make it quite good for this. However, one limitation with Microsoft Query is you can't add parameters to queries that can't be displayed graphically, considerably limiting the sql you can write.

Is there any solution to the error "parameters are not allowed in queries that can't be displayed graphically"?

Answer 1

If you have Excel 2007 you can write VBA to alter the connections (i.e. the external data queries) in a workbook and update the CommandText property. If you simply add ? where you want a parameter, then next time you refresh the data it'll prompt for the values for the connections! magic. When you look at the properties of the Connection the Parameters button will now be active and useable as normal.

E.g. I'd write a macro, step through it in the debugger, and make it set the CommandText appropriately. Once you've done this you can remove the macro - it's just a means to update the query.

Sub UpdateQuery
    Dim cn As WorkbookConnection
    Dim odbcCn As ODBCConnection, oledbCn As OLEDBConnection
    For Each cn In ThisWorkbook.Connections
        If cn.Type = xlConnectionTypeODBC Then
            Set odbcCn = cn.ODBCConnection

            ' If you do have multiple connections you would want to modify  
            ' the line below each time you run through the loop.
            odbcCn.CommandText = "select blah from someTable where blah like ?"

        ElseIf cn.Type = xlConnectionTypeOLEDB Then
            Set oledbCn = cn.OLEDBConnection
            odbcCn.CommandText = "select blah from someTable where blah like ?"
        End If
    Next
End Sub