Macro to reorganize and collect data from multiple workbooks

[bobfor1]

I'm sending out an excel file with a survey to 300+ individuals and was wondering if there was a way to reorganize and collect the data automatically.

I've attached a sample workbook with an example survey.

The data points are located at different sections of the survey and I was wondering if there was a way to automatically reorganize all the data in a single row (example is located in the example output worksheet) and collect it from several workbooks.

The survey that was sent out only includes an instructions tab and the survey tab.

Also not sure if its possible to collect the filled out survey worksheets and dump them into one workbook. I ask this because in the actual survey there's some additional fields that have lists of info that individuals can add but won't be comparable since they can potentially add an infinite number of things.

[xladept]

Hi bobfor1,

You can do it if you allocate one or several rows per survey and you know the book names.

[Leith Ross]

Hello bofor1,

Create a "master" workbook that will hold the summary. Name one the sheets "Summary" and add the same headers to row 1 as in the example workbook. This macro will search the path "C:\Document and Settings\Owner\My Documents" for all Excel workbook files. Change this path to where your survey workbooks are stored. Each file will then be opened and the data from the "Survey" sheet will copied into the master"Summary" sheet. If you have any problems, let me know.

Please Login or Register  to view this content.

[JBeaucaire]

This macro should do the "collecting" for you. The macro goes into the workbook with the sheet names "Example Output", or change the macro to match your actual summary sheet name.

Also be sure to edit the macro so the fPATH points to the folder where all the surveys are. Make sure the Summary workbook you're creating is not in the same folder.

Please Login or Register  to view this content.

[bobfor1]

Thanks for the replies!

@Leith Ross - I seem to be running into some errors when I try using your code.

When I created worksheet labeled "Summary" and copied the headers into row 1 and ran the macro, it gives me an error at the line

Please Login or Register  to view this content.

When I delete the header and ran the macro, it gives me a different error saying that the file could not be found and to check the spelling and file location and gives me an error at the line

Please Login or Register  to view this content.

@JBeaucaire - Looks like everything is working great! Thanks!


Again appreciate the responses, thanks!

[Leith Ross]

Hello bobfor1,

Sorry, I forgot to add the Row property at the end. The statement should be...

Please Login or Register  to view this content.

[JBeaucaire]

Assuming the two options you've been given will give you what you need, be sure to select Thread Tools from the menu above and mark the thread as solved. Thanks.