+- 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: clearing excel sheet values (/Thread-clearing-excel-sheet-values)
clearing excel sheet values - beejfred - 09-19-2014
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 sheetRE: clearing excel sheet values - vinod123 - 09-25-2014
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