Setting criteria on an autofilter in pyWin32

Question

I can set up an autofilter using pyWin32, but I wondered if it's possible to set a default filter and what the syntax would be.

For example, I'd like to set a filter on a year column and set the default for the current year.

xl = Dispatch("Excel.Application") 
xl.Workbooks.Open(file_path) 
xl.ActiveWorkbook.Worksheets(sheetname).Range("A2:A6").AutoFilter(1)
xl.ActiveWorkbook.Close(SaveChanges=1)

I've looked on the web for documentation on pywin32, and also Microsofts site, but can't work out how to translate the MS syntax to pywin32

Range("A2:A6").AutoFilter Field:=1, Criteria1:=rng.Value

Answer 1

I bumped into the same problem and after a bit of experimentation, I found that it was possible to set a range on the Columns attribute. Since I wanted to autofilter on columns A thru I, I set the criteria as follows:

xl.ActiveWorkbook.ActiveSheet.Columns("A:I").AutoFilter(1)

This worked for me. I'm assuming that you want to filter on Columns B thru F since AutoFilter is enabled only for columns. Perhaps the following criteria will work for you:

xl.ActiveWorkbook.ActiveSheet.Columns("B:F").AutoFilter(1)

Alok

Answer 2

The rather cryptic documentation is available at: http://msdn.microsoft.com/en-us/library/office/bb242013(v=office.12).aspx.

Each of the Excel VBA parameters translates to a function argument in pywin32. For example, if you want to filter all years that aren't equal to "2012" you would do this by specifying the Criteria1 parameter as follows:

MyYearRange.AutoFilter(Field=1, Criteria1="2012")