Merging Excel Sheets

[iamtheky]

This meets a singular purpose, so figured leave this in GH&S for a couple of weeks to turn into something with a few more options.

First pass takes a directory of worksheets, and merges them into a workbook with sheets given their filename, but there is no errror checking or attempt to sanitize anything

 

v2

handles multiple sheets

#include<file.au3>
#include<array.au3>
#include<excel.au3>

$aFiles = _FileListToArray(FileSelectFolder("select xlsx dir" , @ScriptDir) , "*.xlsx" , 0 , True)

$oXL =_Excel_Open(False)
$oBook1 = _Excel_BookOpen($oXL , $aFiles[1])

$oBook1.Sheets(1).Name = stringmid( stringtrimright($aFiles[1] , 5) , stringinstr($aFiles[1] , "\" , 0 , -1) + 1) & 1

For $i = 2 to $aFiles[0]

    $oBookCpy = _Excel_BookOpen($oXL , $aFiles[$i])
    $aList = _Excel_SheetList($oBookCpy)
    For $n = 0 to ubound($aList) - 1
    _Excel_SheetCopyMove($oBookCpy , $n + 1 , $oBook1 , $i - 1 + $n , False)
    $oBook1.Sheets($i + $n).Name = stringmid( stringtrimright($aFiles[$i] , 5) , stringinstr($aFiles[$i] , "\" , 0 , -1) + 1) & $n + 1
    Next
    _Excel_BookClose($oBookCpy)

Next

_Excel_BookSaveAs($oBook1 , stringtrimright($aFiles[1] , 5) & "_MERGE.xlsx")
_Excel_BookClose($oBook1 , False)
_Excel_Close

 

Edited December 18, 2018 by iamtheky

[pixelsearch]

Hi iamtheky
In case it may help you, as it didn't work for me immediately, then I checked why :
* My files are .xls so I had to change all three ", 5" with ", 4" in the script
* And all ".xlsx" to ".xls"
* Also _Excel_BookSaveAs() returned @error 5; @extended = -2147352567 so I added the 3rd parameter $iFormat, after consulting ExcelConstants.au3  (thanks help file !)

Also I noticed, in case there are several sheets in each excel file :
* 1st excel file treated will have all its sheets added to the final workbook
* following excel files treaded will have only 1 sheet added, not necessarily the 1st one, but the one that gets the focus when the file is normally opened (i.e the one that had focus when the file was saved)

Edit : comments based on your 1st script (deleted now), before you edited your post to add version 2

Hope it helps anyway

Edited December 18, 2018 by pixelsearch

[iamtheky]

nice, thanks. I certainly need to make it more forgiving on file selection.

I think the issue with multiple sheets is all cleared up, but i havent tried limit testing it.  Do you know why you were throwing that error without the format parameter, i cant duplicate that?

[pixelsearch]

Testing v2, brb in a few to edit this last post and answer you

Back. Yes all sheets are now added in v2, great !
I had to add an $iFormat parameter = 18 to have it work (old excel) but the help file clearly states :
"Starting with Excel 2007 you have to provide both the $iFormat parameter and the correct file extension in $sFilePath"

; _Excel_BookSaveAs($oBook1 , stringtrimright($aFiles[1] , 5) & "_MERGE.xlsx")
_Excel_BookSaveAs($oBook1 , stringtrimright($aFiles[1] , 4) & "_MERGE.xls", 18)
$iKeep_error = @error
$iKeep_extended = @extended
If $iKeep_error Then
    MsgBox(0, "Save error", "@error = " & $iKeep_error & "   @extended = " & $iKeep_extended)
EndIf

Concerning the sheets names and positions, which are a bit modified in the final workbook, maybe there is a way to improve it ?

For example I did the test with 2 xls files, placed here :
C:\Test\Book1.xls
C:\Test\Book2.xls

Book1.xls is composed of 3 sheets, named Sheet1a, Sheet1b, Sheet1c
Book2.xls is composed of 3 sheets, named Sheet2a, Sheet2b, Sheet2c

When the script is run, it doesn't necessarily treat Book1.xls before Book2.xls, depending on how Windows (or AutoIt ?) retrieves the files names one after the other. In my case, the final Book2_MERGE.xls appears like this :

Shouldn't the sheets names and positions be different of the actual result ?

Edited December 18, 2018 by pixelsearch

[pixelsearch]

In case you need a version that keeps the sheets names unchanged, eventual duplicates will be shown with (2) appended to the sheet name. Also file names are sorted (useful) and sheet names will follow that order ;

The test was made on 3 files :
1) 1st file : Sheet1a, Sheet1b, Sheet1c
2) 2nd file : Sheet2a, Sheet2b, Sheet2c
3) 3rd file : Sheet3a, Sheet3b, Sheet3c

A little error checking in the script, for fun

 

expandcollapse popup

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

$sFileSelectFolder = FileSelectFolder("select xlsx dir", @ScriptDir)
If @error Then _Exit("FileSelectFolder", @error, @extended)

$aFiles = _FileListToArray($sFileSelectFolder, "*.xlsx", 0, True)
If @error Then _Exit("_FileListToArray", @error, @extended)

_ArrayDisplay($aFiles, "Before sort")
If @error Then _Exit("_ArrayDisplay - before sort", @error, @extended)

_ArraySort($aFiles, 0, 1) ; 0 = Ascending order, 1 = index to start sorting
If @error Then _Exit("_ArraySort", @error, @extended)

_ArrayDisplay($aFiles, "After sort")
If @error Then _Exit("_ArrayDisplay - after sort", @error, @extended)

$oXL =_Excel_Open(False)
If @error Then _Exit("_Excel_Open", @error, @extended)

$oBook1 = _Excel_BookOpen($oXL , $aFiles[1])
If @error Then _Exit("_Excel_BookOpen file #1", @error, @extended)

$aList = _Excel_SheetList($oBook1)
If @error Then _Exit("_Excel_SheetList file #1", @error, @extended)

$iIdx_Lastsheet = Ubound($aList) ; will be incremented after 1st copy, not b4 (+++)

For $i = 2 to $aFiles[0]
    $oBookCpy = _Excel_BookOpen($oXL, $aFiles[$i])
    If @error Then _Exit("_Excel_BookOpen file #" & $i, @error, @extended)

    $aList = _Excel_SheetList($oBookCpy)
    If @error Then _Exit("_Excel_SheetList file #" & $i, @error, @extended)

    For $n = 0 to Ubound($aList) - 1
        _Excel_SheetCopyMove($oBookCpy, $n + 1, $oBook1, $iIdx_Lastsheet, False)
        If @error Then _Exit("_Excel_SheetCopyMove file #" & $i, @error, @extended)

        $iIdx_Lastsheet += 1
        $oBook1.Sheets($iIdx_Lastsheet).Name = $aList[$n][0]
    Next

    _Excel_BookClose($oBookCpy, False) ; False = don't save
    If @error Then _Exit("_Excel_BookClose file #" & $i, @error, @extended)
Next

_Excel_BookSaveAs($oBook1, StringTrimRight($aFiles[1], 5) & "_MERGE.xlsx")
If @error Then _Exit("_Excel_BookSaveAs" , @error, @extended)

_Excel_BookClose($oBook1, False) ; False = don't save
If @error Then _Exit("_Excel_BookClose file #1", @error, @extended)

_Excel_Close($oXL, False, True) ; False = don't save (already done) . True = Force close
If @error Then _Exit("_Excel_Close" , @error, @extended)

; ====================================================
Func _Exit($sError_msg, $iKeep_error, $iKeep_extended)
    MsgBox($MB_TOPMOST, "Error : " & $sError_msg , _
        "@error = " & $iKeep_error & "    @extended = " & $iKeep_extended)
    Exit
EndFunc ; _Exit