Transferring selected data from 50 Excel workbooks to a single destination Excel workbook

[Spec86]

Loop is work, but don't copy and paste selected data from 50 Excel workbooks to a single destination Excel workbook. I work in Windows Operating System. I have folder with 50 Excel files. I have single destiny Excel file. Data go from folder to 1 single Excel file.

Help, please.

#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

 

[Subz]

Try something like:

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

Global $aFileList = _FileListToArrayRec(@ScriptDir & "\Nezavisimai\Papka2", "*.xlsx", 1, 1, 1, 2)
    If @error Then Exit
Global $aExcelData[1][3] = [["Sample type", "Sample name", "Result"]]
Global $oExcel = _Excel_Open()
    If @error Then Exit
Global $oWorkbook, $aRangeRead
For $i = 1 To $aFileList[0]
    $oWorkbook = _Excel_BookOpen($oExcel, $aFileList[$i], True, True)
        If @error Then ContinueLoop ConsoleWrite("Excel Book Open Error: " & $aFileList[$i] & @CRLF)
    $aRangeRead = _Excel_RangeRead($oWorkbook, "Данные", "A2:D30")
        If Not @error Then
            For $j = (UBound($aRangeRead) - 1) To 0 Step - 1
                If $aRangeRead[$j][0] <> "Sample" Then
                    _ArrayDelete($aRangeRead, $j)
                    ContinueLoop
                EndIf
                If $aRangeRead[$j][3] = "Over limit" Then _ArrayDelete($aRangeRead, $j)
            Next
            _ArrayColDelete($aRangeRead, 3)
            _ArrayAdd($aExcelData, $aRangeRead)
        EndIf
    _Excel_BookClose($oWorkbook, False)
Next
$oWorkbook = _Excel_BookNew($oExcel)
$oExcel.ActiveSheet.Name = "Лист1"
_Excel_RangeWrite($oWorkbook, Default, $aExcelData, "A1")

 

Edited September 9, 2021 by Subz

[Spec86]

5 hours ago, Subz said:

Try something like:

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

Global $aFileList = _FileListToArrayRec(@ScriptDir & "\Nezavisimai\Papka2", "*.xlsx", 1, 1, 1, 2)
    If @error Then Exit
Global $aExcelData[1][3] = [["Sample type", "Sample name", "Result"]]
Global $oExcel = _Excel_Open()
    If @error Then Exit
Global $oWorkbook, $aRangeRead
For $i = 1 To $aFileList[0]
    $oWorkbook = _Excel_BookOpen($oExcel, $aFileList[$i], True, True)
        If @error Then ContinueLoop ConsoleWrite("Excel Book Open Error: " & $aFileList[$i] & @CRLF)
    $aRangeRead = _Excel_RangeRead($oWorkbook, "Данные", "A2:D30")
        If Not @error Then
            For $j = (UBound($aRangeRead) - 1) To 0 Step - 1
                If $aRangeRead[$j][0] <> "Sample" Then
                    _ArrayDelete($aRangeRead, $j)
                    ContinueLoop
                EndIf
                If $aRangeRead[$j][3] = "Over limit" Then _ArrayDelete($aRangeRead, $j)
            Next
            _ArrayColDelete($aRangeRead, 3)
            _ArrayAdd($aExcelData, $aRangeRead)
        EndIf
    _Excel_BookClose($oWorkbook, False)
Next
$oWorkbook = _Excel_BookNew($oExcel)
$oExcel.ActiveSheet.Name = "Лист1"
_Excel_RangeWrite($oWorkbook, Default, $aExcelData, "A1")

 

Don't work. loop work in folder only. There are files for testing. 

Destiny single File.xlsx Folder Papka2 File 1.xlsx Folder Papka2 File 2.xlsx Folder Papka2 File 3.xlsx

[Subz]

See you want to copy formats, so would use something like:

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

Global $aFileList = _FileListToArrayRec(@DesktopDir & "\Nezavisimai\Papka2", "*.xlsx", 1, 1, 1, 2)
    If @error Then Exit
Global $aExcelData[0][3]
Global $oExcel = _Excel_Open()
    If @error Then Exit
Global $oDestinyWorkbook = _Excel_BookNew($oExcel)
    $oExcel.ActiveSheet.Name = "Лист1"
Global $iLastRow = $oDestinyWorkbook.ActiveSheet.UsedRange.Rows.Count
Global $oWorkbook, $aRangeRead
For $i = 1 To $aFileList[0]
    $oWorkbook = _Excel_BookOpen($oExcel, $aFileList[$i], True, True)
        If @error Then ContinueLoop
    $aCopyPaste = _Excel_RangeCopyPaste($oWorkbook.ActiveSheet, $oWorkbook.ActiveSheet.Usedrange.Columns("A:C"),$oDestinyWorkbook.ActiveSheet.Range("A" & $iLastRow))
    $iLastRow = $oDestinyWorkbook.ActiveSheet.UsedRange.Rows.Count + 1
    _Excel_BookClose($oWorkbook, False)
Next

 

[Spec86]

Subz, code is work. Thank you very much.