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

Answer 1

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