Jump to content
Sign in to follow this  
Jefferds44

Help appending to Excel file

Recommended Posts

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 by Jefferds44

Share this post


Link to post
Share on other sites
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.

Share this post


Link to post
Share on other sites
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.

Share this post


Link to post
Share on other sites
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.

Share this post


Link to post
Share on other sites
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.

Share this post


Link to post
Share on other sites
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 by GMK

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
Sign in to follow this  

×