Find the 'String' from excel sheet and save to another sheet.But we d

Find the 'String' from excel sheet and save to another
sheet.But we don't know the string column number and row number?

Question Posted / bharathchandra maddhi

4 Answers
7580 Views
I also Faced
E-Mail Answers

Answers were Sorted based on User's Feedback

Find the 'String' from excel sheet and save to another sheet.But we don't know the s..

This is working code. Don't forget to save workbook else nothing will get saved. Code is:

Set obj=createobject("excel.application")
Set obj2=obj.Workbooks.Open("C:UserspushkarDesktopBook1.xls")
Set obj3=obj2.Worksheets("Sheet1")
Set obj4=obj2.Worksheets("Sheet2")
rw=obj3.usedrange.rows.count
clm=obj3.usedrange.columns.count
For i=1 to rw
For j=1 to clm
a=obj3.cells(i,j).value
If strcomp(a,"nature")=0 Then
obj4.cells(1,2)=a
End If
Next
Next
obj2.Save
obj.Quit

Is This Answer Correct ?

1 Yes

0 No

Find the 'String' from excel sheet and save to another sheet.But we don't know the s..

Answer / kishan

set xlo=createObject("Excel.Application")
xlo.visible=True
set wbo=xlo.Workbooks.open("D:\QTP\TestData\abcdef.xls")
set wso=wbo.worksheets("Sheet1")

rcnt=wso.usedRange.rowcount
ccnt=wso.usedRange.columncount

for i=1 to rcnt
for j=1 to ccnt
str=wso.cells(i,j)
position=instr(1,str,"expected_string")
if position > 1
print "Required String is in
cell"&wso.cells(i,j)
xlo.worksheets("sheet2").cells
(1,1)="expected_string"
end if
next
next

Is This Answer Correct ?

0 Yes

0 No

Find the 'String' from excel sheet and save to another sheet.But we don't know the s..

Answer / bharathchandra maddhi

Option explicit
Dim exo, wbo, wso1, wso2, nor, noc, i, j, x, y
'Open a Excel file
Set exo=CreateObject("excel.application")
exo.Visible=true
Set wbo=exo.Workbooks.Open("C:\Bharath.xls")
Set wso1=wbo.Worksheets("sheet1")
Set wso2=wbo.Worksheets("sheet2")
nor=wso1.usedrange.rows.count
noc=wso1.usedrange.columns.count
'Varifying excel sheet strings
For i=1 to nor step 1
For j=1 to noc step 1
x=wso1.cells(i,j)
x=cstr(x)
If strcomp(x,"Bharath")=0 Then
wso2.cells(1,1)=x
End If
Next
Next
'Close excel s/w and Destroy the objects
exo.Quit
Set wso1=Nothing
Set wso2=Nothing
Set wbo=Nothing
Set exo=Nothing

Is This Answer Correct ?

1 Yes

2 No

Find the 'String' from excel sheet and save to another sheet.But we don't know the s..

Answer / raju

Set xl = CreateObject("Excel.Application")
set xlwbook = xl.Workbooks.Open(Excel Path)

Set xlWS = xlwbook.Worksheets(1)

Set k = xl.Worksheets(1).UsedRange.Find(Value to
Search)
On error resume next
k=k.value
If err.number>0 Then
msgbox "Pass"
else
Flag=0
If flag=0 Then
Status = "Pass"
StepDesc = "The Work Book has the
values "
Else
Status = "Pass"
StepDesc = "The Work Book Does not
has the values "
End If
End If

Set xlWS = Nothing
set xlwbook = Nothing
Set xl = Nothing

Please let me know if it's not working
raju.ippali@gmail.com

Is This Answer Correct ?

0 Yes

1 No

Post New Answer