Data collection from different excel-files

[Bubuto]

I'm intending to make a survey using excel. The plan is to send excel sheets which contains the questions via e-mail to a group of people. They will then fill in the sheet and send it back to me. Is there any way to code a macro that could handle the data collection from the files? What I mean is that if I save all the sheets sent back to me in one directory, how do I collect the data from all of the excel-files say from question number 4 and present the results in one "results" -sheet? There will be a lot of answer files sent to me, so gathering the data manually one by one would take too much time!

 

[Iridium]

Look into consolidation (Data|Consolidate...) which should let you merge all the workbooks and process the responses when you get them back

 

[Bubuto]

Yes, I'm able to create a template, which automatically updates the data collection workbook, whenever a new workbook is created and saved from that template. But this requires that when the file is saved it has access to the data collection workbook to work properly. The problem is that I'm sending the questions sheets by e-mail, and I also collect them back that way. So eventually I'm going to have a directory with a bunch of excel workbooks, which will all be formatted the same way. And I need to collect the data from those files. To put it in english, the data collection workbook should to the following: look into the directory, say excelanswersheets, then sum all the values in cell "O2" in all the workbooks in that directory, and present the result in some cell in the data collection workbook. Does anyone have clue how to do this?

 

[lagavulin]

As a first step, you could get all the replies into a single sheet, using something like this:
==============================
Sub ReplyMerge()
' takes individual survey sheets with questions in A2 to A13, answers in B2 to B13

' puts answers into sheet 1 of open book,
' with first set of answers into B2 to B13, second set in C2 to C13, and so on.
Dim wbkReplies As Workbook
Set wbkReplies = ActiveWorkbook
Dim wshReplies As Worksheet
Set wshReplies = wbkReplies.Sheets(1)

' get name(s) of individual survey reply workbooks
SRfilenames = Application.GetOpenFilename( _
fileFilter:="Excel,*.xls", _
Title:="Select one or more survey reply files", _
MultiSelect:=True)

For SRFN = 1 To UBound(SRfilenames) ' GetOpenFilename array starts at 1
SRfilename = SRfilenames(SRFN)
Workbooks.Open (SRfilename)
Dim wbkSR As Workbook
Set wbkSR = ActiveWorkbook
Dim wshSR As Worksheet
Set wshSR = wbkSR.Sheets(1)

' copy set of answers into next column
For q = 1 To 13
wshReplies.Cells(q + 1, SRFN + 1).Value = wshSR.Cells(q + 1, 2).Value
Next


wbkSR.Activate
wbkSR.Close saveChanges:=False
Next

End Sub
==================================
How you analyse the replies depends on whether they need counting, averaging or whatever, but this should make it easier.