Arg! never let on to people in work that your a dab hand at IT! I’ve been asked to come up with a solution for getting a survey out across 3,000 people. my idea was some sort of web page / ASP connected to mySQL database or something similar to record the results. i wouldn’t know where to start however implementing this? (I’ve done databases connected though .NET applications but not web interfaces) Any better, ideally simpler solutions? (the chap that will need to use the results isnt tech savvy) Thanks for any help guys.
I think your first solution is on the money, but i'd probably try to use php over ASP buts that just personal preference. Once you have the results build an interrogation tool for the non tech guy to get the results from the database, i'd probably put an access front end on your mysql server to prevent scaring the guy but still give you plenty of options if the guy needs more complicated queries.
Some past personal experience for you... I had a similar project back when I worked in the city and had 7000 out of 9500 staff doing a survey on our intranet (that was designed in a couple of hours using ColdFusion onto an Access database!) Fundamental to place yourself in a 'business overview' and remember that every 10 seconds you save in the time it takes each person to do the survey is, over 3000 staff, ONE MAN DAY of work saved/not-wasted (ie 10s x 3,000 = 30,000s = 500m = 8.3h). I suggest two things: a. If you don't have the inhouse capability, then providing the data isn't confidential, just use one of the web-based services - shouldnt cost more than £150 for 3000 surveys ie 5p/person compared to what cost of their time ? b. SPEND as much of the budget as possible on making the questions/answers as TUNED as possible By TUNED I mean from both consumer and inputter ends: - purposeful in that whoever is going to use the information from them is getting USEFUL data - easy to complete, without ambiguity, coping will all possible answers and fundamentally ATTRACTIVE to do The real issues we had were: 1. How to validate WHO people are - in our context we asked for their staff number (but didnt validate it in any way) and their email address - and sent that email address a copy of the data completed. 2. How, if at all, to cope with PARTIAL completion of the survey (we chose NOT to allow partial completion, but just to say that until they pressed final submit they had to start again) 3. Remember to cope with people who cant/wont do online and have a printed/PDF version of the questions/survey ready for some, and with someone geared up to key the info in. Hope thats helpful
Thanks for your input guys, went for a more simple solution in the end (the budget for this is zero) used a modified vb script, courtesy of a chap on hexus. incase anyone else needs something similar, the script bellow will pull any information you require from all worksheets contained the same folder as the script. the output file results.txt is csv Code: 'batch process all .xls files in the same directory where 'this script is located, "results.txt" and "errors.txt" option explicit call main() private sub main() dim filePath dim fileNames dim resultsFile, errorsFile call checkengine() filepath = trim(replace(wscript.scriptFullName, wscript.scriptName, "")) if filepath = "" then call wscript.echo("ERROR: Failed to determine path") exit sub end if if not getFiles(fileNames, filePath, resultsFile, errorsFile) then exit sub call wscript.echo("Found " & ubound(fileNames) + 1 & " file(s) to process") call processBooks(filePath, fileNames, resultsFile, errorsFile) call resultsFile.close() call errorsFile.close() set resultsFile = nothing set errorsFile = nothing call wscript.echo("") call wscript.echo("Press RETURN to continue...") call WScript.StdIn.Read(1) end sub private sub checkengine() dim pcengine, wshshell pcengine = LCase(Mid(WScript.FullName, InstrRev(WScript.FullName,"\")+1)) If Not pcengine="cscript.exe" Then Set WshShell = CreateObject("WScript.Shell") WshShell.Run "CSCRIPT.EXE """ & WScript.ScriptFullName & """" WScript.Quit End If end sub private function getFiles(byref fileNames, byval filePath, byref resultsFile, byref errorsFile) dim fso, folder, files, file getFiles = false set fso = wscript.createObject("scripting.fileSystemObject") set folder = fso.getFolder(filePath) set files = folder.files for each file in files if right(file.name, 4) = ".xls" then if isEmpty(fileNames) then redim fileNames(0) fileNames(0) = file.name else redim preserve fileNames(ubound(fileNames) + 1) fileNames(ubound(fileNames)) = file.name end if end if next set resultsFile = fso.openTextFile(filePath & "results.txt", 2, true) set errorsFile = fso.openTextFile(filePath & "errors.txt", 2, true) getFiles = true set files = nothing set folder = nothing set fso = nothing end function private sub processBooks(byref filePath, byref fileNames, byref resultsFile, byref errorsFile) dim excel, excelsheet dim i, j dim str set excel = wscript.createObject("Excel.Application") excel.DisplayAlerts = 0 ' don't display any messages about documents needing to be converted from old Excel file formats str = "Original File Name,Sheet Number,Cell A1," str = str & "Cell A2," str = str & "Cell A3," str = str & "Cell A4," str = str & "Cell A5," str = str & "Cell A6," str = str & "Cell A7," str = str & "Cell A8," str = str & "Cell A9," str = str & "Cell A10," call resultsFile.writeLine(str) str = "Workbook,Worksheet,Error" call errorsFile.writeLine(str) for i = 0 to ubound(fileNames) call wscript.echo("Processing workbook: " & fileNames(i)) call excel.workbooks.open(filePath & fileNames(i), false, true) ' open the excel document as read-only open (path, confirmconversions, readonly) for j = 1 to excel.worksheets.count call wscript.echo("Processing worksheet: " & j & " of " & excel.worksheets.count) set excelsheet = excel.activeWorkbook.worksheets(j) str = fileNames(i) & "," & excelsheet.name & "," str = fileNames(i) & "," & excelsheet.name & "," str = str & excelsheet.range("A1").value & "," str = str & excelsheet.range("A2").value & "," str = str & excelsheet.range("A3").value & "," str = str & excelsheet.range("A4").value & "," str = str & excelsheet.range("A5").value & "," str = str & excelsheet.range("A6").value & "," str = str & excelsheet.range("A7").value & "," str = str & excelsheet.range("A8").value & "," str = str & excelsheet.range("A9").value & "," str = str & excelsheet.range("A10").value & "," call resultsFile.writeLine(str) set excelsheet = nothing next call excel.workbooks(1).close next call excel.quit() set excel = nothing end sub