Excel Filtering Function

Keywords:   Excel Filtering Function

---

Question:

I would like to do the following :

1. Opening excel file
2. Activate a filter on a computername value in column A
3. Make a list with all applications assigned to the computername
4. Close the filter

1,2,4 is ok

When I scan the filtered table, the program seems not to take the filter in account and gives me a complete list and not a filtered list of applications.

Answer:

Probably the .filter property hides the data form dislay but not access. You ca use ADO to retrieve selected Excel data, which is as good as a filter, and you don't have to actually open the fle in Excel. You can easily modify the atached to meet your criteria.

---

; use ADO to Filter out Excel Data
; Stan Littlefield - September 4, 2002

cXLS  = StrCat( DirGet(), "test.xls" )
If ! FileExist( cXLS ) Then Exit
cConn = "DRIVER={Microsoft Excel Driver (*.xls)};DriverId=790;ReadOnly=True;DBQ=%cXLS%"
DB    = ObjectOpen( "ADODB.Connection" )
RS    = ObjectOpen( "ADODB.Recordset" )
DB.Open( cConn )
RS.Open( "SELECT * FROM [PC$] WHERE [Applications] LIKE 'Acrobat%%'", DB, 3, 3, 1 )

fld1  = RS.Fields("PC Names")
fld2  = RS.Fields("Applications")
While ! RS.Eof()
   Message(fld2.Value,fld1.Value)
   RS.MoveNext()
EndWhile

RS.Close()
ObjectClose( RS )

DB.Close()
ObjectClose( DB )

Exit

---

Article ID:   W15253