clearing excel sheet values

[beejfred]

Hi,
I am iterating search results by outputting them to a row for each test run... But when QTP writes to the next row in the excel data sheet, it just adds to the last set of resutls in my local sheet. So question is, how do I clear my local sheet each iteration of rows?
thank you
Fred

Code:

intStartID = Parameter("StartID") intEndID = Parameter("EndID") intCurrentID = intStartID For i = intStartID to intEndID 'New Code **************** Browser ("Clinical Trials On-Line").Page("Clinical Trials On-Line_3").WebList("ctl00$ContentPlaceHolder1$s_categor").Select GetInputDBValueByID("VALUESET", INTCURRENTID) 'Browser("Clinical Trials On-Line").Page("Clinical Trials On-Line_3").WebList("ctl00$ContentPlaceHolder1$s_categor").Select "Breast Cancer" Browser("Certificate Error: Navigation").Page("Clinical Trials On-Line_4").Image("ctl00$ContentPlaceHolder1$s_ImgBtnF").Click 34,14 Browser("Clinical Trials On-Line_4").Page("Clinical Trials On-Line").Link("All").Click If Browser("Clinical Trials On-Line_2").Page("Clinical Trials On-Line_7").WbfGrid("ContentPlaceHolder1_RadioButtonList").Exist Then Browser("Clinical Trials On-Line_2").Page("Clinical Trials On-Line_7").WbfGrid("ContentPlaceHolder1_RadioButtonList").SetCellData 1,1,"1" Else End If 'DataTable("ProcNum") = "" Set ExcelObj = createobject("excel.application") ExcelObj.Workbooks.Open "C:\CTOL_Search_Test\CTOL_Results_Matrix.xls" 'ExcelObj.Application.Visible = true Set mySheet = ExcelObj.ActiveWorkbook.Worksheets("RESULTS") UseCaseArea = GetInputDBValueByID ("VALUESET", IntCurrentID) ' UseCaseComponent = GetInputDBValueByID ("AREA", IntCurrentID) ' UseCaseDescription = GetInputDBValueByID ("Description", IntCurrentID) ' strSearch = Browser("Clinical Trials On-Line_2").Page("Clinical Trials On-Line_7").WebElement("41 Trial(s) Found Using").GetROProperty("innertext") strSearch = Browser("Clinical Trials On-Line_2").Page("Clinical Trials On-Line_7").WebTable("41 Trial(s) Found Using").GetROProperty("innertext") strSearch = Left (strSearch, 3) 'msgbox strSearch 'strSearch = Browser("Clinical Trials On-Line_2").Page("Clinical Trials On-Line_7").WebElement("WebTable").GetROProperty("innertext") 'strSearch = Left (strSearch, 3) '***************************************************************** mySheet.Cells(intCurrentID, 4) = "" mySheet.Cells(intCurrentID, 4) = strSearch 'mySheet.Cells(intCurrentID, 1) = UseCaseArea ' 'mySheet.Cells(intCurrentID, 2) = UseCaseComponent ' 'mySheet.Cells(intCurrentID, 3) = UseCaseDescription ' Set oDesc = Description.Create() oDesc("class").Value = "protocolOptions" oDesc("micclass").Value = "WebElement" 'Set oChild = Browser("Browser").Page("Page_8").ChildObjects(oDesc) Set oChild = Browser("Clinical Trials On-Line_2").Page("Clinical Trials On-Line_7").ChildObjects(oDesc) intTableCount = oChild.Count intCurTblRow = DataTable.LocalSheet.GetCurrentRow For intObj = 0 to intTableCount -1 DataTable.LocalSheet.SetCurrentRow intObj +1 DataTable("ProcNum") = oChild(intObj).GetROProperty("innertext") strTest = DataTable("ProcNum") strTest = Left(strTest,6) 'msgbox strTest 'DataTable("ProcNum") = strActual If intObj <8 Then 'Showing first 8 protocol numbers that are being parsed 'msgBox "Row: " & intObj & " Parsed Protocol Number is: " & oChild(intObj).GetROProperty("innertext") ' msgbox "Row: " & intObj & " Parsed protocol Number is: " & strActual End If If ProtNo = "" Then ProtNo = strTest 'ProtNo = DataTable.Value("ProcNum") 'ProtNo = Left(ProtNo,6) 'msgbox ProtNo Else 'ProtNo = ProtNo & "; " & DataTable.Value("ProcNum") ProtNo = ProtNo & "; " & strTest End If mySheet.Cells(intCurrentID, 6) = ProtNo Next 'ProtNo = DataTable.Value("ProcNumCTOL2") '*********************************************************************** mySheet.Cells(intCurrentID, 1) = UseCaseArea ' mySheet.Cells(intCurrentID, 2) = UseCaseComponent ' mySheet.Cells(intCurrentID, 3) = UseCaseDescription ' ExcelObj.ActiveWorkbook.Save ExcelObj.Application.Quit Set ExcelObj = Nothing SystemUtil.CloseProcessByName("EXCEL.EXE") intCurrentID = intCurrentID +1 Browser("Clinical Trials On-Line_2").Page("Clinical Trials On-Line_7").Link("Active Trials").Click Next 'Loop next record from data sheet

[vinod123]

use the below function

Code:

Public Function BIP_xlsDeleteRowRange (sSrcPath, sDestPath, sStartRow, sEndRow) ‘Create Excel object Set oExcel = CreateObject(“Excel.Application”) ‘Sets the application to raise no app alerts ‘In this case it will allow a file overwrite w/o raising a ‘yes/no’ dialog oExcel.DisplayAlerts = False ‘Open Book in Excel Set oBook = oExcel.Workbooks.Open(sSrcPath) ‘Set Activesheet Set oSheet = oExcel.Activesheet ‘Delete row range oSheet.Rows(sStartRow +”:”+ sEndRow).Delete ‘Save new book to Excel file oBook.SaveAs (sDestPath) ‘Close the xls file oExcel.Workbooks.Close() End Function