Jump to content

table data to Excel


 Share

Recommended Posts

I am trying to read 2 parts of tables from a IE page to excel, but failing to read the second table (table data from second frame)

Reads the first part of the table

any help ?

#include <IE.au3>

#include <Array.au3>

#include <Date.au3>

$file_for_edit = FileOpenDialog("Choose file to add data from the report", @DesktopDir & "", "Excel (*.xls)", 1 + 4 )

if (StringInStr($file_for_edit, "Test") <> 0) AND FileExists($file_for_edit) Then

; MsgBox(0," Filename Gotten",$file_for_edit)

$default_date = _DateToMonth(@MON,1) & " " & @MDAY & "/" & StringTrimLeft(@YEAR,2)

$Date_for_edit = ($default_date); InputBox("Enter date for the report","Enter date in format (e.g Dec 07/07)",($default_date))

ShellExecute(($file_for_edit),"","","open",@SW_MAXIMIZE)

$file_title = WinGetTitle("Microsoft Excel", "")

Else

MsgBox(16,"Stop Program Run", "Wrong file chosen!!!")

Exit

EndIf

Local $oIE = _IECreate ("http:// ",0,1,1,1)

if @error = 0 Then

$oTable = _IETableGetCollection ($oIE,5)

$aTableData = _IETableWriteToArray ($oTable, True)

msgbox(0,"tables are ",UBound($aTableData))

WinActivate($file_title)

Send("!EG" & "A1" & "{Enter}")

$records_to_insert = UBound($aTableData) -1

If $records_to_insert > 0 Then

$current_record =0

While ($current_record <= $records_to_insert)

Send(StringStripWS($aTableData[$current_record][1],3) & "{Right}"& StringStripWS($aTableData[$current_record][2],3) &"{Right}" & StringStripWS($aTableData[$current_record][3],3) & "{ENTER}{left}{left}")

$current_record = $current_record + 1

WEnd

Else

MsgBox(0,"No SMS Record Retrieved!!!","Please review report manually")

EndIf

_IEQuit ($oIE)

Else

MsgBox(0,"Error on opening SMS report","Contact Administrator")

Exit

EndIf

Table look like (both are on the same webpage)

Table 1

Resource Number of Resources Percentage of Resources

Accepted 5382 97.3

No Status 52 0.9

Rejected 98 1.8

Table 2

Status of Targeted Resources Number of Resources Percentage of Resources

Accepted - No Further Status 35 0.6

Failed 8 0.1

Succeeded 5038 98.5

Link to comment
Share on other sites

There is no reference in your code to frames, or re-navigating IE to anther page. Where is "...the second table (table data from second frame)" supposed to be?

:D

Valuater's AutoIt 1-2-3, Class... Is now in Session!For those who want somebody to write the script for them: RentACoder"Any technology distinguishable from magic is insufficiently advanced." -- Geek's corollary to Clarke's law
Link to comment
Share on other sites

There is no reference in your code to frames, or re-navigating IE to anther page. Where is "...the second table (table data from second frame)" supposed to be?

Posted Image

This is a SMS Adv. status report showing in two sectoins. Find the attached image.post-55413-12635816278825_thumb.jpg

Edited by Begginer
Link to comment
Share on other sites

Did you check the DOM with DebugBar, or some other DOM inspector? That may not be two tables, just an empty row between two groups of data. There may or may not be "frames" involved, etc.

:D

Valuater's AutoIt 1-2-3, Class... Is now in Session!For those who want somebody to write the script for them: RentACoder"Any technology distinguishable from magic is insufficiently advanced." -- Geek's corollary to Clarke's law
Link to comment
Share on other sites

Did you look at the page with IE using the DebugBar addon, as suggested? The code you posted reads the sixth table on the page (zero-based index = 5). How did you know that was the sixth table? Do you know what instance the other table is?

:D

Valuater's AutoIt 1-2-3, Class... Is now in Session!For those who want somebody to write the script for them: RentACoder"Any technology distinguishable from magic is insufficiently advanced." -- Geek's corollary to Clarke's law
Link to comment
Share on other sites

Please find the below pasted source code for the page , I am not allowed to install the debugger tool....

<BASE target="_self">

</HEAD>

<BODY style="margin: 0px" tabindex="-1" language=javascript onload="return window_onload()" onhelp="return window_onhelp()" onresize="return window_onresize()">

<DIV id=Header style="display: block">

<TABLE class=MenuBar width=100% cellpadding=0 cellspacing=0 border=0>

<TR>

<TD NOWRAP> ,............. </TD>

<TD nowrap align=right>

<A href=".................</A>

</TD>

</TR>

</TABLE>

</DIV>

<DIV class=PageHeader style="display: block">

<TABLE class=ReportProperties border=0 cellspacing=1 cellpadding=0>

<COL>

<COL style="padding-left: 10px">

<TR class=ReportCategoryName>

<TD nowrap>Report Name:</TD>

<TD>Status of a specific advertisement</TD>

</TR>

<TR class=ReportCategoryName>

<TD nowrap>Category:</TD>

<TD>Software Distribution - Advertisement Status</TD>

</TR>

<TR>

<TD nowrap>

Comment:

</TD>

<TD>This report shows the status summary of all resources that have been targeted by an advertisement..........</TD>

</TR>

<TR>

<TD nowrap>

Parameters:

</TD>

<TD>

<TABLE class=Table border=1 cellpadding=1 cellspacing=0 style="margin-top: 5 px; border-collapse: collapse" bordercolor=#4FAA63>

<COL class=RowDark style="padding-left: 5 px; padding-right: 5 px">

<COL class=RowLight style="padding-left: 5 px; padding-right: 5 px">

<TR>

<TD>Advertisement ID</TD>

<TD>ABC2008B</TD>

</TR>

</TABLE>

</TD>

</TR>

</TABLE>

</DIV>

<DIV id=divDashboardHeader style="display: none">

<TABLE class=Table width=100% cellpadding=2 cellspacing=0 border=1 style="border-collapse: collapse; border-bottom-style: none">

<TR class=TableHeader>

<TD>

<A href="Report.asp?ReportID=111" target="_blank"><IMG class=Image border=0 src="images/icolink3.gif" alt="Runs and displays this report in a new window." width=11 height=11></A>

<SPAN class=SimpleTextSmallBold style="margin-left: 10px">Status of a specific advertisement</SPAN>

</TD>

</TR>

</TABLE>

</DIV>

<FORM name=frmReport method="post" action="report.asp" style="margin-top: 0px; margin-bottom: 0px" tabindex=-1>

<DIV style="padding-top: 8px"></DIV>

<DIV class=SimpleTextSmallBold id=Explain1 style="padding-left: 16px; display: block"><p>

</DIV>

<DIV id=divSecondHeader style="display: block">

<TABLE class=Table cellpadding=2 cellspacing=0 width=100% style="border-collapse: collapse; border-bottom-style: none">

<TR class=TableHeader>

<TD align=left style="border-right-style: none">

1/19/2010 7:12:22 PM

</TD>

<TD align=right style="border-left-style: none">

&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;

(Number of Records: 3)

</TD>

</TR>

</TABLE>

</DIV>

<DIV id=ReportData1 name=ReportData>

<TABLE class=Table rules=cols border=1 cellpadding=2 cellspacing=0 width=100% style="border-collapse: collapse" bordercolor=#4FAA63>

<!-- column header -->

<TR class=TableHeader style="background-color: white">

<TD width="1%" style="border-bottom: solid 1px"></TD>

<TH style="border-bottom: solid 1px">

<DIV onmouseover="mouseHover('over')" onmouseout="mouseHover('out')">

<A href="/SMSReporting_ABC/Report.asp?ReportID=111&AdvertID=ABC2008B&SortRs1Col=1&SortRs1Dir=1" style="text-decoration:none"><SPAN class=SimpleTextSmallBold>

&nbsp;Resource Receipt Status&nbsp;</SPAN></A></DIV>

</TH>

<TH style="border-bottom: solid 1px">

<DIV onmouseover="mouseHover('over')" onmouseout="mouseHover('out')">

<A href="/SMSReporting_ABC/Report.asp?ReportID=111&AdvertID=ABC2008B&SortRs1Col=2&SortRs1Dir=1" style="text-decoration:none"><SPAN class=SimpleTextSmallBold>

&nbsp;Number of Resources&nbsp;</SPAN></A></DIV>

</TH>

<TH style="border-bottom: solid 1px">

<DIV onmouseover="mouseHover('over')" onmouseout="mouseHover('out')">

<A href="/SMSReporting_ABC/Report.asp?ReportID=111&AdvertID=ABC2008B&SortRs1Col=3&SortRs1Dir=1" style="text-decoration:none"><SPAN class=SimpleTextSmallBold>

&nbsp;Percentage of Resources&nbsp;</SPAN></A></DIV>

</TH>

<TH style="border-bottom: solid 1px">

<DIV onmouseover="mouseHover('over')" onmouseout="mouseHover('out')">

<A href="/SMSReporting_ABC/Report.asp?ReportID=111&AdvertID=ABC2008B&SortRs1Col=4&SortRs1Dir=1" style="text-decoration:none"><SPAN class=SimpleTextSmallBold>

&nbsp;Advertisement Id&nbsp;</SPAN></A></DIV>

</TH>

</TR>

<!-- data -->

<TR class="RowDark">

<TD width=0><A href="Report.asp?ReportID=110&amp;StateName=Accepted&amp;AdvertID=ABC2008B"><IMG border=0 src="images/icolink3.gif" alt="Open the target" width=11 height=11></A></TD>

<TD class=SimpleTextSmall>&nbsp;Accepted&nbsp;</TD>

<TD class=SimpleTextSmall>&nbsp;643&nbsp;</TD>

<TD class=SimpleTextSmall>&nbsp;97.9&nbsp;</TD>

<TD class=SimpleTextSmall>&nbsp;ABC2008B&nbsp;</TD>

</TR>

<TR class="RowLight">

<TD width=0><A href="Report.asp?ReportID=110&amp;StateName=No+Status&amp;AdvertID=ABC2008B"><IMG border=0 src="images/icolink3.gif" alt="Open the target" width=11 height=11></A></TD>

<TD class=SimpleTextSmall>&nbsp;No&nbspStatus&nbsp;</TD>

<TD class=SimpleTextSmall>&nbsp;1&nbsp;</TD>

<TD class=SimpleTextSmall>&nbsp;0.2&nbsp;</TD>

<TD class=SimpleTextSmall>&nbsp;ABC2008B&nbsp;</TD>

</TR>

<TR class="RowDark">

<TD width=0><A href="Report.asp?ReportID=110&amp;StateName=Rejected&amp;AdvertID=ABC2008B"><IMG border=0 src="images/icolink3.gif" alt="Open the target" width=11 height=11></A></TD>

<TD class=SimpleTextSmall>&nbsp;Rejected&nbsp;</TD>

<TD class=SimpleTextSmall>&nbsp;13&nbsp;</TD>

<TD class=SimpleTextSmall>&nbsp;2&nbsp;</TD>

<TD class=SimpleTextSmall>&nbsp;ABC2008B&nbsp;</TD>

</TR>

</TABLE>

</DIV>

<DIV style="padding-top: 8px"></DIV>

<DIV class=SimpleTextSmallBold id=Explain2 style="padding-left: 16px; display: block"><p>

</DIV>

<DIV id=divSecondHeader style="display: block">

<TABLE class=Table cellpadding=2 cellspacing=0 width=100% style="border-collapse: collapse; border-bottom-style: none">

<TR class=TableHeader>

<TD align=left style="border-right-style: none">

1/19/2010 7:12:23 PM

</TD>

<TD align=right style="border-left-style: none">

&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;

(Number of Records: 3)

</TD>

</TR>

</TABLE>

</DIV>

<DIV id=ReportData2 name=ReportData>

<TABLE class=Table rules=cols border=1 cellpadding=2 cellspacing=0 width=100% style="border-collapse: collapse" bordercolor=#4FAA63>

<!-- column header -->

<TR class=TableHeader style="background-color: white">

<TD width="1%" style="border-bottom: solid 1px"></TD>

<TH style="border-bottom: solid 1px">

<DIV onmouseover="mouseHover('over')" onmouseout="mouseHover('out')">

<A href="/SMSReporting_ABC/Report.asp?ReportID=111&AdvertID=ABC2008B&SortRs2Col=1&SortRs2Dir=1" style="text-decoration:none"><SPAN class=SimpleTextSmallBold>

&nbsp;Status of Targeted Resources&nbsp;</SPAN></A></DIV>

</TH>

<TH style="border-bottom: solid 1px">

<DIV onmouseover="mouseHover('over')" onmouseout="mouseHover('out')">

<A href="/SMSReporting_ABC/Report.asp?ReportID=111&AdvertID=ABC2008B&SortRs2Col=2&SortRs2Dir=1" style="text-decoration:none"><SPAN class=SimpleTextSmallBold>

&nbsp;Number of Resources&nbsp;</SPAN></A></DIV>

</TH>

<TH style="border-bottom: solid 1px">

<DIV onmouseover="mouseHover('over')" onmouseout="mouseHover('out')">

<A href="/SMSReporting_ABC/Report.asp?ReportID=111&AdvertID=ABC2008B&SortRs2Col=3&SortRs2Dir=1" style="text-decoration:none"><SPAN class=SimpleTextSmallBold>

&nbsp;Percentage of Resources&nbsp;</SPAN></A></DIV>

</TH>

<TH style="border-bottom: solid 1px">

<DIV onmouseover="mouseHover('over')" onmouseout="mouseHover('out')">

<A href="/SMSReporting_ABC/Report.asp?ReportID=111&AdvertID=ABC2008B&SortRs2Col=4&SortRs2Dir=1" style="text-decoration:none"><SPAN class=SimpleTextSmallBold>

&nbsp;Advertisement Id&nbsp;</SPAN></A></DIV>

</TH>

</TR>

<!-- data -->

<TR class="RowDark">

<TD width=0><A href="Report.asp?ReportID=110&amp;StateName=Failed&amp;AdvertID=ABC2008B"><IMG border=0 src="images/icolink3.gif" alt="Open the target" width=11 height=11></A></TD>

<TD class=SimpleTextSmall>&nbsp;Failed&nbsp;</TD>

<TD class=SimpleTextSmall>&nbsp;12&nbsp;</TD>

<TD class=SimpleTextSmall>&nbsp;1.9&nbsp;</TD>

<TD class=SimpleTextSmall>&nbsp;ABC2008B&nbsp;</TD>

</TR>

<TR class="RowLight">

<TD width=0><A href="Report.asp?ReportID=110&amp;StateName=Running&amp;AdvertID=ABC2008B"><IMG border=0 src="images/icolink3.gif" alt="Open the target" width=11 height=11></A></TD>

<TD class=SimpleTextSmall>&nbsp;Running&nbsp;</TD>

<TD class=SimpleTextSmall>&nbsp;3&nbsp;</TD>

<TD class=SimpleTextSmall>&nbsp;0.5&nbsp;</TD>

<TD class=SimpleTextSmall>&nbsp;ABC2008B&nbsp;</TD>

</TR>

<TR class="RowDark">

<TD width=0><A href="Report.asp?ReportID=110&amp;StateName=Succeeded&amp;AdvertID=ABC2008B"><IMG border=0 src="images/icolink3.gif" alt="Open the target" width=11 height=11></A></TD>

<TD class=SimpleTextSmall>&nbsp;Succeeded&nbsp;</TD>

<TD class=SimpleTextSmall>&nbsp;629&nbsp;</TD>

<TD class=SimpleTextSmall>&nbsp;97.7&nbsp;</TD>

<TD class=SimpleTextSmall>&nbsp;ABC2008B&nbsp;</TD>

</TR>

</TABLE>

</DIV>

<INPUT type="hidden" name="Export">

<!-- export the data to a file -->

</FORM>

</BODY>

</HTML>

Edited by Begginer
Link to comment
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
 Share

  • Recently Browsing   0 members

    • No registered users viewing this page.
×
×
  • Create New...