Weekly Survey report from SurveyMonkey

Official name is "EA_R1105_Surv" as part of reporting system accross the department

Jun 2012
Internal ID:
PF378 (type1)
Big complete systems
VBA - Excel
Macros, or Visual Basic for Application tool

Automating SurveyMonkey to download, open, import, analyse and create weekly survey reports.
R1105.Surv creates Excel sheet per service line with all tickets data imported from SQL server and crossing them against the Resources file downloaded from PeopleSoft, then finally downloads and import individual ticket responses from SurveyMonkey, creating individual reports and then sends an email to service lines owners attaching these reports in addition to admin to watch for the process.
This was part of my work in Electronic Arts in system named (G.R.E.A.T. = Global Reporting for Electronic Arts Team).
The process used to take 8 hours with lots of errors and stops. Now the system takes 10 mins max.

It has two major parts:

  1. R1104.Sur.xlsm: Which will sends an email to ticket submitter once that ticket is closed (reading tickets from SQL database), this email has the request to take a survey about the service. (0:40 long video)

  2. R1105.Surv.xlsm: which will be run weekly or monthly to create weekly Survey reports per service line. (6:17 long video)

The new part is that R1105.Surv will automatically log in to SurveyMonkey website to download the responses for these surveys, unzip them, import them into Excel sheet before creating these reports