Posts: 6
Threads: 2
Joined: Oct 2010
Reputation:
0
10-07-2010, 09:49 AM
(This post was last modified: 10-07-2010, 10:03 AM by 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
Posts: 99
Threads: 0
Joined: Oct 2010
Reputation:
0
10-07-2010, 10:10 AM
(This post was last modified: 10-07-2010, 10:23 AM by 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.
Posts: 6
Threads: 2
Joined: Oct 2010
Reputation:
0
10-07-2010, 10:35 AM
(This post was last modified: 10-07-2010, 10:37 AM by harp.)
Posts: 99
Threads: 0
Joined: Oct 2010
Reputation:
0
10-07-2010, 11:40 AM
(This post was last modified: 10-07-2010, 12:23 PM by 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
Posts: 6
Threads: 2
Joined: Oct 2010
Reputation:
0
10-07-2010, 07:50 PM
(This post was last modified: 10-07-2010, 08:06 PM by harp.)
Posts: 99
Threads: 0
Joined: Oct 2010
Reputation:
0
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.
Posts: 87
Threads: 8
Joined: Sep 2010
Reputation:
0
10-08-2010, 11:29 AM
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....
Posts: 6
Threads: 2
Joined: Oct 2010
Reputation:
0
10-08-2010, 06:49 PM
(This post was last modified: 10-08-2010, 06:58 PM by 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?
Posts: 87
Threads: 8
Joined: Sep 2010
Reputation:
0
10-08-2010, 09:05 PM
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.
Posts: 6
Threads: 2
Joined: Oct 2010
Reputation:
0
10-08-2010, 09:17 PM
(This post was last modified: 10-08-2010, 09:19 PM by 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.
|
