Collecting and reporting large survey data

This forum is really genius and I'm hoping that just a little bit passes by osmosis to me So I have many inartfully planned survey-form workbooks stored in the shared network drive, further separated by both area folders and operations folders, and then divided again into 30 business unit folders. Within the 30 business unit folders (standard 3-letter code naming convention), I need 1 of the 4 subfolders (again, each subfolder also following a standard naming convention), are survey form workbooks which each consist of 3 pages of text introduction and questions, and within which is housed, at most, 20 data-validated answers. The answers and filtering are basic and these are simply counted rather than manipulated significantly, but they have become voluminous and unwieldy when I need to periodically filter and summarize them. Data is essentially used once for reporting and then left in its original location in storage indefinitely but not planned for any future use. The 30 parent folders are relevant because these are individual business units that are charged and evaluated based upon the surveys, but there is a small but conceivable possibility that they may sometime want to examine survey workbooks. Previously, I copied and dropped the contents of all of those business unit folders into a new folder and used a macro to merge every workbook from that directory into a new master workbook to pull my reporting, but that master workbook from the first 6 months of program existence has now reached 3100 pages and it is ridiculous to take so long and experience so many errors (approximately 20 minutes just to open the master) when I need so very little information from each survey/workbook. I'm adding a visual reference just to try to add some clarification:

N:Network Shared Drive
***
O: Operating Area folder
***
P: Parent company folder
***
U: --- (Standardized Title) Business Unit folder
T: --- (Standardized Title) Target subfolder (survey workbooks)
X: Unnecessary
Y: Unnecessary
Z: Unnecessary

The solutions that I have come up with included the following:
• Indirect function using Laurent Longre's Morefunc.xll add-in to pull from all of the closed files but that too, has limitations in that I am still pulling 1 of 4 subfolders in each of the 30 business unit folders and believe that I have to set up a new workbook to get results from each folder.
• Impose a new naming convention by business unit folders and dates so that manually copying and pasting the subfolders will include fewer workbooks, but this will require some marginal degree of user participation and labor-shifting, and will still involve me selecting, copying and pasting 1 of the 4 subfolders in each of the 30 parent folders at least each quarter.
• Dabble in Access, which will be at least a moderate learning curve and seems ill-suited to the very limited nature of what I am collating and reporting.
• Quit and return to my lifelong dreams of becoming the world’s first plus-sized, middle-aged male super-model; while this is an exciting proposition, my own research panels have consistently shown ROI of such a venture to be challenging at best.

Many thanks in advance as I am, of course, open to and hopeful for your correction of any mistakes above or to other completely new ideas!

Hi
I will take a guess based on the information provided above:
Assuming all the lowest level folders have exactly the same layout - yes?
If yes then I would suggest that you have your Master Workbook containing links to each workbook picking up only the information you need for reporting. Whilst initially it would take a while to setup once it is done you would only need to add those links when you create a new workbook.
This way you would only be displaying the data from the multiple workbooks rather than storing it all in one Workbook as it appears you have been doing.
The processing time will obviously depend on the CPU processing power that you have and the speed of your network.
Let me know if this helps in any way.
Tony

Hi Chris
I have done some more digging and found some code written by a guy called Ron De Bruin. I have managed to get the code to merge rows 5 to 47 from every workbook in a given folder into a Master Workbook. However I am not a VBA expert so don't know how to modify the code to only merge given Col/Row numbers. Here is the link, hope it helps:

http://msdn.microsoft.com/en-us/libr...ffice.12).aspx

Look under the second section: Merging a Range from All Workbooks in a Folder.

Good luck.
Tony

Tony, Good suggestion. I was using some of his macros already (I haven't tried the add-in yet, that might make even simpler), to consolidate all of the survey/workbooks in the master in an earlier method before it got so large. I may just have to settle with using architecture to just have single column response data to limit what has to be copied, and then using a second step to re-order and filter results as a second step with the smaller amount of copied data. I can use that though, and am giving you reputation credit. Should genius strike you at a later date, I’d welcome the benefit of your gray matter again. Thanks again and cheers!

Thanks for the feedback.