Macro to Tally Survey Results from Multiple Workbooks in a Folder

[KeNz-]

Hi guys,

I'm currently conducting a survey through Excel workbooks (using drop-down lists mostly). I would like to know how to use macros to tally up the results/replies in the workbooks into one single master sheet. In other words, to use macros to extract the results/replies in the survey workbooks instead of going through each of them individually and then tallying them up. There are about 200-300 workbooks that are in my folder currently. The folder directory is: C:\Client Survey 2011. All of the workbooks located inside are listed under Client Survey 2011 (*company's name*).xlsx.

There are multiple sheets within each workbook that contains the survey questions. E.g.:

Sheet 2: D5 = Client's Name, D6 = Dealer's Name, etc
Sheet 3: E6 = Drop Down List Answer, E9 = Drop Down List Answer, D21 = Overall Comments, etc

The questionnaire itself is rather confidential, so I know it would be difficult since I can't really post any Excel sheets up. But if necessary, I could make a sample sheet of how the questionnaire is like if it helps.

Currently, I'm testing out this macro to see if it works, but I keep running into an error which says: "Subscript out of range".

Code:

Please Login or Register  to view this content.

Although I'm not really too sure what exactly most of the code does (taken it from one of Google searches). When I debug, it returns the error right about Set wks = wkb.Worksheets(Results"). Although in the master sheet file, there is a sheet named Results, so I'm not too sure why I'm encountering this error. Hope someone here could help me. Thanks for looking into my query. Any help is much appreciated. Thanks.

[JapanDave]

What is this line of code for?

Please Login or Register  to view this content.

I would delete this line and see how the macro runs.

But other than that, without a workbook to see what you are doing, it makes it difficult.

[KeNz-]

Hi JapanDave,

Thanks for replying. I have no idea what that macro exactly does. I only picked it from one of the searches on Google hoping it would work. Sorry, I'm a real macro noob when it comes to this. Only started picking up VBA like two weeks ago but can't really find anything related to extracting files or workbooks from a folder.

Essentially the survey workbook is like this (Sample.xlsx):

Sample.xlsx

So, there are about 200-300 workbooks that I will be sending out and based on previous feedback, the expected number that will complete the survey is 90-95% of that. Once I get back the workbooks after they are filled out, I'd like to use a macro to sort out the answers in a master sheet like (Sample 2.xlsx):

Sample 2.xlsx

So, if I put all the workbooks under a folder C:\Client Survey 2011\Returned, how do I go about setting a code to obtain the desired result that I'm looking for? I'm only good at using IF functions in Excel and VBA to run events that has a tournament/league format style, so this is completely beyond what I know. Any help would be much appreciated. Thanks! (:

[JapanDave]

Hey,

Take this workbook and set it on your desktop. Put a couple of files you want the data from in a folder(Try 10 or so) and name the folder anything you like.
You have to copy the path to the folder into the Macro. The path should end with the folder name.

If you don't know how to change the path, copy the path to your folder , post it up and I will replace it for you.

Here is the workbook.

Dave

[KeNz-]

Thank you so much! It works absolutely brilliantly. (:

Are there any recommended threads in this forum where I can learn a little more about these things? Such as the Dim, LR, Left functions?

So, if I have additional sheets I just have to add them as:

Set ws3 = Active.Workbook("Transactions")

And then the results under:

.Cells(LR, 9).Value = ws3.Cells(7, 5).Value

if the result is located under the sheet Transactions in cell E7 and I want it to be pasted in the master sheet under column I. Is that right?

Thanks for your help.

[JapanDave]

You will have to make sure that the destination is correct, but essentially , yes you are correct.
If you liked the answer please click the star in the bottom left hand corner and mark your thread closed, as per forumn rules.

[dtlopez]

Hello guys,

im new to the forum and quite pleased with my first search. Not sure I can write on a closed post???
however my try with this macro does not work.

i have changed the questionnaire to fit my purpose and when I run the macro I get a "already open file message" then it crashes each time.

I attach the file and a sample of a questionnaire.

Thanks in advance for ur help :cool: