Recently I’ve got something to work on with several excel files. More than 150 excel files generated by a web based application have to be merged into one file and then create a summary from it. Usually i did this by doing copy paste all values one at a time into a new empty spreadsheet, or copy to merge sheets to another excel files one by one. But yesterday my friend show me a simple way to combine or merge multiple excel files with macros inside Excel 2007 (edit macros with MS Visual Basic editor and no download required).Although I’m not familiar with office macros, but i can use it easily by just write a simple xls file merger code on vb editor, change the working folder path and cell starter reference name inside the code to suit your reference, and then click RunSub. All excel (xls or xlsx) files inside working folder will be merged into current worksheet.
Watch it on YouTube
For more detail, here’s what i did to merge multiple excel files with MS Excel 2007.
- Gather all xls or xlsx files that you wanted to merge into a folder. Remember that this merger macros will only grab the first worksheet on spreadsheet files. So make sure that all contents is on the first worksheet before continue.
- Close all working excel files so you can focus only on merging files.
- On MS Excel, create new spreadsheet by simply pressing CTRL+N.
- And open Microsoft Visual Basic editor by pressing ALT+F11, you’ll see a blank text editor.
- Now open by doubleclicking ThisWorkBook on the left sheet menu.
- Paste the following macros code:
- Change the folder as mentioned on comment on the macros code
- Change also column start reference to suit your need (usually first row used by column header, so i used A2 as start point).
- For example to start merging all files from column “B” row “1”.
- Change “IV” only if you have files using column wider than column “IV”. Basically, it will try to copy values on all available columns. If you notice the latest column on new worksheet is “IV”, it is the default available column on until your columns growth more than that.
- If everything configured already, press “F5” or click on play icon to run the code (RunSub). You’ll see working progress on left sheet menu.
- If all done, you can now switch to worksheet to see the result.
If you set a new folder within the code, and then hit “F5” or press SubRun button, the result will be added into current worksheet bellow the previous data. That’s mean it will not overwritten the last result but as another merge to previous merged data. In conclusion, you can spam change folder path and hit F5 to run the code in order to merge all files on provided folder into current worksheet. If you want to start new merge for multiple xls or xlsx files you have to clear the current worksheet, or create new file for merge. I hope there’s also a way to merge spreadsheet similar to this but for LibreOffice, since i also work on several ods files.
Update
Read also merge excel with simple merger tool for small files — merge without macros.
Download Folder Lock 7.6.4 Crack & Setup Free Direct Link !
