Any easy/automated way of doing this?

I have a simple worksheet with a number of columns, primarily contact info-type stuff. This template will be sent out to several hundred individuals who will add their own data, ranging from a few rows to hundreds of rows and then return the sheets via email.

I'd like to then be able to take these hundreds of individual sheets and merge them into a single Excel worksheet. Yeah, I could open and copy/paste each one but surely there is a more automated solution. Ideally, I'd rather not have to open and manipulate each sheet.

TIA

---

StingMe

Do all of the worksheets contain the same field structure, same column headings, and the same column order?

---

Yes, all sheets generated from a master template...

---

StingMe

Sting.. there *are* better ways to do that via shared excel file (if on a common secured network), web front end databases, etc....

The base problem with that sort of thing is that everybody will mess with your file, unless you protect the formatting and password protect the protection.

"Oh. I added a column for middle names because I have two of them . Sorry"
"Oh. I just sent you a .vcf file instead, that's where I keep my information. Sorry."

and so forth.

Not to mention you'll have to do this again and again and again, ad infinitum. And of course, the biggie:

Not Everyone Has Excel


Step back and consider your requirements ?

If you put all the files into one folder on your mac, you can 'select all' in Finder and paste the list of filenames into excel. Then use VBA to open each file one at a time, copy its contents, paste into your master sheet, close the file and then open the next one.

if you've done any programming it should be relatively straightforward.

Alternatively, you could use links to each individual cell in each sheet and even if those workbooks aren't open, you can get all the info from those sheets. You'll need to specify the full filepath to get the info from a cell.

for example:
='MacHD:Users:username: Documents:Work:Results:[Workbook1]: Sheet1'!A1

If you drag this formula down and to the right you'll get all the contents of this sheet. Then you can copy it and paste it (probably as values) into another sheet. If you use the concatenate function you can also change the name of the workbook so that you can do it all at once.

VBA is the way to go on this. There are example scripts (just change parts of the code to match your sheets) online that you can simply copy/paste the code and run it. Just search for it.

---

In order to use VBA to solve this problem, of course, the version of Excel you're using must support VBA. Good luck with that.

---

RIP, Greg the DogSitter. You are missed.



Edited 1 time(s). Last edit at 07/30/2012 03:50PM by N-OS X-tasy!.

Thanks for all of the food for thought.

Eventually a web-enabled data submission solution will be created - this is just a first baby step in that direction.

I realize not everyone has access to Excel - given that this is a very simple sheet, I was assuming that a CSV file created by any number of apps would work fine as well.

We'll probably have to put someone's eyes on each file to see that it was completed properly so maybe copy/paste isn't going to be much more time consuming than the alternatives.

Back to the drawing board

---

StingMe

you could also consider a shared google doc (spreadsheet) for those who don't have access to excel. Again, not idiot-proof as all data would be available to everyone, but would be web-based.