How to transfer selected data from 50 Excel workbooks to a single workbook?

Question

Loop in folder works, but loop on cells doesn't. I have a folder with 50 Excel files and a destination Excel file. Data should go from folder to the destination Excel file:

 

#include <file.au3>
#include <Excel.au3>
opt("WinTitleMatchMode", 2)
$path = "C:\Users\User\Desktop\Nezavisimai\Papka2\"
$FileListShort = _FileListToArray($path, "*.xlsx", 1, True)
$ProgExcel = _Excel_Open()
$workbookDestiny = _Excel_BookNew($ProgExcel)

$LineDestiny = 1
_Excel_RangeWrite($workbookDestiny, $workbookDestiny.Activesheet, "Sample type", "A" & $LineDestiny)
_Excel_RangeWrite($workbookDestiny, $workbookDestiny.Activesheet, "Sample name", "B" & $LineDestiny)
_Excel_RangeWrite($workbookDestiny, $workbookDestiny.Activesheet, "Result", "C" & $LineDestiny)

For $i = 1 to $FileListShort[0]

$workbookSource =  _Excel_BookOpen($ProgExcel, $FileListShort[$i], False, False)
For $LineSource = 2 to 30
$sampletype = _Excel_RangeRead($workbookSource, "Данные", "A" & $LineSource)
$samplename = _Excel_RangeRead($workbookSource, "Данные", "B" & $LineSource)
$sampleresult = _Excel_RangeRead($workbookSource, "Данные", "C" & $LineSource)
$sampleobs = _Excel_RangeRead($workbookSource, "Данные", "D" & $LineSource)
If $sampletype = "Sample"  Then
    If $sampleobs <> "Over limit" Then
        $LineDestiny = $LineDestiny + 1
        _Excel_RangeWrite($workbookDestiny, "Лист1", $sampletype, "A" & $LineDestiny)
        _Excel_RangeWrite($workbookDestiny, "Лист1", $sampletype, "B" & $LineDestiny)
        _Excel_RangeWrite($workbookDestiny, "Лист1", $sampletype, "C" & $LineDestiny)
     EndIF
EndIF
Next
_Excel_BookClose($workbookSource, False)
Next

Problems may be in here:

If $sampletype = "Sample"  Then
        If $sampleobs <> "Over limit" Then
           $LineDestiny = $LineDestiny + 1

Answer 1

This could be a good starting point. Let me know if it helps you out, or if there is something missing.

; Combine Excel files
#include <Excel.au3>
#include <Array.au3>
#include <File.au3>
#include <MsgBoxConstants.au3>

$allFiles_A = _FileListToArray(@ScriptDir & '\files\', '*.xlsx', $FLTA_FILES, True)
;~ _ArrayDisplay($allFiles_A)

; Create application object
Local $oExcel = _Excel_Open()
If @error Then Exit MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_BookOpen Example", "Error creating the Excel application object." & @CRLF & "@error = " & @error & ", @extended = " & @extended)

Local $oWorkbook_result = _Excel_BookNew($oExcel)
If @error Then
    MsgBox($MB_SYSTEMMODAL, "@error = " & @error & ", @extended = " & @extended)
    _Excel_Close($oExcel)
    Exit
EndIf

For $i = 1 To $allFiles_A[0]
    $end = $i * 35 + 1
    $start = $end - 35

    Local $sWorkbook_source = _Excel_BookOpen($oExcel, $allFiles_A[$i])
    If @error Then Exit MsgBox($MB_SYSTEMMODAL, "Excel UDF Error", "Error opening '" & $sWorkbook_source & "'." & @CRLF & "@error = " & @error & ", @extended = " & @extended)
    _Excel_RangeCopyPaste($sWorkbook_source.Activesheet, $sWorkbook_source.Activesheet.Range("A1:D35"))
    _Excel_RangeCopyPaste($oWorkbook_result.Activesheet, Default, $oWorkbook_result.Activesheet.Range('A' & $start & ':D' & $end), False, Default, Default, True)
    _Excel_BookClose($sWorkbook_source)
Next

;Autofit
$oWorkbook_result.ActiveSheet.Columns("A:E").AutoFit