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