Help appending to Excel file

[Jefferds44]

Hi guys,

My goal is to collect data from a series of webpages and write that date to a single Excel worksheet.

I spent the morning trying to get this to work but I'm stuck on where to go.

I'm using _IETableWriteToArray to get data from a website into an array. I managed to write the table to Excel using _ExcelWriteSheetFromArray. Now where I'm stuck is I can't figure out how to append the Excel sheet once I fill the array with new data.

Either I can append the Array before writing to Excel or append the Excel file as I go. Which strategy is best? And if so, could I get a quick sample on how to implement this functionality?

Thanks!

Jeff

EDIT: Here's my non-working code so far:

#include <IE.au3>
#include <Excel.au3>
#include <Array.au3>
Global $ESRB = "http://www.esrb.org/ratings/search.jsp?titleOrPublisher=&rating=&ratingsCriteria=&platforms=&platformsCriteria=&searchVersion=compact&content=&searchType=title&contentCriteria=&newSearch.x=33&newSearch.y=11"
Global $DataFile = "C:TempDataFile.xls"
Global $aGlobalData
; Load ESRB webpage
$oIE = _IECreate($ESRB, 1)
Do
   $oTable = _IETableGetCollection($oIE, 2) ; Get Table from webpage
   $aTableData = _IETableWriteToArray($oTable,1) ;Write table to array
;~    _ArrayDisplay($aTableData)
  
   _ArrayAdd($aGlobalData, $aTableData) ; Add more data to array
   _ArrayDisplay($aTableData)
  $o_nextPage = _IEGetObjByName($oIE, "nextPage") ;Get nextPage Object
  $p_nextPageDisabled = _IEPropertyGet($o_nextPage, "isdisabled")
 
   If $p_nextPageDisabled == False Then
   _IEAction($o_nextPage, "click")
   EndIf
  
Until $p_nextPageDisabled == True

Edited February 27, 2012 by Jefferds44

[Jfish]

You could keep track of the last cell number in your array with ubound($yourArray) and then start the write next cycle at that location + 1 ...

[Juvigy]

I would suggst to collect your data to lets say 10 arrays from 10 html pages. Then Combine the arrays into 1 big array (_ArrayConcatenate) and then write that to excel.

[GMK]

_ArrayAdd will not work in this case. It will only work with one-dimensional arrays, as will _ArrayConcatenate. You'll have to ReDim your $aGlobalData as necessary and populate $aGlobalData from $aTableData. Also, you may want to use _ArrayDelete to delete rows that contain unwanted data. I've found that it's a lot quicker to manipulate, sort, and delete data from an array than it is in Excel.

[Juvigy]

"I've found that it's a lot quicker to manipulate, sort, and delete data from an array than it is in Excel."

This is not true. Try simple "transpose" with arrays and with excel and then compare the times.

[GMK]

OK...maybe not literally quicker, but quicker to use the solution as opposed to finding which sort syntax to use with which version of Excel. I often write scripts to use both at home and at work, which can cause problems when using different versions of Office. (At work, I have Office 2010 and at home, I have Office 2000.) That's the only reason I avoid manipulating, sorting, and deleting data in Excel using AutoIT.

[GMK]

Anyway, here's my take on it:

#include <Array.au3>
#include <IE.au3>
Global $sESRB = "http://www.esrb.org/ratings/search.jsp?titleOrPublisher=&rating=&ratingsCriteria=&platforms=&platformsCriteria=&searchVersion=compact&content=&searchType=title&contentCriteria=&newSearch.x=33&newSearch.y=11"
Global $aGlobalData[1][5]
$oIE = _IECreate($sESRB, 1) ; Load ESRB webpage
Do
    $oTable = _IETableGetCollection($oIE, 2) ; Get Table from webpage
    $aTableData = _IETableWriteToArray($oTable, 1) ;Write table to array
    _ArrayDelete($aTableData, 0) ;Delete first row of 0's
    If $aGlobalData[0][0] <> "" Then _ArrayDelete($aTableData, 0) ;Delete header row if $aGlobalData has been populated
    _ArrayDisplay($aTableData, "$aTableData")
    If $aGlobalData[0][0] <> "" Then
        $OldD1 = UBound($aGlobalData) - 1 ; Find last used row of $aGlobalData
        ReDim $aGlobalData[$OldD1 + UBound($aTableData) + 1][5] ; Add number of rows found in $aTableData to $aGlobalData
        For $iRow = 0 To UBound($aTableData) - 1 ; Loop through rows
            For $iCol = 0 To UBound($aTableData, 2) - 1 ; Loop through columns
                $aGlobalData[$OldD1 + $iRow + 1][$iCol] = $aTableData[$iRow][$iCol] ; Append data from $aTableData to $aGlobalData
            Next
        Next
    Else
        $aGlobalData = $aTableData
    EndIf
    _ArrayDisplay($aGlobalData, "$aGlobalData")
    $oNextPage = _IEGetObjByName($oIE, "nextPage") ;Get nextPage Object
    $bNextPageDisabled = _IEPropertyGet($oNextPage, "isdisabled")
    If Not $bNextPageDisabled Then
        _IEAction($oNextPage, "click")
        _IELoadWait($oIE) ;Added to wait for page to load
    EndIf
Until $bNextPageDisabled
_ArrayDisplay($aGlobalData, "$aGlobalData")

EDIT: I don't know why I didn't think of just setting $aGlobalData to equal $aTableData at first.

Edited February 29, 2012 by GMK