Over the years, I have often been asked to run a survey, or been asked to help someone else who has already run the survey.
My first peice of advice with surveys, is that they can be fun, but preparation up-front pays off in spades! Ensuring that the design of the survey is suitable, that you’ve thought about what you’re going to do with the responses is critical.
You might have reasons however, not to use them, and this might include that you want to send customised data to each participant, and you don’t want this information ‘in the cloud’. And you don’t have time or resources to setup your own platform using something like limeSurvey.
Then, people usually fall back to either email reply surveys, or excel or word. Word does have forms, which can be automated to great effect, but then, excel can do calculations etc on the fly, which means you can show people results to somethings that they’re answering, and creating something of value to the participants in the survey.
When you get the spreadsheets back from your participants, how do you aggregate all of the data into a research ‘database’ ?
Well, this is my relatively quickly built solution:
I’ll spend some time walking through my idea for this.
It does require some thinking about the survey design in excel.
Firstly, every spreadsheet that is returned to you via email (or dropbox, or whatever) must be saved in the same directory (I’d suggest a naming convention when you send them out, but don’t expect it to have the same name when it’s returned!).
Secondly, every spreadsheet returned should only be for one user, and have a ‘primary ID’ somewhere in the document, this could be editable by the participant, but it is your key identifier (you could create your own hidden one if you wished though, and I’d suggest this is good practice).
Once you’ve laid out your ‘survey’ spreadsheet in order, you’ll be expecting certain questions to be located in certain cells. Lock this down!!!
Then, in my master extractor, you need to tell it where to expect to find data, what it’s called, and where to put it.
Then you run the macro, and you should get a new workbook, populated with all the data from your input/source/survey spreadsheets.
Feel free to use /edit / adapt this for yourself, but feel free to leave a comment if you do!
On the first sheet 4 columns that represent the data to be collected. eg.
IMPORTANT! The first row, represents the primary ID, or the thing that is going to separate your results from each file. Forgetting this will mean you cannot track the data to a source spreadsheet/user/survey participant.
Sheet Name (Source) is the name of the sheet in your survey workbook from where you are picking up the data (as the program loops over all the surveys in the directory).
Cell Ref (Source) is the cell reference where the value is being picked up from in the source survey
Data Name (Dest) is what you want to call the data, eg. “question 8”, this is limited only by your imagination, but descriptive names will make your analysis easier
Destination Sheet (Dest) is the sheet where you want the output data to go. You might just put all of your data onto the one page, but sometimes you want to separate your data into different tables / groups.
An example of this is that you might want to separate demographic data from survey data in the survey.
In relational database design, normalising tables so that data of one type sits in it’s own table, but is linked by a primary key, is quite common, this will help you use MS Access, or SQLite, or PostGreSQL, or similar to analyse your data – because you could link the Age of all survey participants (from Table A) with the answers to their survey (from table B) with a simple query. This would be much more difficult if you put all of the information in the same table.
The macro follows a pretty basic pattern:
- Get the directory for the files
- Read in the things we need to grab into a dictionary object
- Open a new ‘write’ workbook
- Loop over all the files in the directory, looking for each item in the dictionary, finding the value, and placing it into the ‘write’ workbook, keeping track of what row we’re up to
Option Explicit Function getDirName() ' This function pops up a file dialog for picking which folder you want to do your extract from Dim bob As Object Set bob = Application.FileDialog(msoFileDialogFolderPicker) bob.AllowMultiSelect = False bob.Show getDirName = bob.SelectedItems(1) End Function Sub Main() 'This is the main routine, it grabs all of the surveybooks, searches through them for the data as outlined on sheet 1 in this book ' and then pushes them into a new workbook Dim OutputBook As Workbook Dim SurveyBook As Workbook Dim MainBook As Workbook Dim ReadItems As Range Dim SheetNames As Dictionary Dim ShtNmeList As Range Dim ShtNmes2 As Variant Dim x As Integer Dim item As Variant Dim NameVar As String Dim TempVar As String Dim fs As FileSystemObject Dim fldr As Folder Dim f As File Dim folderspec As String Dim y As Integer 'Define SurveyBook Directory folderspec = getDirName() If folderspec = "-1" Then GoTo Finish Set fs = CreateObject("SCripting.filesystemobject") Set fldr = fs.GetFolder(folderspec) 'Make sure we track the current workbook, for readitems Set MainBook = ActiveWorkbook 'Define Extract Details for iteration (in readItems), uses ranges + selection end points MainBook.Activate MainBook.Sheets(1).Range("A2").Select MainBook.Sheets(1).Range(Selection, Selection.End(xlDown)).Select MainBook.Sheets(1).Range(Selection, Selection.End(xlToRight)).Select Set ReadItems = Selection 'Define Sheetnames from Mainbook inside OutputBook 'Uses the dictionary object from the scripting reference. Enables quick checking if you've seen something before, a bit like a collection Set SheetNames = CreateObject("Scripting.Dictionary") For Each ShtNmeList In ReadItems.Rows If Not (SheetNames.Exists(ShtNmeList.Cells(1, 4).Value)) Then SheetNames.Add ShtNmeList.Cells(1, 4).Value, 1 End If Next 'Define NewBook for extract details Set OutputBook = Workbooks.Add 'after getting the required sheet names above, creates (or renames if a sheet already exists) those sheets in a new book x = 1 For Each ShtNmes2 In SheetNames.Keys If x <= 3 Then OutputBook.Worksheets(x).Name = ShtNmes2 x = x + 1 Else OutputBook.Worksheets.Add().Name = ShtNmes2 End If Next 'Now do the extraction, loop through the files in the directory 'and for each file, get all the details as outlined in readItems (this books lookup reference for all surveys) x = 1 For Each f In fldr.files Set SurveyBook = Workbooks.Open(f.Path) SurveyBook.Activate For Each item In ReadItems.Rows If x = 1 Then 'define the primary key / unique ID for each survey sheet NameVar = SurveyBook.Sheets(item.Cells(1, 1).Value).Range(item.Cells(1, 2).Value).Value x = x + 1 Else 'get variable from survey TempVar = SurveyBook.Sheets(item.Cells(1, 1).Value).Range(item.Cells(1, 2).Value).Value 'put variable into output y = SheetNames(item.Cells(1, 4).Value) OutputBook.Sheets(item.Cells(1, 4).Value).Range("A" & y).Value = NameVar OutputBook.Sheets(item.Cells(1, 4).Value).Range("B" & y).Value = item.Cells(1, 3).Value OutputBook.Sheets(item.Cells(1, 4).Value).Range("C" & y).Value = TempVar x = x + 1 'conveniently track the row number that we're up to in the output sheet, using the dictionary, so we don't have to search the sheet each time SheetNames.item(item.Cells(1, 4).Value) = SheetNames(item.Cells(1, 4).Value) + 1 End If Next x = 1 SurveyBook.Close (False) Next Finish: Set OutputBook = Nothing Set SurveyBook = Nothing Set SheetNames = Nothing Set item = Nothing Set ReadItems = Nothing Set fldr = Nothing Set f = Nothing Set fs = Nothing End Sub