I haven recently been working on a script to merge several Excel files to one big. Havent found any free software to do this so i made my own.
It uses Excel COM to re-save all Excel files in the selected folder into CSV-files.
It then generates a bat file the user have to run.
All code is there and is free to use and modify, just plz mention here if you do.
Find any bugs or have a improvment? Let me know
Anyhow, here is the code (80 lines of code, could possible be shortned
)Code: Select all
; Merge several Excel files to one File
; Author: Acuena
; Forum URL: https://autohotkey.com/boards/viewtopic.php?f=6&t=47391
; How it works:
; It uses Excel COM to re-save all chosen Excel-files into csv files.
; Then it generates a BAT file with the following code: copy *.csv Output.csv
; It also adds a code line that deletes the BAT file when it has been run
; Then the user only need to import this csv file in Excel.
;Create the GUI
Gui, Add, Text, x12 y9 w120 h20 , Path the origianal files:
Gui, Add, Edit, x132 y9 w300 h20 vPathOriginal
Gui, Add, Button, x442 y9 w70 h20 gChoseOriginalFiles, Choose path
Gui, Add, Text, x12 y35 w120 h20 , Name of the output file:
Gui, Add, Edit, x132 y35 w300 h20 vFilename
Gui, Add, Text, x12 y60 w40 h20 , Files:
Gui, Add, ListBox, x12 y78 w500 h210 +HwndLB vFileList
Gui, Add, Button, x392 y282 w120 h30 gMerge, Merg the files
Gui, Show, w526 h320, Merge several Excel files to one (1.0)
return
GuiClose:
ExitApp
;Let user select the folder which contains the files
ChoseOriginalFiles:
FileSelectFolder, PathOriginal,,,Choose the folder which contains the Excel files
PathOriginal := RegExReplace(PathOriginal, "\\$") ; Removes the trailing backslash, if present.
if PathOriginal =
{
MsgBox, 0x40, Merge several Excel files to one, No folder has been selected
}
Else
{
GuiControl,, SokOriginal, %PathOriginal%
GuiControl, , FileList, |
Loop, Files, %PathOriginal%\*.xls*
{
GuiControl, , FileList, %A_LoopFileName%
}
}
Return
;Do the merging of the files
Merge:
Gui,Submit,NoHide
if (Filename="")
{
MsgBox, 0x40, Merge several Excel files to one, You must choose a Filename
Return
}
ControlGet, ListItems, List, , , % "ahk_id " LB
if (ListItems ="")
{
MsgBox, 0x10, Merge several Excel files to one, There is no files to merge or no folders has been choosen
return
}
Loop, Files, %PathOriginal%\*.*
{
FilePath = %A_LoopFileLongPath%
XL := ComObjCreate("Excel.Application") ;starts excel nothing visible
XL_Workbook := XL.Workbooks.Open(FilePath) ;opens the file in a new workbook
Filename = %A_LoopFileDir%\%A_LoopFileName%.csv
Filename := RegExReplace(Filename, ".xls.") ; Removes the trailing backslash, if present.
XL_Workbook.SaveAs(Filename, xlCSV:=6)
XL.Quit
}
FileAppend, copy *.csv %Filename%.csv`n, %PathOriginal%\Merge csv files.bat
FileAppend, del `"`%~f0`" & exit, %PathOriginal%\Merge csv files.bat
MsgBox, 0x40, Merge several Excel files to one, All Excel files has been saved as CSV files.`nRun the generated BAT-file to merge them to one file.`nThen import it into Excel.`nThe Bat-file is automaticly deleted.
return