Copy arrays data to Excel

[Duff360]

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

[water]

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?

[Duff360]

In the same sheet!

Thank you very much for your help btw

Edited December 3, 2016 by Duff360

[water]

#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 December 3, 2016 by water

[Duff360]

Works perfectly! You're awesome 

[water]

Thanks