Excel AutoFilter Method

 Keywords: Autofilter function 

---

Question:

I use the excel autofilter function but I have a syntax problem.

Based on a macro recording in excel and my experience with the FIND function, I've made the following code but ...:-(

XLAND=1
RANGE=DB.Columns("A:A")
RANGE.Select
Z=DB.Selection
Z.AutoFilter
FILTER=Z.AutoFilter("=*cognos*" :: Operator=XLAND,Field=1)

All is ok except the last line which gives me an OLE exception .

Here is the macro code for the following operations

1. selection of column A
2. activation of autofiltering
3. make a sort of query inside autofilter

Sub Macro1()
' Macro1 Macro
' Macro recorded 5/08/02 by JVM
Columns("A:A").Select
Selection.AutoFilter
Selection.AutoFilter Field:=1, Criteria1:="=*cognos*", Operator:=xlAnd
End Sub

My excel file has only one column with a list of software products and my sample is to have all the products where "cognos" is in.

Answer:

Give the following code a try. It seems to work for me....

objXL = ObjectOpen("Excel.Application")
objXL.Visible = @TRUE
objWorkbooks=objXL.WorkBooks
objWorkbooks.Open("C:\temp\test.xls")
objColumns=objXL.Columns("A:A")
objColumns.Select

objSelection=objXL.Selection
objSelection.AutoFilter
xlAnd = 1
objSelection.AutoFilter(:: Field=1, Criteria1="=*cognos*", Operator=xlAnd)

ObjectClose(objSelection)
ObjectClose(objColumns)
ObjectClose(objWorkbooks)
ObjectClose(objXL)