Micro Focus QTP (UFT) Forums
Search for a word in excel string - 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: UFT / QTP Beginners (https://www.learnqtp.com/forums/Forum-UFT-QTP-Beginners)
+--- Thread: Search for a word in excel string (/Thread-Search-for-a-word-in-excel-string)

Search for a word in excel string - QAVA - 11-21-2012

Hi,

I am trying to find the word "qtp" if exist delete it from the cell. i dont want to delete all the data from the cell . only qtp.

that means if cell has "i like qtp". i want to delete "qtp"

This code works just fine to find qtp. i dont know how to delete qtp after if finds it.

Please advise.

Code:
Dim xl
t = "qtp"
Set xl = createobject("excel.application")
xl.Visible = true
Set sd = xl.Workbooks.Open ("F:\usp\FP Tickets to Automate2.xls")
Set st = sd.Worksheets("Sheet1")
rows = st.usedrange.rows.count
coloumn = st.usedrange.columns.count
msgbox t
For i = 1 to rows
For j = 1 to 2
Dim a
a = st.cells(i,j)
msgbox "a  "   &a
If Instr(1,a,t) >0 Then
'st.cells(i,6) = "pass"
msgbox "dd"
End If
Next
next


RE: Search for a word in excel string - Ankesh - 11-21-2012

Code used is not optimized..its time consuming. you should better be using excel find and replace option for this.

use replace function instead of delete.

First find the text "i like qtp" and then replace it with "i like".

No delete is required. Smile

Regards,
Ankesh

RE: Search for a word in excel string - QAVA - 11-21-2012

I am not sure how to do that in qtp. How do i replace "i like qtp" with "i like" in excel using qtp?

RE: Search for a word in excel string - ssvali - 11-21-2012

Sample Code :-
Code:
a = "i like QTP"

b= Replace(a,"QTP","")

Msgbox b


RE: Search for a word in excel string - QAVA - 11-21-2012

First i need to find out whether QTP exist. if exist then replace with blank space. How to do that in excel?

I am simply working with excel. I just need to delete any cell has qtp and delete that. That cell has lot of data along with qtp.

How?

First i want to find whether any cell has qtp with other data.
second if qtp exist, replace with blank or delete it.

(11-21-2012, 07:08 PM)ssvali Wrote: Sample Code :-
Code:
a = "i like QTP"

b= Replace(a,"QTP","")

Msgbox b
In excel, i also have in different cells 2) 3) 4).
I want to delete all of them (2) 3) 4).
) if it is any cell.

RE: Search for a word in excel string - ssvali - 11-22-2012

Can you please attach ur excel...

RE: Search for a word in excel string - vinod123 - 11-23-2012

Code:
Dim appExcel, objWorkBook, objSheet, columncount, rowcount,

'Create the application object for excel
Set appExcel = CreateObject("Excel.Application")

'Set the workbook object by opening
Set objWorkBook = appExcel.Workbooks.open("c:\Smoke_Test\SmokeTest.xls")

'Set the Worksheet object
Set objSheet = appExcel.Sheets("Global")

'Get the count for total used columns
columncount = objSheet.usedrange.columns.count

'Get the count for total used rows
rowcount = objSheet.usedrange.rows.count

'Assign the data to search for
Find_Details="Report"

'Iterate the loop through each cell to find out required data
For a= 1 to rowcount
For b = 1 to columncount
fieldvalue =objSheet.cells(a,b)
If  cstr(fieldvalue)= Cstr(Find_Details) Then
msgbox cstr(fieldvalue)
Exit For
End If
Next
Next