Join serveral Excel files to one file (Exports to csv via COM)

Post your working scripts, libraries and tools
Acuena
Posts: 20
Joined: 27 Jan 2014, 14:56

Join serveral Excel files to one file (Exports to csv via COM)

16 Apr 2018, 06:36

Hi all!

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 :D

Anyhow, here is the code (80 lines of code, could possible be shortned :P)

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

Return to “Scripts and Functions”

Who is online

Users browsing this forum: afe, Bing [Bot] and 43 guests