2

So in MS Excel I've imported a table from a database in a SQL Server. I want to create a parameterized query where you have two cells. Say these two cells are G1 and G2. G1 takes a parameter/category and G2 takes a value from the parameter/category and queries the table you imported (essentially a WHERE clause that is dynamic from cell input). Can someone show me how to do this?

Mitchell Walker
  • 211
  • 3
  • 8
  • 18

1 Answers1

1

EDIT: Based on a chat session, we discovered that the first parameter is the column to be searched and the second parameter is the value to filter.

You can do what you want by filtering the table you imported.

Use the code below as your template. Modify it to reference the correct worksheets and ranges.

Sub FilterByParameter()
    Dim wb As Workbook
    Dim dataSheet As Worksheet
    Dim parameterSheet As Worksheet
    Dim rng As Range
    Dim filterColumn As Long
    Dim filterValue As String

    Set wb = ThisWorkbook
    ' sheet that contains your table
    Set dataSheet = wb.Sheets("Sheet1")
    ' sheet that contains your parameters
    Set parameterSheet = wb.Sheets("Sheet2")
    ' range that contains your table, hard-coded here
    ' but can easily be set dynamically
    Set rng = dataSheet.Range("A1:F78")

    ' get the column you are searching
    filterColumn = parameterSheet.Range("G1").Value

    ' get the value you want to filter on
    filterValue = parameterSheet.Range("G2").Value

    ' turn off autofilters if set
    dataSheet.AutoFilterMode = False

    ' autofilter using your column and filter
    rng.AutoFilter field:=filterColumn, Criteria1:=filterValue

    ' now you can do whatever you want to with the rows
    ' that remain after the autofilter was applied
End Sub

See Efficient way to delete entire row if... for an example of how to use the visible rows.

Community
  • 1
  • 1
Jon Crowell
  • 21,695
  • 14
  • 89
  • 110
  • I've never really used VBA, so I don't know the syntax very well. Here is the database I have: http://examples.oreilly.com/9780735666054-files/9780735666054_files.zip You will have to unzip and open TSQL2012. The table I've pulled up in MS Excel is Products. And I realized I needed that in cells H1 and H2 instead. – Mitchell Walker Jul 02 '13 at 20:50
  • Do you want to create a new SQL statement, or do you want to filter the table you imported? – Jon Crowell Jul 02 '13 at 20:51
  • Filter the table I've imported. – Mitchell Walker Jul 02 '13 at 20:53
  • OK, that's what I thought. I don't need to see your database. Have you tried to apply my answer to your workbook? – Jon Crowell Jul 02 '13 at 20:56
  • I tried applying your answer to my workbook and essentially all it is doing closing the cells that the table is in (except for the title columns) and makes them not visible. So I don't know if it is actually filtering or doing something else. – Mitchell Walker Jul 02 '13 at 21:19
  • Your template is supposed to filter the table in Sheet 1 correct? – Mitchell Walker Jul 02 '13 at 21:30
  • It will filter the table on the sheet that the dataSheet variable is set to. `field:=1` refers to the first column in the table. What columns are the parameters supposed to filter? – Jon Crowell Jul 02 '13 at 21:51
  • let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/32782/discussion-between-head-of-catering-and-mitchell-walker) – Jon Crowell Jul 02 '13 at 21:52