Micro Focus QTP (UFT) Forums
Does QTP support writing runtime data to multiple sheets in excel - Printable Version

+- 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: VB Scripting/Descriptive Programming (https://www.learnqtp.com/forums/Forum-VB-Scripting-Descriptive-Programming)
+--- Thread: Does QTP support writing runtime data to multiple sheets in excel (/Thread-Does-QTP-support-writing-runtime-data-to-multiple-sheets-in-excel)

Does QTP support writing runtime data to multiple sheets in excel - Shwethareddy - 10-16-2012

Hi,

I am trying to write data to multiple sheets in excel, but isnt working with the below code. Please let me know for any corrections.
Updating Links on different pages of the webpage directly to excel.

'Admin Login

Code:
Browser("CCAMS | Login").Page("CCAMS | Login").WebEdit("UserName").Set "sfpuc.admin@jenesys.in"
Browser("CCAMS | Login").Page("CCAMS | Login").WebEdit("Password").Set "admin@jenesys"
Browser("CCAMS | Login").Page("CCAMS | Login").WebButton("Login").Click

Set alllinkob = Description.Create()
alllinkob("micclass").value ="Link"

Set objAlllinkObj =Browser("CCAMS | Login").Page("CCAMS | Home | 1.1.33").ChildObjects(alllinkob)

'Write to Excel  
Set myxl=createobject("Excel.Application")
myxl.Application.Workbooks.Open "C:\LinkCheckLogin.xlsx"
myxl.Application.Visible=True
Set mysheet=myxl.ActiveWorkbook.Worksheets("Home")


'Home Page-fetching Links info
For a=0 to objAlllinkObj.count-1

url = objAlllinkObj(a).getroproperty("url")
nam=objAlllinkObj(a).getroproperty("name")

call geturlstatus(url )

Next
'Site Search-Different webpage in the same applictaion

Browser("CCAMS | Login").Page("CCAMS | Home | 1.1.33").Link("Site Search").Click

myxl.Application.Visible=True
Set mysheet=myxl.ActiveWorkbook.Worksheets("SiteSearch")


Set lnk=description.Create
lnk("micclass").value="Link"

Set Sitelnk=Browser("CCAMS | Login").Page("CCAMS | Site Search |").ChildObjects(lnk)
For a=0 to Sitelnk.count-1

url = Sitelnk(a).getroproperty("url")
nam=Sitelnk(a).getroproperty("name")

call geturlstatus(url )

Next
'Link Check function

Public Function geturlstatus(url )
     On Error Resume Next
     Call ClearBrowserCache()
     Set webService = nothing
     Set webService= CreateObject("Microsoft.XMLHTTP")  ' Create an xmlhttp object
     webService.open "GET", url, False     ' Opens the connection to the remote server
     webService.Send
     pagestatus = webService.status
     If pagestatus >= 200 or pagestatus <= 206 Then
         mysheet.Cells(a+2,1)=nam
         mysheet.Cells(a+2,2)="Success"
         mysheet.Cells(a+2,3)=pagestatus
         mysheet.Cells(a+2,4)=url
                     geturlstatus = 0

     elseif pagestatus >= 302 or pagestatus <= 307 Then
     mysheet.Cells(a+2,1)=nam
         mysheet.Cells(a+2,2)="Redirection"
         mysheet.Cells(a+2,3)=pagestatus
         mysheet.Cells(a+2,4)=url
             geturlstatus = 1

      elseif pagestatus >= 400 or pagestatus <= 423 Then
         mysheet.Cells(a+2,1)=nam
         mysheet.Cells(a+2,2)="Client Error"
         mysheet.Cells(a+2,3)=pagestatus
         mysheet.Cells(a+2,4)=url
            geturlstatus = 1

  elseif pagestatus >= 500 or pagestatus <= 505 Then
         mysheet.Cells(a+2,1)=nam
         mysheet.Cells(a+2,2)="Server Error"
         mysheet.Cells(a+2,3)=pagestatus
         mysheet.Cells(a+2,4)=url
                geturlstatus = 1
    
     End If
     Set webService = nothing
     err.clear
End Function


RE: Does QTP support writing runtime data to multiple sheets in excel - Ankesh - 10-16-2012

What is the error you are getting?

RE: Does QTP support writing runtime data to multiple sheets in excel - Shwethareddy - 10-16-2012

Hi Ankesh,
Thanks for teh reply, was able to solve it by addding new sheet method to shift teh focus to next sheet.

Thanks