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

Shopping

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

Hotel

Hi. Thanks for this. I'll have a look! – Shopping

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")

Deficient

Answer 3

I'm just throwing an answer here for future people who want to use a different but similar solution. It is a lot more simple though. You will need to install xlwings and have pywin32. With xlwings, you can access the api functions of the pywin32 giving you a lot of flexibility on top of its own functions.

import xlwings
#puts the excel window into focus or opens it up. It evens work on csv files.
wb = xlwings.Book('C:\\Users\\yourusername\\Desktop\\Excel.xlsx')

#Choose the sheet you want to focus
datasht = wb.sheets['Sheet1']

#Pay attention to where you the .api. part. It matters if you are trying to achieve something specific. AND MAKE SURE to that you follow case-sensensitive typing for 'Range' and 'Autofilter'.
datasht.api.Range('A1:J10').AutoFilter(3,'SomeFilterValue')

Unfortunately, I'm not sure how to bring about the rest of the arguments. You pretty much just have to figure out how to translate the arguments into python. I did get it to work, but I'm unsure if you would run into any issues. here is one that would work

datasht.api.Range('A1:J10').AutoFilter(3,'filtervalue1',2,'filtervalue1',1)

Read the 2nd link specifically if you need to call on the Operator Parameter: https://msdn.microsoft.com/en-us/vba/excel-vba/articles/range-autofilter-method-excel https://msdn.microsoft.com/en-us/vba/excel-vba/articles/xlautofilteroperator-enumeration-excel

Arquit

Answer 4

If you need to select multiple filter values in the same column:

ws.Columns('ColumnLetter:ColumnLetter').AutoFilter(column_number, value_list, 7)

From https://learn.microsoft.com/en-us/office/vba/api/excel.xlautofilteroperator:
xlFilterValues | 7 | Filter values

Flitter

Answer 5

This works:

Excel = win.Dispatch("Excel.Application")
Excel.visible = True
wb = Excel.Workbooks.open('path to xlsx')
ws = wb.Worksheets(1)
#use Range("A:A") for autofilter
ws.Columns("A:I").AutoFilter(1,"criteria string")

This will apply AutoFilter on Column A with Criteria1 is "criteria string"

Keven