How to perform the Filter action in excel using vb script?

How to perform the Filter action in excel using vb script? - Printable Version

+- Micro Focus QTP (UFT) Forums (https://www.learnqtp.com/forums)
+-- Forum: Micro Focus UFT (earlier known as QTP) (https://www.learnqtp.com/forums/Forum-Micro-Focus-UFT-earlier-known-as-QTP)
+--- Forum: VB Scripting/Descriptive Programming (https://www.learnqtp.com/forums/Forum-VB-Scripting-Descriptive-Programming)
+--- Thread: How to perform the Filter action in excel using vb script? (/Thread-How-to-perform-the-Filter-action-in-excel-using-vb-script)

How to perform the Filter action in excel using vb script? - venkatbatchu - 10-08-2009

I have some 10000 records in excel among this there are duplicated data availabale, i need to filter the data based on selected value,

Please help me in this regard

Thanks in Advance,
Venkat

Hi All,
With some R&d and by searching i have got up to some extent of the solution which is am able to set the filter action and now i want to select the particular value in the data

Please find the below one

Code:
set aa=Createobject("Excel.Application")

set b=aa.Workbooks.open("D:\subbu.xls")

set c=aa.worksheets("sheet2")

c.cells(1,5).autofilter

set aa=Nothing

msgbox "Done"

Thanks in advance,
Venkat

RE: How to perform the Filter action in excel using vb script? - Saket - 10-08-2009

Hi Venkat,

Try using the arguments for autofilter.
see if this works for you

Code:
c.cells(1,5).autofilter 5,"a"

RE: How to perform the Filter action in excel using vb script? - venkatbatchu - 10-08-2009

Thanks saket for replying,
i have tried with that and got the message like this
"---------------------------
Windows Script Host
---------------------------
Script: C:\Documents and Settings\venkat.batchu\Desktop\To get a value from specific row and 1coloumn from excell.vbs
Line: 5
Char: 1
Error: AutoFilter method of Range class failed
Code: 800A03EC
Source: Microsoft Office Excel

---------------------------
OK
---------------------------
"
Venkat..

RE: How to perform the Filter action in excel using vb script? - Saket - 10-08-2009

how many autofilter you have on the sheet?
use in the syntax below
c.cells(1,5).autofilter <<autofilter fileld Number>>,<<your selection criteria>>
if you have only one autofilter field in the sheet then
use c.cells(1,5).autofilter 1,"Selection criteria"

does this work now?

RE: How to perform the Filter action in excel using vb script? - venkatbatchu - 10-08-2009

Hi Saket,
Thanks for replaying which is working and if you dont mind can u please describe the cells(1,5) here what is meant by 1 and what is meant by 5
i am using blindly (1,1) with this i am getting as per my expected data
please do this favor

Thanks.........................

RE: How to perform the Filter action in excel using vb script? - Saket - 10-08-2009

when you use cells in excel spreadsheet then a cell indicates a box on a particular row and column.
So whenever you need to have an operation on any cell then you will have to provide which row and which column.
So in your code when you use cells(1,5) that indicates you are working on a cell which is on first row and column 3.

see excel vba help for more info on this.

RE: How to perform the Filter action in excel using vb script? - venkatbatchu - 10-08-2009

Thanks alot saket,