Search for a particular value in Excel
Set appExcel = CreateObject(“Excel.Application”)
appExcel.visible=true
Set objWorkBook = appExcel.Workbooks.Open (filepath)’opens the sheet
Set objSheet = appExcel.Sheets(“Sheet1”)’ To select particular sheet
With objSheet.UsedRange ‘ select the used range in particular sheet
Set c = .Find (“nn”)’ data to find
For each c in objSheet.UsedRange’ Loop through the used range
If c=”nn” then’ compare with the expected data
c.Interior.ColorIndex = 40′ make the gary color if it finds the data
End If
Set c = .FindNext(c)’ next search
next
End With
objWorkBook.save
objWorkBook.close
set appExcel=nothing
Author:Mohan Kakarla
18 Comments »
Leave a comment
Mohan Kumar Kakarla
-
Archives
- July 2011 (1)
- March 2011 (1)
- November 2010 (1)
- November 2008 (4)
- October 2008 (2)
- September 2008 (2)
- June 2008 (1)
- March 2008 (4)
- February 2008 (44)
-
Categories
- Automated Test Script Creation Process
- Automating MS Word
- Automating QC
- Automation Object Model For QTP
- Backward compatability in QTP
- Browser Scripts
- COM and QTP
- compare 2 text files
- Descriptive programming
- Descriptive Programming and Child Objetcs
- Dictonary Object
- DotNetFactory
- Excel Automation
- Extra Topics
- Files and Folders
- General Standards to be followed in Test scripts
- New Features In QTP9.5
- QC-OTA
- QTP Additional Faqs
- QTP Faqs
- QTP Naming Convention
- QTP Tips and Faqs
- Running stored procedures from QTP
- Send keyboard keys
- Uncategorized
- Uninstall a software using QTP
- Uninstall a software using vbscript
- Update data in a database
- VBScript and IE Automation
- VBScript Faqs and Useful resources
- What’s New in Quick Test Pro 9.0
- Whats New in QTP 9.5
- XML Scripting
-
RSS
Entries RSS
Comments RSS
Quick test professional 
hey can you help me in this issue ?
i have a batch process which runs for 8 hours .Now the developement of the application is complete .It’s in testing phase.while testing(automation testing , with any tool )can the script call the batch process in and can the script wait for results from batch process for such a long time ?
meyya,
yes it can wait , for that use shell object
Dim oShell
Set oShell = CreateObject (“WSCript.shell”)
oShell.run “cmd /K CD C:\Documents and Settings\kmohankumar\Desktop & 2.vbs”,,true’ here true indicates wait until this step completes, if u provide false here the qtp does not wait till it completes
Set oShell = Nothing
msgbox “hi”
Hi,
Can you please help me with a problem that I’m having with QTP? I need to loop through an excel sheet with data and create an array with required data.
I hope you can understand my question. Please let me know if you cannot. I’ll try in a different way. Thanks.
Hi
Good one
it will be more fast if you use this
Set appExcel = CreateObject(“Excel.Application”)
appExcel.visible=true
Set objWorkBook = appExcel.Workbooks.Open (filepath)
Set objSheet = appExcel.Sheets(“Sheet1”)
set objValueFind = objectSheet.UsedRange.Find(“rajiv”)
If Not objValueFind Is Nothing Then
rem find the address
firstAddress = objValueFind.Address
rem make the gary color if it finds the data
objValueFind.Interior.ColorIndex = 40
Do
Set objValueFind = .FindNext(objValueFind)
Loop While Not objValueFind Is Nothing And objValueFind.Address firstAddress
End If
I am unable to run it, as there might be some issue with Do loop.. can you please help me
There is no quliflier in below line
Loop While Not objValueFind Is Nothing And objValueFind.Address????firstAddress
Hi,
Can u please help me with this. I need to find the repeated entries also. The above code searches for a text but it colors only one again and again when multiple entries of the text are present.
Hello,
I’m a new in QTP. please help me, I wants write a test case in google web page, how to I write it or what is the procedure……….
Please help me………..
hi mahan and all,
if we release parents memory does it really release the child object memory or we need to manually release the child objects memory too ?
hi mohan,
i want to search for a particular number like 235613 in an excel sheet… if it is not found in the first sheet i have to look into the second sheet until i get that no….
i have used the code given above.. but the problem is i am exporting some information into the excel sheet.. in that excel sheet only i am searching for a particular value as i said earlier… the problem exist here…the file name and the sheet name are same… so i am getting an error in this line (Set objSheet = appExcel.Sheets(“Sheet1″))… instead of Sheet1 i have used the file name but still showing the error as “Subscript out of range”….. help me out yaar….
help me out in this…
Hi All,
Can any one explain me how to validate each cell when the excel sheet is loaded into data table where i have to validate the each cell before set the value to the application.
Any one have answer.Waiting for your repaly.
Thank You
Jagath
Can we find the row number and Column number using the .Find(“abc”) logic.
I mean to ask after value is searched using .Find(“”) method.Will it give the row and Column number value.
Hi,
Search for a particular value in Excel, Descriptive Programming for “Search for a particular value in Excel” is not working.
Hi, I would like Thanks and Appreciate you stuff which u given “Search for a particular value in Excel” .
I was struggling to find the cell number with match string . Thanks I got in you block.
I want the code to Minimize and Maximize the Browser (IE version 7) if there are multiple site are open in Browser.
Advance Thanks
Imtiaz
hi,
i want help for checking PDF file contents like header contents, footer contents, data, column length, spacing between two text using vb scripting.
your help will be really appreciated
none of the above codes are working… please help and post the correct one. I want to search for a text = “.length()” in the excel sheet and also need to check that the is of red color or not. I used the code below but it’s not working. please help:
On Error Resume Next
Set objExcelApp = createobject(“Excel.Application”)
objExcelApp.visible=true
Set objWorkbook = objExcelApp.Workbooks.Open(“C:\Shalabh\Automation Framework\Templates\DIA_Proposed_Mapping_Template_GHEPM.xls”)
Set objWorksheet = objWorkbook.Worksheets(“DIA Mapping”)
set objValueFind = objWorksheet.UsedRange.Find(“.length()”)
If Not objValueFind Is Nothing Then
rem find the address
firstAddress = objValueFind.Address
rem make the gray color if it finds the data
MsgBox “value Found”
objValueFind.Interior.ColorIndex = 40
Do
Set objValueFind = .FindNext(objValueFind)
Loop While Not objValueFind Is Nothing And objValueFind.Address firstAddress
End If
Thanks
[…] Search for a particular value in Excel […]
I am getting the following run time effor when i am running the following script:-
Error msg:-
Subscript out of range
Line (6): “objWorkbook1.Worksheets(“Sheet1”).Range(“B1″).PasteSpecial Paste =xlValues”.
Script:-
Set objExcel = CreateObject(“Excel.Application”)
objExcel.Visible = True
Set objWorkbook2= objExcel.Workbooks.Open(“C:\TEST\Practise\testrun.xls”)
Set objWorkbook1= objExcel.Workbooks.Open(“C:\TEST\Practise\res.xls”)
objWorkbook2.Worksheets(“Sheet1”).UsedRange.Copy
objWorkbook1.Worksheets(“Sheet1”).Range(“B1”).PasteSpecial Paste =xlValues
objworkbook1.Worksheets(“Sheet2”).pastespecial
objWorkbook1.save
objWorkbook2.save
objWorkbook1.close
objWorkbook2.close
set objExcel=nothing
Please help i am new to qtp and vbscripting
please help me..
need to create Function/Sub for below script to use call or execute in qtp framework.
—————————————————————————————
‘Declaring excel sheet
Dim objExcel, objWorkBook, objWorkSheet, i
Set objExcel=CreateObject(“excel.application”)
Set objWorkBook=objExcel.Workbooks.open(“C:\ZCWebDataTable2.xls”)
Set objWorkSheet=objWorkBook.Worksheets(“ZCReq”)
Rows_Count=objworksheet.Usedrange.rows.count
For i= 1 to Row
For j=1 to Col
Msgbox mysheet.cells(i,j).value
Next
Next
‘Get URL from excel and open
systemutil.Run “iexplore.exe”, objWorkSheet.cells(2,”B”)