What is Autoit equivalent for VBA Collection object?

[Spec86]

expandcollapse popup

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

opt("WinTitleMatchMode", 2)
$path = "C:\Users\User\Desktop\Nezavisimai\Papka2\"
$FileListShort = _FileListToArray($path, "*.xlsx", 1, True)
$ProgExcel = _Excel_Open()
$BookDestiny = _Excel_BookNew($ProgExcel)

Global $arr = []

$LineDestiny = 1
_Excel_RangeWrite($BookDestiny, $BookDestiny.Activesheet, "Weather", "A" & $LineDestiny)
_Excel_RangeWrite($BookDestiny, $BookDestiny.Activesheet, "Temp", "B" & $LineDestiny)

For $i = 1 to $FileListShort[0]

   $BookSource =  _Excel_BookOpen($ProgExcel, $FileListShort[$i], False, False)
   $aArray = _Excel_RangeRead($BookSource, $BookSource.Activesheet, "A2:B4")

   _ArrayAdd($arr, $aArray)

   _Excel_BookClose($BookSource, False)
Next


   Global $numRows = UBound($arr[0], 1)
   Global $numCols = UBound($arr[0], 2)

   Global $fArray = []
   ReDim $fArray [0 To ($numRows * (Ubound($arr) - 1))] [0 to $numCols]

   rT = 0
   For $p = 0 To UBound($arr) - 1
      For r = 0 To $numRows
         rT = rT + 1
         For c = 0 To $numCols
            $fArray[rT][c] = $p[r][c]
         Next c
      Next r
   Next $p

   _Excel_RangeWrite($BookDestiny, $BookDestiny.Activesheet, $fArray, "A2")

Hello, what is Autoit equivalent for VBA Collection object?

Dim CollArrays As New Collection 'VBA Collection

How can create collection of 2D arrays with Autoit?

This code write error.

[ajag]

 ReDim $fArray [0 To ($numRows * (Ubound($arr) - 1))] [0 to $numCols]

try this:

ReDim $fArray[($numRows * (Ubound($arr) - 1))][$numCols]

 

A-Jay

[Spec86]

44 minutes ago, ajag said:

 ReDim $fArray [0 To ($numRows * (Ubound($arr) - 1))] [0 to $numCols]

try this:

ReDim $fArray[($numRows * (Ubound($arr) - 1))][$numCols]

 

A-Jay

Write Error.

[water]

What do you mean by "Write Error"? Can you please post the error message you get?

[water]

BTW: You try to use a lot of "variables" without the leading $; rt, r, c.
"Next $p" is not a valid statement. Should be "Next"

[Spec86]

19 minutes ago, water said:

BTW: You try to use a lot of "variables" without the leading $; rt, r, c.
"Next $p" is not a valid statement. Should be "Next"

$rT = 0
   For $p = 0 To (UBound($arr) - 1)
      For $r = 0 To $numRows
         $rT = $rT + 1
         For $c = 0 To $numCols
            $fArray[$rT][$c] = $p[$r][$c]
         Next
      Next
   Next

Error.

Line 4397 (File"C:\Users\Users\Desktop\Test2.exe"):

Error: Array variable has incorrect number of subscript or subscript dimension range exceeded.

[Jos]

$p is a number not an Array ...right?

Edited December 22, 2021 by Jos

[TheXman]

3 hours ago, Spec86 said:

How can create collection of 2D arrays with Autoit?

Here's a conceptual example of how to create a collection (array) of 2D arrays:

#include <Array.au3>

array_of_2d_arrays_example()

Func array_of_2d_arrays_example()
    Const $2D_ARRAY_1 = [["A1 Row 0/Col 0", "A1 Row 0/Col 1"], ["A1 Row 1/Col 0", "A1 Row 1/Col 1"]], _
          $2D_ARRAY_2 = [["A2 Row 0/Col 0", "A2 Row 0/Col 1"], ["A2 Row 1/Col 0", "A2 Row 1/Col 1"]]

    Local $aArrayOf2dArrays[0]

    ;Display 2d array 1 & 2
    _ArrayDisplay($2D_ARRAY_1, "$2D_ARRAY_1")
    _ArrayDisplay($2D_ARRAY_2, "$2D_ARRAY_2")

    ;Add 2d array 1 & 2 to the array of 2d arrays
    _ArrayAdd($aArrayOf2dArrays, $2D_ARRAY_1, 0, "", "", $ARRAYFILL_FORCE_SINGLEITEM)
    _ArrayAdd($aArrayOf2dArrays, $2D_ARRAY_2, 0, "", "", $ARRAYFILL_FORCE_SINGLEITEM)

    ;Display array of 2d arrays
    _ArrayDisplay($aArrayOf2dArrays, "$aArrayOf2dArrays")

    ;Display the rows of array of 2d arrays
    _ArrayDisplay($aArrayOf2dArrays[0], "Row 0 of $aArrayOf2dArrays")
    _ArrayDisplay($aArrayOf2dArrays[1], "Row 1 of $aArrayOf2dArrays")
EndFunc

 

[water]

Can you please tell us what you try to do with your script?
Do you need to create the array at all?
 

[Spec86]

24 minutes ago, Jos said:

$p is a number not an Array ...right?

Yes, VBA has "For Each". Autoit has "For". I cannot use "For Each" in Autoit.

Edited December 22, 2021 by Spec86

[Spec86]

5 minutes ago, TheXman said:

Here's a conceptual example of how to create a collection (array) of 2D arrays:

#include <Array.au3>

array_of_2d_arrays_example()

Func array_of_2d_arrays_example()
    Const $2D_ARRAY_1 = [["A1 Row 0/Col 0", "A1 Row 0/Col 1"], ["A1 Row 1/Col 0", "A1 Row 1/Col 1"]], _
          $2D_ARRAY_2 = [["A2 Row 0/Col 0", "A2 Row 0/Col 1"], ["A2 Row 1/Col 0", "A2 Row 1/Col 1"]]

    Local $aArrayOf2dArrays[0]

    ;Display 2d array 1 & 2
    _ArrayDisplay($2D_ARRAY_1, "$2D_ARRAY_1")
    _ArrayDisplay($2D_ARRAY_2, "$2D_ARRAY_2")

    ;Add 2d array 1 & 2 to the array of 2d arrays
    _ArrayAdd($aArrayOf2dArrays, $2D_ARRAY_1, 0, "", "", $ARRAYFILL_FORCE_SINGLEITEM)
    _ArrayAdd($aArrayOf2dArrays, $2D_ARRAY_2, 0, "", "", $ARRAYFILL_FORCE_SINGLEITEM)

    ;Display array of 2d arrays
    _ArrayDisplay($aArrayOf2dArrays, "$aArrayOf2dArrays")

    ;Display the rows of array of 2d arrays
    _ArrayDisplay($aArrayOf2dArrays[0], "Row 0 of $aArrayOf2dArrays")
    _ArrayDisplay($aArrayOf2dArrays[1], "Row 1 of $aArrayOf2dArrays")
EndFunc

 

I'm first time in coding )) I cannot use Functions )))

[Spec86]

16 minutes ago, water said:

Can you please tell us what you try to do with your script?
Do you need to create the array at all?
 

Target: combine 2D arrays. And create final 2D array. insert final 2D array into sheet.  This script combine data from folder. I'm first time in coding. I understand this code. Loop files is work.  Combine 2D arrays is not work.

Edited December 22, 2021 by Spec86

[mLipok]

ah.... here you are...

https://stackoverflow.com/questions/70417461/what-is-autoit-equivalent-for-vba-collection-object

 

[Spec86]

3 minutes ago, mLipok said:

ah.... here you are...

https://stackoverflow.com/questions/70417461/what-is-autoit-equivalent-for-vba-collection-object

 

I am )))

Edited December 22, 2021 by Spec86

[water]

There is no need to create an array.
I suggest something like this:

#include <File.au3>
#include <Excel.au3>

Global $LineDestiny = 1, $oBookDestiny, $aArraySource, $oBookSource
Global $sPath = "H:\Local\TEST" ; "C:\Users\User\Desktop\Nezavisimai\Papka2\"
Global $aFileListShort = _FileListToArray($sPath, "*.xlsx", $FLTA_FILES, True)
Global $oExcel = _Excel_Open()
Global $oBookDestiny = _Excel_BookNew($oExcel)
_Excel_RangeWrite($oBookDestiny, $oBookDestiny.Activesheet, "Weather", "A" & $LineDestiny)
_Excel_RangeWrite($oBookDestiny, $oBookDestiny.Activesheet, "Temp", "B" & $LineDestiny)

For $i = 1 to $aFileListShort[0]
   $oBookSource = _Excel_BookOpen($oExcel, $aFileListShort[$i], False, False)
   $aArraySource = _Excel_RangeRead($oBookSource, $oBookSource.Activesheet, "A2:B4")
   ; Calculate the last used row 
   $iLastUsedRow = $oBookDestiny.Activesheet.UsedRange.SpecialCells($xlCellTypeLastCell).Row
   _Excel_RangeWrite($oBookDestiny, $oBookDestiny.Activesheet, $aArraySource, "A" & $iLastUsedRow+1)
   _Excel_BookClose($oBookSource, False)
Next

 

Edited December 22, 2021 by water

[Spec86]

19 minutes ago, water said:

There is no need to create an array.
I suggest something like this:

#include <File.au3>
#include <Excel.au3>

Global $LineDestiny = 1, $oBookDestiny, $aArraySource, $oBookSource
Global $sPath = "H:\Local\TEST" ; "C:\Users\User\Desktop\Nezavisimai\Papka2\"
Global $aFileListShort = _FileListToArray($sPath, "*.xlsx", $FLTA_FILES, True)
Global $oExcel = _Excel_Open()
Global $oBookDestiny = _Excel_BookNew($oExcel)
_Excel_RangeWrite($oBookDestiny, $oBookDestiny.Activesheet, "Weather", "A" & $LineDestiny)
_Excel_RangeWrite($oBookDestiny, $oBookDestiny.Activesheet, "Temp", "B" & $LineDestiny)

For $i = 1 to $aFileListShort[0]
   $oBookSource = _Excel_BookOpen($oExcel, $aFileListShort[$i], False, False)
   $aArraySource = _Excel_RangeRead($oBookSource, $oBookSource.Activesheet, "A2:B4")
   ; Calculate the last used row 
   $iLastUsedRow = $oBookDestiny.Activesheet.UsedRange.SpecialCells($xlCellTypeLastCell).Row
   _Excel_RangeWrite($oBookDestiny, $oBookDestiny.Activesheet, $aArraySource, "A" & $iLastUsedRow+1)
   _Excel_BookClose($oBookSource, False)
Next

 

Thank you. I still have an interest )))  I would like to have code version with final array ))) How is create final array? How do my first code working?  

[water]

#include <File.au3>
#include <Excel.au3>

Global $sPath = "H:\Local\TEST" ; "C:\Users\User\Desktop\Nezavisimai\Papka2\"
Global $aFileListShort = _FileListToArray($sPath, "*.xlsx", $FLTA_FILES, True)
Global $oExcel = _Excel_Open()
Global $oBookDestiny = _Excel_BookNew($oExcel)
Global $LineDestiny = 1, $oBookDestiny, $aArraySource, $oBookSource, $aArrayOutput[1][2] = [["Weather", "Temp"]]
_Excel_RangeWrite($oBookDestiny, $oBookDestiny.Activesheet, "Weather", "A" & $LineDestiny)
_Excel_RangeWrite($oBookDestiny, $oBookDestiny.Activesheet, "Temp", "B" & $LineDestiny)

For $i = 1 to $aFileListShort[0]
   $oBookSource = _Excel_BookOpen($oExcel, $aFileListShort[$i], False, False)
   $aArraySource = _Excel_RangeRead($oBookSource, $oBookSource.Activesheet, "A2:B4")
   _ArrayConcatenate($aArrayOutput, $aArraySource)
   $iLastUsedRow = $oBookDestiny.Activesheet.UsedRange.SpecialCells($xlCellTypeLastCell).Row
   _Excel_RangeWrite($oBookDestiny, $oBookDestiny.Activesheet, $aArraySource, "A" & $iLastUsedRow+1)
   _Excel_BookClose($oBookSource, False)
Next
_ArrayDisplay($aArrayOutput)

 

[Spec86]

12 minutes ago, water said:

#include <File.au3>
#include <Excel.au3>

Global $sPath = "H:\Local\TEST" ; "C:\Users\User\Desktop\Nezavisimai\Papka2\"
Global $aFileListShort = _FileListToArray($sPath, "*.xlsx", $FLTA_FILES, True)
Global $oExcel = _Excel_Open()
Global $oBookDestiny = _Excel_BookNew($oExcel)
Global $LineDestiny = 1, $oBookDestiny, $aArraySource, $oBookSource, $aArrayOutput[1][2] = [["Weather", "Temp"]]
_Excel_RangeWrite($oBookDestiny, $oBookDestiny.Activesheet, "Weather", "A" & $LineDestiny)
_Excel_RangeWrite($oBookDestiny, $oBookDestiny.Activesheet, "Temp", "B" & $LineDestiny)

For $i = 1 to $aFileListShort[0]
   $oBookSource = _Excel_BookOpen($oExcel, $aFileListShort[$i], False, False)
   $aArraySource = _Excel_RangeRead($oBookSource, $oBookSource.Activesheet, "A2:B4")
   _ArrayConcatenate($aArrayOutput, $aArraySource)
   $iLastUsedRow = $oBookDestiny.Activesheet.UsedRange.SpecialCells($xlCellTypeLastCell).Row
   _Excel_RangeWrite($oBookDestiny, $oBookDestiny.Activesheet, $aArraySource, "A" & $iLastUsedRow+1)
   _Excel_BookClose($oBookSource, False)
Next
_ArrayDisplay($aArrayOutput)

 

#include <File.au3>
#include <Excel.au3>

Global $sPath = "C:\Users\User\Desktop\Nezavisimai\Papka2\"
Global $aFileListShort = _FileListToArray($sPath, "*.xlsx", $FLTA_FILES, True)
Global $oExcel = _Excel_Open()
Global $oBookDestiny = _Excel_BookNew($oExcel)
Global $LineDestiny = 1, $oBookDestiny, $aArraySource, $oBookSource, $aArrayOutput[1][2] = [["Weather", "Temp"]]
_Excel_RangeWrite($oBookDestiny, $oBookDestiny.Activesheet, "Weather", "A" & $LineDestiny)
_Excel_RangeWrite($oBookDestiny, $oBookDestiny.Activesheet, "Temp", "B" & $LineDestiny)

For $i = 1 to $aFileListShort[0]
   $oBookSource = _Excel_BookOpen($oExcel, $aFileListShort[$i], False, False)
   $aArraySource = _Excel_RangeRead($oBookSource, $oBookSource.Activesheet, "A2:B4")
   _ArrayConcatenate($aArrayOutput, $aArraySource)
   ;$iLastUsedRow = $oBookDestiny.Activesheet.UsedRange.SpecialCells($xlCellTypeLastCell).Row
   ;_Excel_RangeWrite($oBookDestiny, $oBookDestiny.Activesheet, $aArraySource, "A" & $iLastUsedRow+1)
   _Excel_BookClose($oBookSource, False)
Next
_Excel_RangeWrite($BookDestiny, $BookDestiny.Activesheet, $aArrayOutput, "A2")
_ArrayDisplay($aArrayOutput)

Your code is work. But when insert $aArrayOutput in one iteration all array, write error. I am normal ))) But i would like to insert final array = 1 object.   _Excel_RangeWrite($BookDestiny, $BookDestiny.Activesheet, $aArrayOutput, "A2")

[water]

I have modified the variable names to adhere to the hungary notation.
So

_Excel_RangeWrite($BookDestiny, $BookDestiny.Activesheet, $aArrayOutput, "A2")

should be

_Excel_RangeWrite($oBookDestiny, $oBookDestiny.Activesheet, $aArrayOutput, "A2")

[water]

Once again: If you get an error and ask for help, please post the full error message!