Data collection from different excel-files

Bubuto

New Member
Joined
Jul 24, 2002
Messages
15
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!
 

Excel Facts

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)

Iridium

Well-known Member
Joined
Jul 15, 2002
Messages
2,831
Look into consolidation (Data|Consolidate...) which should let you merge all the workbooks and process the responses when you get them back
 

Bubuto

New Member
Joined
Jul 24, 2002
Messages
15
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

New Member
Joined
Oct 16, 2002
Messages
6
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.
 

Forum statistics

Threads
1,143,707
Messages
5,720,386
Members
422,282
Latest member
psunith

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top