Excel Parameter: How to show in spreadsheet

[cclabaugh]

I have a worksheet that runs a db query based upon two parameters. Once the data has been retrieved from the database, how do I show on my spreadsheet the parameters that were sent to get the data?

 

[SkipVought]

Hi,

Can't do it without some VBA code. You could store the parameter values in cells in row 1 and return your query to row 3. You would need a button to run the code.

Here's how to generate most of the code.
1. turn on the macro recorder
2. select the top left cell in the results set
3. Data/Get External Data - edit query and hit [NEXT] thru Return Data to Excel and [FINISH}
4. turn off the macro recorder

Now you have code (alt+F11) that looks similar tothis...

    Range("A3").Select
    With Selection.QueryTable
        .Connection = Array(Array( _
        "ODBC;DSN=MS Access Database;DBQ=C:\My Documents\vbaProjects\snkPlumbing\snkDatabase.mdb;DefaultDir=C:\My Documents\vbaProjects\snkPl" _
        ), Array("umbing;DriverId=25;FIL=MS Access;MaxBufferSize=2048;PageTimeout=5;"))
        .CommandText = Array( _
        "SELECT tblChangeOrderDetail.JobNbr, tblChangeOrderDetail.Item, tblChangeOrderDetail.ChangeOrderNbr, tblChangeOrderDetail.WorkDescription, tblChangeOrderDetail.Amount" & Chr(13) & "" & Chr(10) & "FROM `C:\My Documents\vbaProjects" _
        , _
        "\snkPlumbing\snkDatabase`.tblChangeOrderDetail tblChangeOrderDetail" & Chr(13) & "" & Chr(10) & "WHERE (tblChangeOrderDetail.JobNbr=231.0)" _
        )
        .Refresh BackgroundQuery:=False
    End With

Now, since one of MY parameters is JobNbr and I put my Job Number in B1, then the code for the SQL code would be modified...

        "SELECT tblChangeOrderDetail.JobNbr, tblChangeOrderDetail.Item, tblChangeOrderDetail.ChangeOrderNbr, tblChangeOrderDetail.WorkDescription, tblChangeOrderDetail.Amount" & Chr(13) & "" & Chr(10) & "FROM `C:\My Documents\vbaProjects" _
        , _
        "\snkPlumbing\snkDatabase`.tblChangeOrderDetail tblChangeOrderDetail" & Chr(13) & "" & Chr(10) & "WHERE (tblChangeOrderDetail.JobNbr=" & [B2] & ")" _

Then I'd but a button on the sheet and run this code with the button_click event.



Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

Skip,

http://www.TheOfficeExperts.com

 

[Durendal1970]

If your parameters are asked in Excel when you refresh your query...

Data->Get External Data->Parameters will let you designate a given cell to place your parameter value in.

Reference the cell for display where needed.



QOTD:Computers never make mistakes they are only given bad instructions.