Jump to content
Sign in to follow this  
Rimoun

Problem with merging many excel files

Recommended Posts

Hello Guys,

Recently, I am working on automation for Excel to reduce the amount of work. The work is like many excel files they have same number of columns for example A,B,C in all files, The number of files are not known. So, I made a List Box it will show the selected files. Then, there is a function to read each file into an array. I do not know how to merge all of the files in one array then write that array to a Master Excel file.

Here is my attempt to write the function but I only get the last file just.

Func mrge()
Local $o_WorkBook
Local $o_Excel = _Excel_Open(False)

    $B = _GUICtrlListBox_GetCount($List1)

        for $i = 0 to $B
        $o_WorkBook = _Excel_BookOpen($o_Excel,_GUICtrlListBox_GetText($List1,$i), False, False)
        $N = _Excel_RangeRead($o_WorkBook)
        _Excel_Close($o_Excel)
        _ArrayAdd($N,$N)
        Next
        _ArrayDisplay($N)
EndFunc

Thanks in advance.

Share this post


Link to post
Share on other sites

Have a look at _Excel_RangeCopyPaste in the helpfile. Example 2 explains how to copy cells from one workbook to another.


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2020-09-05 - Version 1.5.1.1) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2020-06-27 - Version 1.6.1.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX_GUI (NEW 2020-06-27 - Version 1.3.2.0) - Download
Outlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
PowerPoint (2017-06-06 - Version 0.0.5.0) - Download - General Help & Support
Excel - Example Scripts - Wiki
Word - Wiki
Task Scheduler (2019-12-03 - Version 1.5.1.0) - Download - General Help & Support - Wiki

Tutorials:
ADO - Wiki, WebDriver - Wiki

 

Share this post


Link to post
Share on other sites

Hmm

Just as an aside, what kind of data is in these spreadsheets? Must the data be sorted in any way?  Once in this Master Spreadsheet, I guess this is used for some kind of report(s)? Do you use the actual Master Spreadsheet, or do you use the reports extracted from it?

Skysnake


Skysnake

Why is the snake in the sky?

Share this post


Link to post
Share on other sites

Example code:

#include <Excel.au3>
Global $aData1[][] = [[11, 12], [21, 22], [31, 32]]
Global $aData2[][] = [["A", "B"], ["C", "D"]]
Global $iCurrentRow = 1
Global $oExcel = _Excel_Open()
Global $oWBIn[2]
$oWBIn[0] = _Excel_BookNew($oExcel)
$oWBIn[1] = _Excel_BookNew($oExcel)
Global $oWBOut = _Excel_BookNew($oExcel)

_Excel_RangeWrite($oWBIn[0], 1, $aData1)
_Excel_RangeWrite($oWBIn[1], 1, $aData2)

For $i = 0 To UBound($oWBIn) - 1
    _Excel_RangeCopyPaste($oWBIn[$i].Worksheets(1), $oWBIn[$i].Worksheets(1).Usedrange, $oWBOut.Worksheets(1).Range("A" & $iCurrentRow))
    $iCurrentRow = $iCurrentRow + $oWBIn[$i].Worksheets(1).Usedrange.Rows.Count
Next

 

Edited by water

My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2020-09-05 - Version 1.5.1.1) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2020-06-27 - Version 1.6.1.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX_GUI (NEW 2020-06-27 - Version 1.3.2.0) - Download
Outlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
PowerPoint (2017-06-06 - Version 0.0.5.0) - Download - General Help & Support
Excel - Example Scripts - Wiki
Word - Wiki
Task Scheduler (2019-12-03 - Version 1.5.1.0) - Download - General Help & Support - Wiki

Tutorials:
ADO - Wiki, WebDriver - Wiki

 

Share this post


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
Sign in to follow this  

  • Recently Browsing   0 members

    No registered users viewing this page.

×
×
  • Create New...