Jump to content

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


Go to solution Solved by Subz,

Recommended Posts

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

 

Link to post
Share on other sites

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 by Subz
Link to post
Share on other sites
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

Link to post
Share on other sites
  • Solution

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

 

Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
  • Recently Browsing   0 members

    No registered users viewing this page.

×
×
  • Create New...