Search for Particular value in Excel

Set myxl = createobject("excel.application")
myxl.Application.Visible = true
myxl.Workbooks.Open "C:\Documents and Settings\pavann\Desktop\qtp15"
'This is the name of Sheet in Excel file "qtp15.xls" where data needs to be entered
set mysheet = myxl.ActiveWorkbook.Worksheets("Sheet1")
'Select the used range in particular sheet
With mysheet.UsedRange
' Data "PAVAN" to search
' Loop through the used range
For each search_data in mysheet.UsedRange
' compare with the expected data
If search_data="PAVAN" then
'make the cell with color if it finds the data search_data.Interior.ColorIndex = 10
End If
next
End With
myxl.ActiveWorkbook.Save
myxl.ActiveWorkbook.Close
myxl.Application.Quit
Set mysheet =nothing
Set myxl = nothing

-- Pavankumar Nandagiri

Read the data from Excel File

Set xlapp3 = createobject("excel.application")
xlapp3.Visible = true
Set xlbook3 = xlapp3.Workbooks.Open("C:\Documents and Settings\pavann\Desktop\qtp15")Set xlsheet3= xlbook3.Worksheets("sheet1")
xlrwcnt = xlsheet3.usedrange.rows.count
'msgbox xlrwcnt
xlcolcnt = xlsheet3.usedrange.columns.count
'msgbox xlcolcnt
For m=1 to xlrwcnt
For n=1 to xlcolcnt
val = xlsheet3.cells(m,n).value
msgbox val
Next
Next

-- Pavankumar Nandagiri

Creating a new Excel sheet, Opening the exsisting sheet & entering data and saving it

'Creating a new Excel sheet
Set xlapp = createobject("excel.application")
xlapp.Visible = true
xlapp.Workbooks.Add
xlapp.ActiveWorkbook.SaveAs("C:\Documents and Settings\pavann\Desktop\qtp12")xlapp.Application.Quit
Set xlapp=nothing

'Opening the exsisting sheet & entering data and saving it
Set xlapp1 = createobject("Excel.Application")
xlapp1.Visible = true
Set xlbook1 = xlapp1.Workbooks.Open("C:\Documents and Settings\pavann\Desktop\qtp12")Set xlsheet1 = xlbook1.Worksheets("sheet1")
For i = 1 to 10
xlsheet1.cells(i,1).value = "pavan"
For k = 1 to 10
If k>1 Then
xlsheet1.cells(i,k).value = "soumya"
End If
Next
Next
set objrange = xlsheet1.usedrange
For each cell in objrange
cell.value = ucase(cell.value)
Next
xlbook1.Save
xlapp1.Application.Quit
Set xlapp1=nothing

-- Pavankumar Nandagiri

Adding values into excel sheet and changing the cell values to Uppercase

Set xlapp = createobject("Excel.Application")
xlapp.Visible = true
set xlbook = xlapp.Workbooks.Open("C:\Documents and Settings\pavann\Desktop\test")
set xlsheet = xlbook.Worksheets("sheet1")
For i = 1 to 10
For j= 1 to 10
xlsheet.cells(i,1) = "pavan"
If j>1 Then
xlsheet.cells(i,j) = "soumya"
End If
Next
Next
set objrange = xlsheet.usedrange
For each objcell in objrange
objcell.value = Ucase(objcell.value)
Next

-- Pavankumar Nandagiri

Associating the evironment variables (By Parametrization)

' Associating the evironment variables (By Parametrization)

'Develop the script in test pane as below:' Setting the declered environment value (a ) to value1 edit button
VbWindow("Form1").VbEdit("val1").Set environment.Value("a")
' Setting the declered environment value (b ) to value2 edit button
VbWindow("Form1").VbEdit("val2").Set environment.Value("b")
' clicking on ADD button
VbWindow("Form1").VbButton("ADD").Click

-- Pavankumar Nandagiri

To find the last day of the current month & also last day of any month and year

'To find last day of the current month
MsgBox DateSerial(Year(Now), 1 + Month(Now), 0)

'To find last day of any month and year

usr_dte = inputbox("Enter the date for which you want to know the last day of the month in the format dd/mm/yyyy")
current_date = now()
only_date = left(current_date,9)
dat_diff = datediff("m",only_date,usr_dte)
req_mnth = DateAdd ("m",dat_diff,only_date)
a = split(req_mnth,"/")
mnth = a(0)
dat = a(1)
yr= a(2)
msgbox DateSerial(yr, 1 +mnth, 0)

--Pavankumar Nandagiri

Creating a new column in a runtime datatable and adding values and moving the these values to the new column

Datatable.AddSheet("sample").AddParameter "OldColumn","pavan"
For i= 1 to 10
datatable.SetCurrentRow(i)
datatable.Value("OldColumn","sample") = "pavan"&(i)Next
rw_cnt = datatable.GetSheet("sample").GetRowCount
msgbox rw_cnt
datatable.GetSheet("sample").AddParameter "newcoloumn","Row1Value"
For k = 1 to rw_cnt
datatable.SetCurrentRow(k)
old_val = datatable.Value("OldColumn","sample")
datatable.Value("newcoloumn","sample") = old_val
Next
datatable.GetSheet("sample").DeleteParameter("OldColumn")


-- Pavankumar Nandagiri

How to add multiple values to a column for a runtime data table

'Adding a new sheet @ runtime
Datatable.AddSheet("dtGlobalSheet").AddParameter "OldColumn","pavan"
'Adding multiple values
For i= 1 to 10
datatable.SetCurrentRow(i)
datatable.Value("OldColumn","dtGlobalSheet") = "pavan"&(i)
Next

Thanks,
Pavankumar Nandagiri