Need A script to open a excel file and extract the data using autofilter

[David Monaghan]

On Sat, 1 Oct 2011 10:35:06 -0700 (PDT), Prakash <prakashr85 at gmail.com>
wrote:

>On Oct 1, 10:25 pm, Prakash <prakash... at gmail.com> wrote:
>> Need  A script to open a excel file and extract the data using
>> autofilter and write it in a new sheet or new file like I have to
>> select all rows in which all the columns contain pass as status
>
>from win32com.client import Dispatch
>xlApp = Dispatch("Excel.Application")
>xlApp.Workbooks.Open(r'C:\Users\Administrator\Desktop\test.xls')
>xlApp.Visible = 1
>
>after opening the text.xls file i need to filter all the rows in which
>the status column is passed and copy the whole sheet to another sheet

I don't do this often enough to have it to mind, so what I normally do is
record a Macro, convert it to VBS and then convert that to Python.

I'll leave the final step for you to complete yourself, but this will do
what you ask up to the point of copying the selected lines:

from win32com.client import Dispatch
xlApp = Dispatch("Excel.Application")
xlWbook = xlApp.Workbooks.Open(r"C:\Users\Administrator\Desktop\test.xls")
xlApp.Visible = 1
xlWorksheet = xlWbook.Worksheets(1)
xlWorksheet.Columns("A:V").Select() 
xlApp.Selection.AutoFilter( 2, "pass") # column number, filter criteria
xlApp.Selection.AutoFilter( 3, "pass")
xlApp.Selection.AutoFilter( 4, "pass")
xlApp.Selection.AutoFilter( 5, "pass")
#etc, etc - up to column 22 in this case
xlApp.Selection.Copy()

DaveM