ANmarS - Portfolio - EA_R1105_Surv

Excel/VBA expert since Sep 1997 (21 years 2 months)

We are moving ...

We are moving to another location, better, faster and more resources .... check it out ANmar.Systems

EA_R1105_Surv    Added: 12/6/2012 | Created: 6/20/2012   

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

Screen shots (15)