how to copy webpage into excel using qtp

[harp]

i am fairly a new user of qtp. i am trying to record a script which should copy the table in a webpage and its data. the copied data should be pasted into an excel sheet as unicode text (using paste special function of excel). i am facing problem in doing this as on running the script i am getting object not visible error. can someone please help me in figuring this out?

the same process is used for 1992 times. and the webpage is opened through url's stored in local datasheet.

Code:

For i=0 to 1991


SystemUtil.Run "C:\Program Files\Mozilla Firefox\firefox.exe","","c:\documents and settings\Harp ","open"




Dim url1, url2, url3, url

url1= datatable.Value("A", dtlocalsheet)
url2=datatable.Value("B",dtLocalsheet)
url3= datatable.value("C",dtlocalsheet)

url= url1&url2&url3

Window("Mozilla Firefox").WinObject("MozillaWindowClass").Type url
Window("Mozilla Firefox").WinObject("MozillaWindowClass").Type  micReturn
wait(4)
Window("Mozilla Firefox").WinObject("MozillaWindowClass_2").Drag 82,204
Window("Mozilla Firefox").WinObject("MozillaWindowClass_2").Drop 1000,457
'Window("Mozilla Firefox").WinObject("MozillaWindowClass_2").Click 969, 353, micRightBtn
'Window("Window").Click 35,12
'SystemUtil.Run "C:\Program Files\Microsoft Office\Office12\EXCEL.EXE","","",""
'Window("Book1").Activate
'Window("Book1").WinObject("Book1").Click 62,50
'Window("Book1").WinObject("Book1").Click 62, 50, micRightBtn
'Window("Book1").Window("Window").WinObject("NetUIHWND").Click 79,73
'Window("Book1").Window("Paste Special").Click 118,83
'Window("Book1").Window("Paste Special").Click 386,305
window("Mozilla Firefox").Close



wait(8)



next

[KavitaPriyaCR]

Hi
Is the data in the table of web page static? can u pls tell me how you are copying the table data?

---

You can try with:
Use the descriptive programming, to capture the table contents, and use paste special to paste the data into excel file.

[harp]

the data in the table is static.

you can a look at this page: http://emops.tse.com.tw/server-java/t05s...&year=2004

unfortunately idk how to use descriptive programming.

copying that data: highlight the contents starting from debt ratio upto return on stock equity. after that i right click to copy data and then open a worksheet and then do the paste special.

[KavitaPriyaCR]

Hi

Try this, below script is the edited one, will give you the perfect output xls file

Code:

Set desc=Description.Create()
desc("micclass").value="WebTable"
desc("text").Value="200220032004Capital structure analysis.*"
Set WebEdits=Browser("Title:=Financial Analysis").Page("Title:=Financial Analysis").ChildObjects(desc)
cCount=WebEdits(0).getROProperty("cols")
rCount=WebEdits(0).getROProperty("rows")

Set objExcel = CreateObject("Excel.Application")
objExcel.Visible = True
Set objWorkbook= objExcel.Workbooks.Open("C:\Documents and Settings\Administrator\Desktop\ForumExcel.xls")
Set objSheet=objExcel.Sheets("Sheet1")

For i=1 To rCount
For j=1 To cCount
data=WebEdits(0).GetCellData(i,j)
If (Instr(data,"analysis (%)") <> 0 OR Instr(data,"flow (%)") <> 0)Then
    objsheet.Cells(i,j).Value=data
    j=cCount
else
objsheet.Cells(i,j).Value=data
End If
Next
Next
objWorkbook.save
objWorkbook.close
objExcel.Quit
set objExcel=nothing

[harp]

I get this error on running the script:
general run error:

Code:

Set WebEdits=Browser("Title:=Financial Analysis").Page("Title:=Financial Analysis").ChildObjects(desc)

also, what i am trying to achieve is launch url-->copy paste data from that page--> into excel--> quit browser-->launch browser again and enter new url.
for every url http://emops.tse.com.tw/server-java/t05s...&year=2004, co_id(2463 in this case) is a variable which will be different everytime i launch a browser.


also, my settings in qtp are run test on any browser window and on any windows application.

---

Code:

For i=0 to 1991


SystemUtil.Run "C:\Program Files\Mozilla Firefox\firefox.exe","","c:\documents and settings\Harp ","open"




Dim url1, url2, url3, url

url1= datatable.Value("A", dtlocalsheet)
url2=datatable.Value("B",dtLocalsheet)
url3= datatable.value("C",dtlocalsheet)

url= url1&url2&url3

Window("Mozilla Firefox").WinObject("MozillaWindowClass").Type url
Window("Mozilla Firefox").WinObject("MozillaWindowClass").Type  micReturn
wait(4)

Set desc=Description.Create()
desc("micclass").value="WebTable"
desc("text").Value="200220032004Capital structure analysis.*"
Set WebEdits=Browser("Title:=Financial Analysis").Page("Title:=Financial Analysis").ChildObjects(desc)
cCount=WebEdits(0).getROProperty("cols")
rCount=WebEdits(0).getROProperty("rows")

Set objExcel = CreateObject("Excel.Application")
objExcel.Visible = True
Set objWorkbook= objExcel.Workbooks.Open("C:\Documents and Settings\Harp\Desktop\ForumExcel.xls")
Set objSheet=objExcel.Sheets("Sheet1")

For m=1 To rCount
For n=1 To cCount
data=WebEdits(0).GetCellData(m,n)
If (Instr(data,"analysis (%)") <> 0 OR Instr(data,"flow (%)") <> 0)Then
objsheet.Cells(m,n).Value=data
n=cCount
else
objsheet.Cells(m,n).Value=data
End If
Next
Next
objWorkbook.save
objWorkbook.close
objExcel.Quit
set objExcel=nothing

window("mozilla firefox").Close
wait(5)
next

is this correct? try running this code on your machine.

for getting the url, put this in 3 or 4 rows in column A of action1 sheet:
http://emops.tse.com.tw/server-java/t05s...show&co_id=
Coumn B:
3006
2498
2463
Column C:
&year=2004
&year=2004
&year=2004


in test settings from File, i have "run on all rows" in the Run tab. i am trying to provide all info for you to narrow down the cause of error. Hope this helps. thank you for chasing this.

[KavitaPriyaCR]

I have got installed QTP for only IE, here is the complete script, with no change you will get the result (Just paste this script, don't record).

Code:

CO_ID=Array(2463,3006,2498)
For k=0 To 2
Set oIE=CreateObject("InternetExplorer.Application")
oIE.Visible=True
oIE.Navigate "http://emops.tse.com.tw/server-java/t05st22_e?TYPEK=sii&step=show&co_id="&Co_ID(k)&"&year=2004"
Set desc=Description.Create()
desc("micclass").value="WebTable"
desc("text").Value="200220032004Capital structure analysis.*"
Set WebEdits=Browser("Title:=Financial Analysis").Page("Title:=Financial Analysis").ChildObjects(desc)
cCount=WebEdits(0).getROProperty("cols")
rCount=WebEdits(0).getROProperty("rows")

Set objExcel = CreateObject("Excel.Application")
objExcel.Visible = True
Set objWorkbook= objExcel.Workbooks.Open("C:\Documents and Settings\Administrator\Desktop\ForumExcel.xls")
Set objSheet=objExcel.Sheets("Sheet"&k+1)

    For i=1 To rCount
        For j=1 To cCount
        data=WebEdits(0).GetCellData(i,j)
        If (Instr(data,"analysis (%)") <> 0 OR Instr(data,"flow (%)") <> 0)Then
            objsheet.Cells(i,j).Value=data
            j=cCount
            else
            objsheet.Cells(i,j).Value=data
        End If
        Next
    Next
    objsheet.Cells(i,j).Value=CO_ID(k)
    objWorkbook.Save
    objWorkbook.Close
    objExcel.Quit
    oIE.Quit
Next

And pls et me know if it worked.

[A.Saini]

Hi Harp,

In this case we are not able to use our normal (simple) approach to get the data of web table into Excel Sheet. There are 2 reasons of that:

1. If you add any data of the web table into repository, it's taking "innertext" & "html tag" as the mandatory property. The hierarchy is Browser->Page->WebElement (Data Table is note taken) and RE for Web Element also not working.

2. By using any combination of web table properties, QTP is not recognizing the table while using Descriptive Programming.

But there is an alternative solution:
The below code will give you the content of the table. You have to put that content at correct location in Excel Sheet. You can use properties like "class" etc.

Code:

Set objDesc = Description.Create
   objDesc( "micclass" ).value = "WebElement"         
   objDesc( "html tag" ).value  = "TD"
'   objDesc( "class" ).value  = "in-l-10"      
  
  Set arrObj = Browser("name:=Financial Analysis").Page("title:=Financial Analysis").ChildObjects(objDesc)
  total = arrObj.count
  MsgBox total

  For i=0  to total-1
     data= arrObj(i).GetROProperty("innerhtml")
     print data
  Next

I hope it will work for you....

[harp]

I am attaching the ForumExcel.xls file with output after the script executed completely. Apparently, the script saved data only for the first co_id. for subsequent array elements no data was input into the excel.

---

Its working. i failed to notice sheets&k+1


can we get all data in sheet 1 itself - append data after the last row with data in sheet 1 and keep on doing that for every co-id?

[A.Saini]

Hi,

As per my understanding this Regular Expression will work only for first row not for the entire web table.

Code:

desc("text").Value="200220032004Capital structure analysis.*"

Note: "text" value is totally different for each row.

Well, it's very challenging problem .I will awesome if some senior QTP person share his/her views about it.

[harp]

Hello M/s Priya and Mr. Saini,

I am glad to inform that the script has run successfully. I made a few modifications to the script to add a new sheet each time the script runs.This will save me from the error when k+1 sheet is not present in the workbook. Adding a new worksheet makes my work easy as I can run a macro later to perform same action on all the data.

Code:

Set objExcel = CreateObject("Excel.Application")
objExcel.Visible = True
Set objWorkbook= objExcel.Workbooks.Open("C:\Documents and Settings\Harp\Desktop\Data\ForumExcel.xls")
Set objSheet= objWorkbook.sheets.add

then rename the sheet to the co_id so that i know which sheet has which company's data and can be easily put into a SPSS file without any error.

This was a huge data collection task and I am thankful to you guys for helping me achieve my goal.

Thanks again.

---

Solved. This thread stands solved.