Python- How to show the selections in an Excel AutoFilter

Question

This may be a simple task but for the life of me I cannot find a solution. I have an excel document that has a table. The Columns in this table all have autofilters applied. All I want to do is to be able to select all the entries in that autofilter (for the 9th column) and store it in an array. I'm using Win32Com.

import win32com.client as win32

working_dir = 'C:\\invoice\\'
save_dir = 'C:\\test\\'

xl = win32.gencache.EnsureDispatch("Excel.Application")
xl.Visible = True

template_wb = xl.Workbooks.Open(working_dir + 'Settlement report V6-EMPLATE.xlsm')

#Worksheets
orgdata_ws = template_wb.Sheets('Organization Data')
masterdata_ws = template_wb.Sheets('Master Data')

#I want to access the autofilter in column 9 and simply get the contents in the autofilter list and put them in the array
filtercontents = []
thefilter = orgdata_ws.Columns(9).Autofilter
for i in thefilter:
    filtercontents.append(i)     ?????????

Hi Raphael, with this specific code I receive this error: TypeError: 'instancemethod' object is not iterable. But it's highly likely I am not using the correct method for trying to achieve my goal. Also quick update I changed Autofilter to AutoFilter :) — Alexander Witte, May 31 '16 at 21:44

score 4 · Accepted Answer · answered Jun 02 '16 at 22:03

I figured it out for anyone interested. Turns out the column I wanted to access was referenced as well as a Pivot Field in a Pivot Table. So once I was able to read the contents of that pivotfield I could then funnel it into an array (and then use that array to print out pdf invoices). Had some encoding weirdness but solved that with the setdefaultcoding function. Here is the code:

import win32com.client as win32
import sys

reload(sys)
sys.setdefaultencoding("UTF-8")

working_dir = 'C:\\invoice\\'
save_dir = 'C:\\test\\'

xl = win32.gencache.EnsureDispatch("Excel.Application")
xl.Visible = True

template_wb = xl.Workbooks.Open(working_dir + 'Settlement report V6- TEMPLATE.xlsm')

#Worksheets
settlements_ws = template_wb.Sheets('Settlement')
orgdata_ws = template_wb.Sheets('Organization Data')
masterdata_ws = template_wb.Sheets('Master Data')

settlements_ws.Activate()

agencies = []

def maxrow(sheet):
    used = sheet.UsedRange
    nrows = used.Row + used.Rows.Count - 1
    return nrows

mypivot = settlements_ws.PivotTables("PivotTable2").PivotFields("AgencyName")

for j in mypivot.PivotItems():
    j = str(j)
    if j == "#N/A":
        continue
    else:
        j = j.replace("\xc2\xa0","")
        agencies.append(j)
print agencies

#Looping through agencies and saving PDFs
for i in agencies:
    settlements_ws.Cells(8,3).Value = i
    print settlements_ws.Cells(8,3).Value
    settlements_ws.ExportAsFixedFormat(0, save_dir + i + '.pdf')

print "Finished!"

score 0 · Answer 2 · edited May 23 '17 at 10:29

0

I remember my first script....

are you married to win32?

https://mail.python.org/pipermail/python-list/2011-October/613213.html

Python - How to turn-on Excel Auto Filter on cells in non-first row?

edited May 23 '17 at 10:29

Community

1
1

answered May 31 '16 at 22:03

jeremyforan

1,417
18
25

are you missing the trailing brackets on line 17: .AutoFilter() instead of .AutoFilter – jeremyforan May 31 '16 at 22:16
Hey Jeremy thanks for the tip. Yeah I had forgotten the (). I think my concept is flawed though since I now get this error "AutoFilter method of Range class failed". Im trying to apply an autofilter to a pre-existing table (that has the columns filtered already) so maybe that's the hang up. Or likely I am not able to iterate through an autofilter() in the way I am coding. Thanks for the help though; I might be able to steal some stuff from those links – Alexander Witte Jun 01 '16 at 18:36
your best bet is to do a read of an existing sheet and then run the filters as python code, and output the results to a new excel sheet. In my experience, reading is easy, writing is easy but reading and writing of the same complex excel file is challenging – jeremyforan Jun 01 '16 at 19:42

score 0 · Answer 3 · answered Jun 01 '16 at 07:47

0

You're trying to iterate over the method reference Autofilter, and not its return value(s) Autofilter(). By adding the brackets, you call the method. Without the brackets, you just have a reference to that method.

answered Jun 01 '16 at 07:47

Raphaël Gomès

938
1
8
23

Hey Raphael, thanks for the post. Yeah I had forgotten the (). I run into a different error now however (AutoFilter method of Range class failed) so I believe my concept of iterating through an autofilter in this way is not correct. The column I am trying to filter already has a pre-existing filter on it (it is part of a table) so if I can just tap into that filter and extract the criteria that would accomplish my goal! – Alexander Witte Jun 01 '16 at 18:37

Python- How to show the selections in an Excel AutoFilter

3 Answers3