+- 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 search a value in excel sheet using vb script (/Thread-How-to-search-a-value-in-excel-sheet-using-vb-script)
How to search a value in excel sheet using vb script - venkatbatchu - 10-07-2009
Hi All,
I have 10000 records in excel sheet, here i need to search for a paricular value in this 10000 records and if it finds a record then it has to fetch the row and coloumn of that specific value and it has to search until it reaches the 10000th record once it is finsihed then by this time i need to fetch all the row and column of the matches data
Please help me in this reagrd...
Thanks in advance,
Venkat
RE: How to search a value in excel sheet using vb script - basanth27 - 10-07-2009
Simple query repeated a 1000 times..Anyways i will give you the logic, go ahead and build your code.
1. Exploit Excel COM component (Excel.Application)
2. Set reference to the active workbook and then to the active worksheet.
3. find the total row count.
4. Use the For loop to loop through and read through the rows and cells.
5. Compare the data retrieved to match with the one you need.
Search this forum with the keyword Excel, read and build your code from the clues i have supplied.
RE: How to search a value in excel sheet using vb script - venkatbatchu - 10-07-2009
Hi Basanth,
Thanks for giving the logic and i implemeted and its working fine but which is taking more time to execute, can u please try to reduce the execution time
Code:
set a=Createobject("Excel.Application")
set b=a.Workbooks.open("D:\av.xls")
set c=a.worksheets("sheet1")
m=2
For i=1 to 40 step 1 'For number of rows i.e it will be searching among the first 40 rows
For j=1 to 3 step 1 ' for number of columns i.e it wil be searchinf among the first 3 columns
d=c.cells(i,j).value
If d="Tokyo" then
datatable.Value(1,1)=i
datatable.Value(2,1)=j
datatable.SetCurrentRow(m)
m=m+1
end if
Next
Next
b.close
datatable.Export("d:/av1.xls")Please help me in this reagard,
Thanks in advance,
Venkat.
RE: How to search a value in excel sheet using vb script - basanth27 - 10-07-2009
It is normal for it behave so. Your exploitation of the COM is one which is actually taking time. The init , execute action requires several seconds.
What is the total time taken for the execution ? Morever you havent killed the Excel object.You should do that else excel.exe wouldnt let you open another copy.