Combining or Appending to Arrays

[GMK]

Reproducer:

expandcollapse popup

#include <ExcelCOM_UDF.au3>
#include <IE.au3>

_IECreate($oIE, "http://www.imdb.com/title/tt0458259/tvschedule") ;Go to Dirty Jobs schedule
$oDiv = _IEGetObjById($oIE, "tn15content") ;Get DIV
$oTable = _IETagNameGetCollection($oDiv, "table", 1) ;Get second table within DIV
$aDirtyJobs = _IETableWriteToArray($oTable) ;Write table to array

_IENavigate($oIE, "http://www.imdb.com/title/tt0383126/tvschedule") ;Go to MythBusters schedule
$oDiv = _IEGetObjById($oIE, "tn15content") ;Get DIV
$oTable = _IETagNameGetCollection($oDiv, "table", 1) ;Get second table within DIV
$aMythBusters = _IETableWriteToArray($oTable) ;Write table to array
_IEQuit($oIE) ;Quit IE

$oExcel = _ExcelBookOpen (@MyDocumentsDir & "\TV_Shows.xls") ;Open Excel workbook
$oExcel.Sheets ("Upcoming").Select ;Select 
$Row = $oExcel.ActiveSheet.UsedRange.Rows.Count

If UBound($aDirtyJobs, 0) <> 0 Then ;Perform the following actions if data is found
   For $i = 0 To UBound($aDirtyJobs, 1) - 1
      For $j = 1 To UBound($aDirtyJobs, 2) - 1
         ;Some code to format the data omitted
         _ExcelWriteCell ($oExcel, "Dirty Jobs", $j + $Row, 1) ;Insert show title in Excel
         _ExcelWriteCell ($oExcel, $aDirtyJobs[$i][$j], $j + $Row, $i + 2) ;Insert data in Excel
      Next
   Next
   $Row = $oExcel.ActiveSheet.UsedRange.Rows.Count + 1
EndIf

If UBound($aMythBusters, 0) <> 0 Then ;Perform the following actions if data is found
   For $i = 0 To UBound($aMythBusters, 1) - 1
      For $j = 1 To UBound($aMythBusters, 2) - 1
         ;Some code to format the data omitted
         _ExcelWriteCell ($oExcel, "MythBusters", $j + $Row, 1) ;Insert show title in Excel
         _ExcelWriteCell ($oExcel, $aMythBusters[$i][$j], $j + $Row, $i + 2) ;Insert data in Excel
      Next
   Next
   $Row = $oExcel.ActiveSheet.UsedRange.Rows.Count + 1
EndIf
;More code to format data in Excel omitted

So my question is: how can I shorten this? Is there a way to combine $aDirtyJobs and $aMythBusters into one array?

Edited August 7, 2007 by GMK

[Leighwyn]

The way I thought of is to redim your array so it has enough room for the second one, then iterate through to add values one by one into the now-enlarged first array.

But looking at the code, it seems that if all you want to do is shrink your code then the simplest way would be to make your "array -> excel" stuff a subfunction that has the array as an input. You could make the show's name an input as well if you didn't want to construct it from the variable name. This way, you'd remove the duplicate code without combining the arrays.

Here's the quick and dirty way of doing the former:

Dim $x[4] = [0, 1, 2, 3]
Dim $y[2] = [4, 5]
$sizex = ubound($x)
$sizey = ubound($y)
Redim $x[$sizex+$sizey]
for $i = $sizex to Ubound($x)-1
    $x[$i] = $y[$i-$sizex]
Next
;$x=[0,1,2,3,4,5]

Edited August 8, 2007 by Leighwyn

[randallc]

Hi,

you can also just paste the array directly with no need to loop through it?

Best, randall

[GMK]

But looking at the code, it seems that if all you want to do is shrink your code then the simplest way would be to make your "array -> excel" stuff a subfunction that has the array as an input. You could make the show's name an input as well if you didn't want to construct it from the variable name. This way, you'd remove the duplicate code without combining the arrays.

I'm not quite sure what you mean here. Do you mean providing the array name via an InputBox? Or do you think I could somehow use an INI file? (Can I put an array variable name in an INI file?)

Hi,

you can also just paste the array directly with no need to loop through it?

Best, randall

That's a good idea, except that part of the reason I'm looping through it is to run some StringRegExp and StringReplace commands before inserting it into Excel. I've found it easier to manipulate the data in the array, rather than manipulating it in Excel. But thanks anyway.

[GMK]

OK, I managed to put together a UDF for appending 2-dimensional arrays:

;===============================================================================
;
; Function Name:  _2DArrayAppend()
; Description:    Append one 2-dimensional array to the end of another
; Author(s):      Greg Koelln <gkoelln7 at gmail dot com>
;
;===============================================================================
Func _2DArrayAppend ($aDestArray, $aSrcArray)
    If IsArray($aDestArray) And IsArray($aSrcArray) Then
        $OldD2 = UBound($aDestArray, 2) ;Get UBound of second dimension
        If UBound($aDestArray, 1) < UBound($aSrcArray, 1) Then
            ReDim $aDestArray[UBound($aSrcArray, 1)][$OldD2 + UBound($aSrcArray, 2)]
        Else
            ReDim $aDestArray[UBound($aDestArray, 1)][$OldD2 + UBound($aSrcArray, 2)]
        EndIf
        For $D1 = 0 To UBound($aSrcArray, 1) - 1
            For $D2 = 0 To UBound($aSrcArray, 2) - 1
                $aDestArray[$D1][$OldD2 + $D2] = $aSrcArray[$D1][$D2]
            Next
        Next
        SetError(0)
        Return $aDestArray
    Else
        Select
            Case Not IsArray($aDestArray)
                SetError(1)
                Return ""
            Case Not IsArray($aSrcArray)
                SetError(2)
                Return ""
        EndSelect
    EndIf
EndFunc

Edited August 8, 2007 by GMK