Filter External Data

[Guest]

I import external data from an Access query into an Excel spreadsheet. Upon
refresh data I want to filter the imported records based on data from a
single cell in a separate worksheet in a separate workbook.
The data imported is employee information including days off requested and
has a date field. I am trying to get the query, when refreshed, to look in a
single cell located in a separate workbook and filter the records based on
the value of that cell.
I would like to attach the procedure/macro/code to a button and run on
click. I am a beginner at programming and wonder if this is possible? What is
the best way to go about it and open to suggestions to get me going in the
right direction. Thank You

 

[Guest]

What you are asking for is fairly easy to set-up. You need to use the Sort
command to do what you are asking. For example, if your data was imported to
Sheet1, Cells A1 thru G50 and you wanted to sort by the A column you would
use the following command:

Range("A1:G50").Sort _
Key1:=Worksheets("Sheet1").Range("A1")

That will sort your data in ascending order based on your A column. If you
need more help with setting it up search in the Help file for Sort.

 

[lexcel]

Hi

I suppose your kernal statement would be something like:

Range("MyData").AutoFilter _
field:=CriteriumColumn, _
Criteria1:= SeprateWorkBook.Range("FilterCriterium")

To manipulate the filtered data you can then use:
Range("MyData").SpecialCells(xlCellTypeVisible).Select

and then to access filtered records:
For Each Record in Selection.Rows
....
Next Record

Declarations needed:
Dim Record as Range, CriteriumColumn as Integer, SeprateWorkBook as
WorkBook

In my example I assumed that you made 2 named ranges: One in the
workbook to be filtered (MyData), the other (FilterCriterium) denotes
the cell containing the Criterium Cell in the other workbook.
Also you need both workbooks to be open.

If you have more than 1 workbook open, you can access them through the
WorkBooks Collection. You will need either the name or the index number
of the workbook in which your criteriumcell resides to access it.
Suppose it is called Criteria then you can assign it like:
Set SeparateWorkBook = WorkBooks("Criteria.xls")

You might find the Excel help topics "About linking to another workbook
or program" and "Create a link to another cell, workbook, or program ->
Link to a name in another workbook" interesting.

Good luck, hope this gets you going,

Lex