Duff360

Copy arrays data to Excel

6 posts in this topic

Hello guys,

I'm still a beginner with AutoIT and programmation in general.

I'm trying to extract data from a web site (Bank of Canada) AND put it into an Excel file. I made an easy script, but since the web page contains many table, I'm not able to copy ALL the data into Excel. Please find my script below:

#include <IE.au3>
#include <Array.au3>
#include <Excel.au3>

$oIE = _IECreate("http://www.bankofcanada.ca/rates/exchange/monthly-average-lookup/?lookupPage=lookup_monthly_exchange_rates.php&startRange=2006-10-01&endRange=2016-10-01&startYear=2006&currentYear=2016&currentMonth=10&rangeType=dates&dFromMonth=10&dFromYear=2016&dToMonth=10&dToYear=2016&rangeMonthlyValue=1&series%5B%5D=LOOKUPS_IEXM0101&series%5B%5D=LOOKUPS_IEXM1601 &submit_button=Submit", 0, 0)
$oTableCollection = _IETableGetCollection($oIE)
For $oAll In $oTableCollection
    $aTableData = _IETableWriteToArray($oAll, True)
Next
_IEQuit($oIE)

Local $oExcel = _Excel_Open()
Local $oWorkbook = _Excel_BookNew($oExcel)
_Excel_RangeWrite($oWorkbook, $oWorkbook.Activesheet, $aTableData [1][2], "A1")

With this script, only the data from the last array is copied into the Excel file, but I want the data from all the tables. (only 2 in my example, but my real request contains many countries).

Do you have a better solution than that? I tried with BinaryToString and InetRead, but it seems much more complicated.

Thanks you very much for your help! :)

Felix

Share this post


Link to post
Share on other sites



You need to write the data inside the loop:

#include <IE.au3>
#include <Array.au3>
#include <Excel.au3>

Local $oExcel = _Excel_Open()
Local $oWorkbook = _Excel_BookNew($oExcel)
Local $oIE = _IECreate("http://www.bankofcanada.ca/rates/exchange/monthly-average-lookup/?lookupPage=lookup_monthly_exchange_rates.php&startRange=2006-10-01&endRange=2016-10-01&startYear=2006&currentYear=2016&currentMonth=10&rangeType=dates&dFromMonth=10&dFromYear=2016&dToMonth=10&dToYear=2016&rangeMonthlyValue=1&series%5B%5D=LOOKUPS_IEXM0101&series%5B%5D=LOOKUPS_IEXM1601 &submit_button=Submit", 0, 0)
Local $oTableCollection = _IETableGetCollection($oIE)
For $oAll In $oTableCollection
    $aTableData = _IETableWriteToArray($oAll, True)
    _Excel_RangeWrite($oWorkbook, $oWorkbook.Activesheet, $aTableData, "A1") ; <== needs to be modified
Next
_IEQuit($oIE)

The marked line needs to be modified. Do you want to write all tables to one sheet or open a new sheet for each table?


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2017-04-18 - Version 1.4.8.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2017-02-27 - Version 1.3.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2015-04-01 - Version 0.4.0.0) - Download - General Help & Support - Example Scripts
Excel - Example Scripts - Wiki
Word - Wiki
PowerPoint (2015-06-06 - Version 0.0.5.0) - Download - General Help & Support

Tutorials:
ADO - Wiki

 

Share this post


Link to post
Share on other sites

#3 ·  Posted (edited)

In the same sheet!

Thank you very much for your help btw :)

Edited by Duff360

Share this post


Link to post
Share on other sites

#4 ·  Posted (edited)

#include <IE.au3>
#include <Array.au3>
#include <Excel.au3>

Local $oExcel = _Excel_Open()
Local $oWorkbook = _Excel_BookNew($oExcel)
Local $iRow2Write2 = 1
Local $oIE = _IECreate("http://www.bankofcanada.ca/rates/exchange/monthly-average-lookup/?lookupPage=lookup_monthly_exchange_rates.php&startRange=2006-10-01&endRange=2016-10-01&startYear=2006&currentYear=2016&currentMonth=10&rangeType=dates&dFromMonth=10&dFromYear=2016&dToMonth=10&dToYear=2016&rangeMonthlyValue=1&series%5B%5D=LOOKUPS_IEXM0101&series%5B%5D=LOOKUPS_IEXM1601 &submit_button=Submit", 0, 0)
Local $oTableCollection = _IETableGetCollection($oIE)
For $oAll In $oTableCollection
    $aTableData = _IETableWriteToArray($oAll, True)
    _Excel_RangeWrite($oWorkbook, $oWorkbook.Activesheet, $aTableData, "A" & $iRow2Write2)
    $iRow2Write2 = $oWorkBook.Activesheet.UsedRange.Rows.Count + 1
Next
_IEQuit($oIE)

 

Edited by water

My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2017-04-18 - Version 1.4.8.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2017-02-27 - Version 1.3.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2015-04-01 - Version 0.4.0.0) - Download - General Help & Support - Example Scripts
Excel - Example Scripts - Wiki
Word - Wiki
PowerPoint (2015-06-06 - Version 0.0.5.0) - Download - General Help & Support

Tutorials:
ADO - Wiki

 

Share this post


Link to post
Share on other sites

Works perfectly! You're awesome :lmao:

Share this post


Link to post
Share on other sites

Thanks :)


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2017-04-18 - Version 1.4.8.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2017-02-27 - Version 1.3.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2015-04-01 - Version 0.4.0.0) - Download - General Help & Support - Example Scripts
Excel - Example Scripts - Wiki
Word - Wiki
PowerPoint (2015-06-06 - Version 0.0.5.0) - Download - General Help & Support

Tutorials:
ADO - Wiki

 

Share this post


Link to post
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