Need help combining Multiple Excel Files & Sheets into one Workbook

[antimattercrusader]

Hello! I need some Excel help, and hope I came to the right place!

I use LPS Desktop which exports excel files in 2003 compatible format. Unfortunately, due to the limits of both LPS and 2003 Excel, I must export a total of 19 files; each file has between 1 and 5 tabs. Then, I spend a few hours manually combining them by copy and pasting all of it into an Excel 2007 workbook. Stupid? I think so.

Does anyone know if there is a method of automatically combing all these (so long as the total is less than 1.4 million records, I know) into one worksheet?

Any help would be greatly appreciated and would add years to my life.

[Pepe Le Mokko]

Hi
why not have a go at this add-in? - Code is also available

[antimattercrusader]

Heck yes! Thank you so much - this is perfect. Just turned hours into minutes.

[antimattercrusader]

Actually, I am having a bit of an issue with this.

I first placed a sample set in a folder. I did the sample of 2 files with multiple tabs manually, resulting in around 484,000 records.

Then I tried the RDBMerge Tool, and selected the following:
Folder Location: *Folder with the 2 Files*
Which Files: XL?
- Merge All Files from the folder in the files location
Which Worksheets
- Merge all worksheets
Which Range:
- First call A1 till last cell on Worksheet

Checked Boxes:
- Add File Name
- Paste as Values


When I selected MERGE it produced a worksheet with two columns: One with the file location, and one with the sheet. But no data. There were 484k of these, so it got the count right.

Next, I tried unchecking paste as values and ran the app. It returned a worksheet with the two columns as before, with 484k rows, and the correct data after that - but only 90k rows of data. (Only, it appears, some data from Sheet 1, there are a total of 5 sheets).

Any suggestions?

[antimattercrusader]

I've been playing around... I think it has to do with sheet and the column headings.

The LPS exported files only have column headings on the 1st sheet. I deleted those, leaving only data and the app picked up the 1st and last sheets.

Then, I ran it separately for each sheet, 1 - 5, and it returned correct results.

I then used it to combine those files and now have one completed combined file. That'll still save a ton of time, but if anyone has any suggestions on how to remedy the issue, it would be helpful.

Thanks again!