SQL Queries in Excel using Excel cells as argument

  • Forgive me for my lack of knowledge, I am fairly new to TSQL programming. I want to run a db query in Excel, which I can do no problem. What I would like to do is use data in cells as arguements in the TSQL. IE - select * from table_1 where column_1 = cellA1. Help??

  • Carl,

    This seems more an excel/ADO question than T-SQL. There are a few ways of referencing it. If you can run the query from excel then just concatenate the selection (cells(r,c).value - make sure the sheet you want is active). Make sure you use correct TSQL syntax when concatenating the string. The rest is just like normal VB unless you use a query table object.

    Query tables are an excellent generic way to go.

    But generally if it is fixed then just create the T-sql string referencing the cells.

    Create an adodb.recordset object.

    Create an adodb.connection object.

    Connect.

    Open the recordset with the sql string and the connection object.

    (rs.Open strsql, <connected connectionobject>, adOpenKeyset, adLockOptimistic 'or whatever

    And you've got the recordset.

    You can also muck around with command objects which are good for stored procedures (if quite unweildy)

    here's an example using command objects and a query table.

    Dim objMyQueryTable As QueryTable

    Dim cmd As New ADODB.Command

    Dim con As New ADODB.Connection

    Dim rs As ADODB.Recordset

    Range("A6:M6").Select

    Range(Selection, Selection.End(xlDown)).Select

    Selection.ClearContents

    Range("A1").Select

    con.CursorLocation = adUseClient

    con.Open <connection string>

    With cmd

    Set .ActiveConnection = con

    .CommandType = adCmdStoredProc

    .CommandText = "spViewOrders"

    .Parameters.Append .CreateParameter("@StartDate", adVarChar, adParamInput, 20, Format(Application.Range("vwStartDate"), "dd/mmm/yyyy"))

    .Parameters.Append .CreateParameter("@EndDate", adVarChar, adParamInput, 20, Format(Application.Range("vwEndDate"), "dd/mmm/yyyy"))

    .CommandTimeout = Application.Range("timeout")

    Set objMyQueryTable = ActiveSheet.QueryTables.Add(.Execute, Destination:=ActiveSheet.Range("A6"))

    objMyQueryTable.Refresh False

    objMyQueryTable.Delete

    End With

  • One approach to this in Excel is with parameters. Create a DSN and link to the database, then in the query builder use SQL and enter something like this:

    SELECT Ship.TrackingNbrStr, Ship.UnitCd, Ship.CustCd, Ship.MRADt, Ship.RefNbr

    FROM dw.dbo.Ship Ship

    WHERE (Ship.TrackingNbrStr=?)

    Note the "?". Then under the import External Data use the Parameters option to define where it comes from. You can either prompt for the parameter (on each refresh), specify a value there, or get it from a data cell on the spreadsheet.

    I've used this occasionally, and it generally works, but it can be awkward and a bit flakey. I don't recall the flakiness in detail, I just remember moving away from this and providing a web interface for our people who wanted to write reusable queries. But I just tried one with the query above and it worked fine.

  • You could use the sql.request function from the excel odbc add in - tools - add ins - "ODBC add-in". Then you can return stuff from your database from the sheet without going through code. Or as the man says do a query table and then a lookup - often quicker. formula is "=SQL.REQUEST("dsn=databasename",,3,"SELECT top 1 fieldname from tb_Records")" You can add in your references to other cells in the sql string bit at the end. You can also return this as an array - but then you might as well use a query table.

    Andy

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply