I need to merge hundred Excel files into one Excel file. Each Excel file needs to be merged into its own worksheet in the workbook and the destination worksheet needs to have the name of the original file (minus the extension). Is this possible?
Excel – How to merge multiple sheets of Excel files to one Excel file
microsoft excelvba
Best Answer
Well, it doesn't seem like you've put any effort into this, but since I already have these macros written, I'll supply them for anyone else that is searching. These were written in excel 2007 and were part of a larger process.
It is important to note that this will fail if any of your file names have over 31 characters, excel has a character limit for sheet names
Combine the files into one with the name of the worksheets being set to the file name -
Now iterate through the sheets to remove the last five characters of the sheet name: .xlsx