Multiple Arrays one Loop?

[SkysLastChance]

I have a folder that has 21 different files in a folder all with different names. (used #'s for the example) I am trying to combine these files that are in folder into 4 separate files. It works exactly how I need it to.

However,  I want to be able to loop through all 4 arrays and not just the first one. 

There are other files in the folder then just the ones I need, but the names of the files I do need will not ever change. 

I tried turning the $aFileList1 into a variable and inserting another for loop, however, I found out quickly that was not going to work.

I am guessing I will have to throw them into one array.  I am hoping there is a way to do it with multiple arrays. 

expandcollapse popup

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

;File List One
Global $aFileList1[4] = ["C:\Users\UserName\Desktop\FileName\1.xlsx" _
,"C:\Users\UserName\Desktop\FileName\2.xlsx" _
,"C:\Users\UserName\Desktop\FileName\3.xlsx" _
,"C:\Users\UserName\Desktop\FileName\4.xlsx"]
;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;

;File List Two
Global $aFileList2[3] = ["C:\Users\UserName\Desktop\FileName\5.xlsx" _
,"C:\Users\UserName\Desktop\FileName\6.xlsx" _
,"C:\Users\UserName\Desktop\FileName\7.xlsx"]
;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;

;File List Three
Global $aFileList3[2] = ["C:\Users\UserName\Desktop\FileName\8.xlsx" _
,"C:\Users\UserName\Desktop\FileName\9.xlsx"]
;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;

;File List Four
Global $aFileList4[12] = ["C:\Users\UserName\Desktop\FileName\100.xlsx" _
,"C:\Users\UserName\Desktop\FileName\11.xlsx" _
,"C:\Users\UserName\Desktop\FileName\12.xlsx" _
,"C:\Users\UserName\Desktop\FileName\13.xlsx" _
,"C:\Users\UserName\Desktop\FileName\14.xlsx" _
,"C:\Users\UserName\Desktop\FileName\15.xlsx" _
,"C:\Users\UserName\Desktop\FileName\16.xlsx" _
,"C:\Users\UserName\Desktop\FileName\17.xlsx" _
,"C:\Users\UserName\Desktop\FileName\18.xlsx" _
,"C:\Users\UserName\Desktop\FileName\19.xlsx" _
,"C:\Users\UserName\Desktop\FileName\20.xlsx" _
,"C:\Users\UserName\Desktop\FileName\21.xlsx"]
;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;


Global $aExcelData[0][20]
Global $oExcel = _Excel_Open()
    If @error Then Exit
Global $oDestinationWorkbook = _Excel_BookNew($oExcel)
    $oExcel.ActiveSheet.Name = "Sheet1"
_Excel_SheetAdd($oDestinationWorkbook, -1, False, 1, "Facilites")
$oDestinationWorkbook.Sheets("Sheet1").Activate

Global $DestinationiLastRow = $oDestinationWorkbook.ActiveSheet.UsedRange.Rows.Count
Global $oWorkbook, $aRangeRead




    For $i = 0 To UBound($aFileList1) -1

        $oWorkbook = _Excel_BookOpen($oExcel, $aFileList1[$i], True, True)
            If @error Then
                MsgBox(0,"Error",$aFileList1[$i] & " is missing. Push Okay to Continue")
                ContinueLoop
            EndIf
        Local $iLastRow = $oWorkbook.ActiveSheet.UsedRange.Rows.Count 

        If $i = 0 Then ;This is used to get the header from the first row and not the other sheets after.
            $aCopyPaste = _Excel_RangeCopyPaste($oWorkbook.ActiveSheet, $oWorkbook.ActiveSheet.Usedrange.Columns("A:T"),$oDestinationWorkbook.ActiveSheet.Range("A" & $DestinationiLastRow))

        Else
            $aCopyPaste = _Excel_RangeCopyPaste($oWorkbook.ActiveSheet, $oWorkbook.ActiveSheet.Range("A2:T" & $iLastRow),$oDestinationWorkbook.ActiveSheet.Range("A" & $DestinationiLastRow))

        EndIf

        $oWorkbook.Sheets("Facilites").Activate
        $oDestinationWorkbook.Sheets("Facilites").Activate

        $aCopyPaste = _Excel_RangeCopyPaste($oWorkbook.ActiveSheet, $oWorkbook.ActiveSheet.Range("A1"),$oDestinationWorkbook.ActiveSheet.Range("A" & $i + 1))


        $oDestinationWorkbook.Sheets("Sheet1").Activate

        $DestinationiLastRow = $oDestinationWorkbook.ActiveSheet.UsedRange.Rows.Count + 1
        _Excel_BookClose($oWorkbook, False)

    Next

 

Edited March 11, 2022 by SkysLastChance

[SkysLastChance]

I suppose I could do this too. It just seems like there is a better way.  I would eventually change the excel save into a function. If I had to go this route. 

expandcollapse popup

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

;File List One
Global $aFileList1[4] = ["C:\Users\UserName\Desktop\FileName\1.xlsx" _
,"C:\Users\UserName\Desktop\FileName\2.xlsx" _
,"C:\Users\UserName\Desktop\FileName\3.xlsx" _
,"C:\Users\UserName\Desktop\FileName\4.xlsx"]
;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;

;File List Two
Global $aFileList2[3] = ["C:\Users\UserName\Desktop\FileName\5.xlsx" _
,"C:\Users\UserName\Desktop\FileName\6.xlsx" _
,"C:\Users\UserName\Desktop\FileName\7.xlsx"]
;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;

;File List Three
Global $aFileList3[2] = ["C:\Users\UserName\Desktop\FileName\8.xlsx" _
,"C:\Users\UserName\Desktop\FileName\9.xlsx"]
;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;

;File List Four
Global $aFileList4[12] = ["C:\Users\UserName\Desktop\FileName\100.xlsx" _
,"C:\Users\UserName\Desktop\FileName\11.xlsx" _
,"C:\Users\UserName\Desktop\FileName\12.xlsx" _
,"C:\Users\UserName\Desktop\FileName\13.xlsx" _
,"C:\Users\UserName\Desktop\FileName\14.xlsx" _
,"C:\Users\UserName\Desktop\FileName\15.xlsx" _
,"C:\Users\UserName\Desktop\FileName\16.xlsx" _
,"C:\Users\UserName\Desktop\FileName\17.xlsx" _
,"C:\Users\UserName\Desktop\FileName\18.xlsx" _
,"C:\Users\UserName\Desktop\FileName\19.xlsx" _
,"C:\Users\UserName\Desktop\FileName\20.xlsx" _
,"C:\Users\UserName\Desktop\FileName\21.xlsx"]
;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;


Global $aExcelData[0][20]
Global $oExcel = _Excel_Open()
    If @error Then Exit
Global $oDestinationWorkbook = _Excel_BookNew($oExcel)
    $oExcel.ActiveSheet.Name = "Sheet1"
_Excel_SheetAdd($oDestinationWorkbook, -1, False, 1, "Facilites")
$oDestinationWorkbook.Sheets("Sheet1").Activate

Global $DestinationiLastRow = $oDestinationWorkbook.ActiveSheet.UsedRange.Rows.Count
Global $oWorkbook, $aRangeRead




    For $i = 0 To UBound($aFileList1) -1

        $oWorkbook = _Excel_BookOpen($oExcel, $aFileList1[$i], True, True)
            If @error Then
                MsgBox(0,"Error",$aFileList1[$i] & " is missing. Push Okay to Continue")
                ContinueLoop
            EndIf
        Local $iLastRow = $oWorkbook.ActiveSheet.UsedRange.Rows.Count 

        If $i = 0 Then ;This is used to get the header from the first row and not the other sheets after.
            $aCopyPaste = _Excel_RangeCopyPaste($oWorkbook.ActiveSheet, $oWorkbook.ActiveSheet.Usedrange.Columns("A:T"),$oDestinationWorkbook.ActiveSheet.Range("A" & $DestinationiLastRow))

        Else
            $aCopyPaste = _Excel_RangeCopyPaste($oWorkbook.ActiveSheet, $oWorkbook.ActiveSheet.Range("A2:T" & $iLastRow),$oDestinationWorkbook.ActiveSheet.Range("A" & $DestinationiLastRow))

        EndIf

        $oWorkbook.Sheets("Facilites").Activate
        $oDestinationWorkbook.Sheets("Facilites").Activate

        $aCopyPaste = _Excel_RangeCopyPaste($oWorkbook.ActiveSheet, $oWorkbook.ActiveSheet.Range("A1"),$oDestinationWorkbook.ActiveSheet.Range("A" & $i + 1))


        $oDestinationWorkbook.Sheets("Sheet1").Activate

        $DestinationiLastRow = $oDestinationWorkbook.ActiveSheet.UsedRange.Rows.Count + 1
        _Excel_BookClose($oWorkbook, False)

    Next
    
_Excel_BookSaveAs($oDestinationWorkbook,"C:\Users\username\Desktop\Test\Combined 1")
If @error Then Exit MsgBox($MB_SYSTEMMODAL, "_Excel_BookSaveAs", "Error saving workbook to '" & "'." & @CRLF & "@error = " & @error & ", @extended = " & @extended)
_Excel_Close($oExcel)
    
    
    For $i = 0 To UBound($aFileList2) -1

        $oWorkbook = _Excel_BookOpen($oExcel, $aFileList2[$i], True, True)
            If @error Then
                MsgBox(0,"Error",$aFileList2[$i] & " is missing. Push Okay to Continue")
                ContinueLoop
            EndIf
        Local $iLastRow = $oWorkbook.ActiveSheet.UsedRange.Rows.Count 

        If $i = 0 Then ;This is used to get the header from the first row and not the other sheets after.
            $aCopyPaste = _Excel_RangeCopyPaste($oWorkbook.ActiveSheet, $oWorkbook.ActiveSheet.Usedrange.Columns("A:T"),$oDestinationWorkbook.ActiveSheet.Range("A" & $DestinationiLastRow))

        Else
            $aCopyPaste = _Excel_RangeCopyPaste($oWorkbook.ActiveSheet, $oWorkbook.ActiveSheet.Range("A2:T" & $iLastRow),$oDestinationWorkbook.ActiveSheet.Range("A" & $DestinationiLastRow))

        EndIf

        $oWorkbook.Sheets("Facilites").Activate
        $oDestinationWorkbook.Sheets("Facilites").Activate

        $aCopyPaste = _Excel_RangeCopyPaste($oWorkbook.ActiveSheet, $oWorkbook.ActiveSheet.Range("A1"),$oDestinationWorkbook.ActiveSheet.Range("A" & $i + 1))


        $oDestinationWorkbook.Sheets("Sheet1").Activate

        $DestinationiLastRow = $oDestinationWorkbook.ActiveSheet.UsedRange.Rows.Count + 1
        _Excel_BookClose($oWorkbook, False)

    Next
    
_Excel_BookSaveAs($oDestinationWorkbook,"C:\Users\username\Desktop\Test\Combined 2")
If @error Then Exit MsgBox($MB_SYSTEMMODAL, "_Excel_BookSaveAs", "Error saving workbook to '" & "'." & @CRLF & "@error = " & @error & ", @extended = " & @extended)
_Excel_Close($oExcel)   
    
    For $i = 0 To UBound($aFileList3) -1

        $oWorkbook = _Excel_BookOpen($oExcel, $aFileList3[$i], True, True)
            If @error Then
                MsgBox(0,"Error",$aFileList3[$i] & " is missing. Push Okay to Continue")
                ContinueLoop
            EndIf
        Local $iLastRow = $oWorkbook.ActiveSheet.UsedRange.Rows.Count 

        If $i = 0 Then ;This is used to get the header from the first row and not the other sheets after.
            $aCopyPaste = _Excel_RangeCopyPaste($oWorkbook.ActiveSheet, $oWorkbook.ActiveSheet.Usedrange.Columns("A:T"),$oDestinationWorkbook.ActiveSheet.Range("A" & $DestinationiLastRow))

        Else
            $aCopyPaste = _Excel_RangeCopyPaste($oWorkbook.ActiveSheet, $oWorkbook.ActiveSheet.Range("A2:T" & $iLastRow),$oDestinationWorkbook.ActiveSheet.Range("A" & $DestinationiLastRow))

        EndIf

        $oWorkbook.Sheets("Facilites").Activate
        $oDestinationWorkbook.Sheets("Facilites").Activate

        $aCopyPaste = _Excel_RangeCopyPaste($oWorkbook.ActiveSheet, $oWorkbook.ActiveSheet.Range("A1"),$oDestinationWorkbook.ActiveSheet.Range("A" & $i + 1))


        $oDestinationWorkbook.Sheets("Sheet1").Activate

        $DestinationiLastRow = $oDestinationWorkbook.ActiveSheet.UsedRange.Rows.Count + 1
        _Excel_BookClose($oWorkbook, False)

    Next
    
_Excel_BookSaveAs($oDestinationWorkbook,"C:\Users\username\Desktop\Test\Combined 3")
If @error Then Exit MsgBox($MB_SYSTEMMODAL, "_Excel_BookSaveAs", "Error saving workbook to '" & "'." & @CRLF & "@error = " & @error & ", @extended = " & @extended)
_Excel_Close($oExcel)
    
    For $i = 0 To UBound($aFileList4) -1

        $oWorkbook = _Excel_BookOpen($oExcel, $aFileList4[$i], True, True)
            If @error Then
                MsgBox(0,"Error",$aFileList4[$i] & " is missing. Push Okay to Continue")
                ContinueLoop
            EndIf
        Local $iLastRow = $oWorkbook.ActiveSheet.UsedRange.Rows.Count 

        If $i = 0 Then ;This is used to get the header from the first row and not the other sheets after.
            $aCopyPaste = _Excel_RangeCopyPaste($oWorkbook.ActiveSheet, $oWorkbook.ActiveSheet.Usedrange.Columns("A:T"),$oDestinationWorkbook.ActiveSheet.Range("A" & $DestinationiLastRow))

        Else
            $aCopyPaste = _Excel_RangeCopyPaste($oWorkbook.ActiveSheet, $oWorkbook.ActiveSheet.Range("A2:T" & $iLastRow),$oDestinationWorkbook.ActiveSheet.Range("A" & $DestinationiLastRow))

        EndIf

        $oWorkbook.Sheets("Facilites").Activate
        $oDestinationWorkbook.Sheets("Facilites").Activate

        $aCopyPaste = _Excel_RangeCopyPaste($oWorkbook.ActiveSheet, $oWorkbook.ActiveSheet.Range("A1"),$oDestinationWorkbook.ActiveSheet.Range("A" & $i + 1))


        $oDestinationWorkbook.Sheets("Sheet1").Activate

        $DestinationiLastRow = $oDestinationWorkbook.ActiveSheet.UsedRange.Rows.Count + 1
        _Excel_BookClose($oWorkbook, False)

    Next
    
_Excel_BookSaveAs($oDestinationWorkbook,"C:\Users\username\Desktop\Test\Combined 4")
If @error Then Exit MsgBox($MB_SYSTEMMODAL, "_Excel_BookSaveAs", "Error saving workbook to '" & "'." & @CRLF & "@error = " & @error & ", @extended = " & @extended)
_Excel_Close($oExcel)

 

[Nine]

Hi Sky.  Now I understand what you were asking.  Just create a unique Func with the required parameters...

[SkysLastChance]

Thanks Nine for the response. I am not sure what you mean though.  I made this question much harder then it had to be I think.

expandcollapse popup

#include <Array.au3>


;File List One
Global $aFileList1[4] = ["A" _
,"B" _
,"C" _
,"D"]
;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;

;File List Two
Global $aFileList2[3] = ["E" _
,"F" _
,"G"]
;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;

;File List Three
Global $aFileList3[2] = ["H" _
,"I"]
;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;

;File List Four
Global $aFileList4[12] = ["J" _
,"K" _
,"L" _
,"M" _
,"N" _
,"O" _
,"P" _
,"Q" _
,"R" _
,"S" _
,"T" _
,"U"]
;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;




    For $i = 0 To UBound($aFileList1) -1

    ConsoleWrite($aFileList1[$i])

    Next

    For $i = 0 To UBound($aFileList2) -1

    ConsoleWrite($aFileList2[$i])

    Next

    For $i = 0 To UBound($aFileList3) -1

    ConsoleWrite($aFileList3[$i])

    Next


    For $i = 0 To UBound($aFileList4) -1

    ConsoleWrite($aFileList4[$i])

    Next

To simplify my question. 

I am wondering if I can do this. In one loop with and keep multiple arrays instead of 4. 

Maybe I am doing it wrong, but I was not able to make the Ubound($aFileList) into a variable .

X = 1

UBound($aFileList & $x) -1

This does not work. 

Edited March 11, 2022 by SkysLastChance

[Nine]

What I meant was to create a function instead of repeating just about the same code 4 times :

MyFunc($aFileList1)
MyFunc($aFileList2)
MyFunc($aFileList3)
MyFunc($aFileList4)

Func MyFunc (ByRef $aArray)
  For $i = 0 To UBound($aArray) -1
    ConsoleWrite($aArray[$i])
  Next
EndFunc

But to answer your question, you need to use Eval function :

For $i = 1 to 4
  ConsoleWrite(UBound(Eval("aFileList" & $i)) & @CRLF)
Next

 

[SkysLastChance]

Love learning new tricks. Thank you as always Nine.

Just so I have it here is what I went with.

expandcollapse popup

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

;File List One
Global $aFileList1[4] = ["C:\Users\UserName\Desktop\FileName\1.xlsx" _
,"C:\Users\UserName\Desktop\FileName\2.xlsx" _
,"C:\Users\UserName\Desktop\FileName\3.xlsx" _
,"C:\Users\UserName\Desktop\FileName\4.xlsx"]
;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;

;File List Two
Global $aFileList2[3] = ["C:\Users\UserName\Desktop\FileName\5.xlsx" _
,"C:\Users\UserName\Desktop\FileName\6.xlsx" _
,"C:\Users\UserName\Desktop\FileName\7.xlsx"]
;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;

;File List Three
Global $aFileList3[2] = ["C:\Users\UserName\Desktop\FileName\8.xlsx" _
,"C:\Users\UserName\Desktop\FileName\9.xlsx"]
;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;

;File List Four
Global $aFileList4[12] = ["C:\Users\UserName\Desktop\FileName\100.xlsx" _
,"C:\Users\UserName\Desktop\FileName\11.xlsx" _
,"C:\Users\UserName\Desktop\FileName\12.xlsx" _
,"C:\Users\UserName\Desktop\FileName\13.xlsx" _
,"C:\Users\UserName\Desktop\FileName\14.xlsx" _
,"C:\Users\UserName\Desktop\FileName\15.xlsx" _
,"C:\Users\UserName\Desktop\FileName\16.xlsx" _
,"C:\Users\UserName\Desktop\FileName\17.xlsx" _
,"C:\Users\UserName\Desktop\FileName\18.xlsx" _
,"C:\Users\UserName\Desktop\FileName\19.xlsx" _
,"C:\Users\UserName\Desktop\FileName\20.xlsx" _
,"C:\Users\UserName\Desktop\FileName\21.xlsx"]
;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;


Global $aExcelData[0][20]
Global $oExcel = _Excel_Open()
    If @error Then Exit
Global $oDestinationWorkbook = _Excel_BookNew($oExcel)
    $oExcel.ActiveSheet.Name = "Sheet1"
_Excel_SheetAdd($oDestinationWorkbook, -1, False, 1, "Facilites")
$oDestinationWorkbook.Sheets("Sheet1").Activate

Global $DestinationiLastRow = $oDestinationWorkbook.ActiveSheet.UsedRange.Rows.Count
Global $oWorkbook, $aRangeRead




For $iLoop = 1 to 4

    For $i = 0 To UBound(Eval("aFileList" & $iLoop)) -1

        $oWorkbook = _Excel_BookOpen($oExcel, Eval("aFileList" & $iLoop)[$i], True, True)
            If @error Then
                MsgBox(0,"Error",Eval("aFileList" & $iLoop)[$i] & " is missing. Push Okay to Continue")
                ContinueLoop
            EndIf
        Local $iLastRow = $oWorkbook.ActiveSheet.UsedRange.Rows.Count ;This gets the row count of the current sheet

        If $i = 0 Then ;This is used to get the header from the first row and not the other sheets after.
            $aCopyPaste = _Excel_RangeCopyPaste($oWorkbook.ActiveSheet, $oWorkbook.ActiveSheet.Usedrange.Columns("A:T"),$oDestinationWorkbook.ActiveSheet.Range("A" & $DestinationiLastRow))
                If @error Then Exit MsgBox($MB_SYSTEMMODAL, "_Excel_RangeCopy", "Error copying cells." & @CRLF & "@error = " & @error & ", @extended = " & @extended)
        Else
            $aCopyPaste = _Excel_RangeCopyPaste($oWorkbook.ActiveSheet, $oWorkbook.ActiveSheet.Range("A2:T" & $iLastRow),$oDestinationWorkbook.ActiveSheet.Range("A" & $DestinationiLastRow))
                If @error Then Exit MsgBox($MB_SYSTEMMODAL, "_Excel_RangeCopy", "Error copying cells." & @CRLF & "@error = " & @error & ", @extended = " & @extended)
        EndIf

        $oWorkbook.Sheets("Facilites").Activate
        $oDestinationWorkbook.Sheets("Facilites").Activate

        $aCopyPaste = _Excel_RangeCopyPaste($oWorkbook.ActiveSheet, $oWorkbook.ActiveSheet.Range("A1"),$oDestinationWorkbook.ActiveSheet.Range("A" & $i + 1))
            If @error Then Exit MsgBox($MB_SYSTEMMODAL, "_Excel_RangeCopy", "Error copying cells." & @CRLF & "@error = " & @error & ", @extended = " & @extended)

        $oDestinationWorkbook.Sheets("Sheet1").Activate

        $DestinationiLastRow = $oDestinationWorkbook.ActiveSheet.UsedRange.Rows.Count + 1
        _Excel_BookClose($oWorkbook, False)

    Next




    _Excel_BookSaveAs($oDestinationWorkbook,"C:\Users\UserName\Desktop\Test\Combined " & $iLoop)
        If @error Then Exit MsgBox($MB_SYSTEMMODAL, "_Excel_BookSaveAs", "Error saving workbook to '" & "'." & @CRLF & "@error = " & @error & ", @extended = " & @extended)
    _Excel_Close($oExcel)

Next

 

[seadoggie01]

I like to avoid using eval, because it looks messy to me and makes future changes much harder. Personally, I like option 1 better, but if you want to store an array in another array, you can do that...

; declare your 4 files lists above
Local $aListList[4] = [$aFileList1, $aFileList2, $aFileList3, $aFileList4]

; Option 1:
  ; For each list
  For $iList=0 To UBound($aListList) - 1
      $aFileList = $aListList[$iList]
      ; For each file in the list
      For $iFile = 0 To UBound($aFileList) - 1
          $sFile = $aFileList[$iFile]
      Next
  Next

; Option 2:
  ; For each List
  For $iList = 0 To UBound($aListList) - 1
      ; For each file in the list
      For $iFile = 0 To UBound($aListList[$iList]) - 1
          $sFile = ($aListList[$iList])[$i]
      Next
  Next

Obviously, if what you have works for you, that's great and keep going 

[SkysLastChance]

Hey, thanks so much. I like this a lot better. It took me a bit to figure out what was going on. 

Leaving this here so I have something to look back at. 

expandcollapse popup

#include <Array.au3>


;File List One
Global $aFileList1[4] = ["A" _
,"B" _
,"C" _
,"D"]
;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;

;File List Two
Global $aFileList2[3] = ["E" _
,"F" _
,"G"]
;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;

;File List Three
Global $aFileList3[2] = ["H" _
,"I"]
;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;

;File List Four
Global $aFileList4[12] = ["J" _
,"K" _
,"L" _
,"M" _
,"N" _
,"O" _
,"P" _
,"Q" _
,"R" _
,"S" _
,"T" _
,"U"]
;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;




; declare your 4 files lists above
Local $aListList[4] = [$aFileList1, $aFileList2, $aFileList3, $aFileList4]

; Option 1:
  ; For each list
  For $iList=0 To UBound($aListList) - 1
      $aFileList = $aListList[$iList]
      ; For each file in the list
      For $iFile = 0 To UBound($aFileList) - 1
          $sFile = $aFileList[$iFile]
          ConsoleWrite($sFile & @CRLF)
      Next
      
    ConsoleWrite("Save Book" & @CRLF)
      
  Next

 

Edited March 11, 2022 by SkysLastChance

[junkew]

For your reference I would take the approach with a function as was given

• You do not have to give a dimension for your arrays
• You do not have to put a separate variable $aFilelist on a separate line
  ($aListList[$iList])[$iFile] with opening/closing brace will do the trick

See full example below

expandcollapse popup

#include <Array.au3>

;File List One - Four
Local $aFileList1[]= ["A" ,"B" ,"C" ,"D"]
Local $aFileList2[] = ["E" ,"F" ,"G"]
Local $aFileList3[] = ["H" ,"I"]
Local $aFileList4[] = ["J" ,"K" ,"L" ,"M" ,"N" ,"O" ,"P" ,"Q" ,"R" ,"S" ,"T" ,"U"]

local $aListList[]=[$aFileList1,$aFileList2,$aFileList3, $aFileList4]

example1()
example2()

func example1()
; Option 1:
  ; For each list
  For $iList=0 To UBound($aListList) - 1
      ; For each file in the list
      For $iFile = 0 To UBound($aListList[$iList]) - 1
          $sFile = ($aListList[$iList])[$iFile]
          ConsoleWrite($sFile & @CRLF)
      Next

    ConsoleWrite("Save Book" & @CRLF)

  Next
EndFunc

func example2()
;~ Option 2 with function
  ; For each list
  For $iList=0 To UBound($aListList) - 1
    mergeMyArray($aListList[$iList])
  Next
EndFunc

func mergeMyArray($aFileList)
      ; For each file in the list
      For $iFile = 0 To UBound($aFileList) - 1
          $sFile = $aFileList[$iFile]
          ConsoleWrite($sFile & @CRLF)
      Next

    ConsoleWrite("Save Book" & @CRLF)
EndFunc

 

Edited March 14, 2022 by junkew

[Nine]

The summum of cleanestery :

Local $aListList[4] = [$aFileList1, $aFileList2, $aFileList3, $aFileList4]

For $aArray In $aListList
  For $sText In $aArray
    ConsoleWrite($sText)
  Next
Next

Edited March 14, 2022 by Nine

[junkew]

@SkysLastChance

This was the intented reply on using functions. Your solution was written a little more complex so I rewrote it to below, you still have to test but hopefully some usefull learning points. I was confused in some areas what your intention is with sheet1 so maybe some errors but hopefully with the names of the variables you understand what I rewrote.  You do not have to activate sheets to work with them you just can make references to worksheets which will in general be quicker.

expandcollapse popup

#include <Array.au3>
#include <excel.au3>
Global $oExcel 
;File List One - Four
Local $aFileList1[]= ["A" ,"B" ,"C" ,"D"]
Local $aFileList2[] = ["E" ,"F" ,"G"]
Local $aFileList3[] = ["H" ,"I"]
Local $aFileList4[] = ["J" ,"K" ,"L" ,"M" ,"N" ,"O" ,"P" ,"Q" ,"R" ,"S" ,"T" ,"U"]

example3()
func example3()
    $oExcel== _Excel_Open()
    If @error Then Exit
    
    Local $aListList[] = [$aFileList1, $aFileList2, $aFileList3, $aFileList4]

    For $i = 0 To UBound($aListList) -1
        for $filename in $aListList[$i]
            mergeWorkbook($filename,"C:\Users\username\Desktop\Test\Combined " & $i)
        next
    next

    _Excel_Close($oExcel)

endfunc
func mergeWorkbook($wbSourceFileName, $wbDestinationFileName)
;~ Global $aExcelData[0][20]
    Local $oDestinationWorkbook = _Excel_BookNew($oExcel)
    Local $oDestinationSheet1Worksheet=$oDestinationWorkbook.Worksheets("Sheet1")

;~     $oExcel.ActiveSheet.Name = "Sheet1"

    _Excel_SheetAdd($oDestinationWorkbook, -1, False, 1, "Facilites")
    Local $oDestionationFacilitesWorksheet=$oDestinationWorkbook.worksheets("Facilites")
;~ $oDestinationWorkbook.Sheets("Sheet1").Activate

;~ Global $DestinationiLastRow = $oDestinationWorkbook.ActiveSheet.UsedRange.Rows.Count
    Local $DestinationiLastRow = $oDestinationSheet1Worksheet.UsedRange.Rows.Count

;~ Global $oWorkbook, $aRangeRead
    $oSourceWorkbook = _Excel_BookOpen($oExcel, $wbSourceFileName, True, True)
    If @error Then
        MsgBox(0,"Error",$wbSourceFileName & " is missing. Push Okay to Continue")
;~                 ContinueLoop
        return SetError(1, 10, "Some return value")
    EndIf
    
;~     Local $iLastRow = $oWorkbook.ActiveSheet.UsedRange.Rows.Count    ;~ The activesheet can be anything make explicit named reference
    local $sourceWorksheet=$oSourceWorkbookworksheets("Sheet1")
    Local $iLastRow = $sourceWorksheet.UsedRange.Rows.Count 

;~  If $i = 0 Then ;This is used to get the header from the first row and not the other sheets after.
    if ($oDestionationFacilitesWorksheet.usedrange.rows.count =0)
        $aCopyPaste = _Excel_RangeCopyPaste($sourceWorksheet, $sourceWorksheet.Usedrange.Columns("A:T"),$oDestionationFacilitesWorksheet.Range("A" & $DestinationiLastRow))
    Else
        $aCopyPaste = _Excel_RangeCopyPaste($sourceWorksheet, $sourceWorksheet.Range("A2:T" & $iLastRow),$oDestionationFacilitesWorksheet.Range("A" & $DestinationiLastRow))
    EndIf

;~     $oSourceWorkbook.Sheets("Facilites").Activate
;~     $oDestinationWorkbook.Sheets("Facilites").Activate
    
    $oSourceFacilitesWorksheet=$oSourceWorkbook.Sheets("Facilites")
    $oDestinationFacilitesWorksheet=$oDestinationWorkbook.Sheets("Facilites")
    

    $aCopyPaste = _Excel_RangeCopyPaste($oSourceFacilitesWorksheet, $oSourceFacilitesWorksheet.Range("A1"),$oSourceFacilitesWorksheet.Range("A" & $i + 1))

;~     $oDestinationWorkbook.Sheets("Sheet1").Activate

    $DestinationiLastRow = $oDestinationSheet1Worksheet.UsedRange.Rows.Count + 1
    _Excel_BookClose($oSourceWorkbook, False)
    
    _Excel_BookSaveAs($oDestinationWorkbook,$wbDestinationFileName)

    If @error Then Exit MsgBox($MB_SYSTEMMODAL, "_Excel_BookSaveAs", "Error saving workbook to '" & "'." & @CRLF & "@error = " & @error & ", @extended = " & @extended)

EndFunc

 

[SkysLastChance]

Oh wow, Thank you! @junkew I ultimately  did end up going with your example from Monday and it is working great. Yours and @seadoggie01example worked the best because the $iFiile and $iList variables come in a lot of handy. However, I see there is some more things I can clean up from your post today. It going to take me a bit to go over these. Thanks again for your time.  

P.S. Thanks for leaving in the old code.

P.S.S. This is great. I have a lot of scripts to update... 

Edited March 16, 2022 by SkysLastChance