Deploying excel workbook with VBA to web app

[MarkAlletson]

I am a the director of Maths in a school in the UK.
I am looking for an online solution for my staff to produce feedback sheets for students in their classes.
Currently I have an excel spreadsheet with some VBA code that enbales the teachers to input the scores that students get in a given assessement - then they access the macro which merges the information into a word document and emails the results as apd to both themselves and me.
However as the school is run on a network i am unable to allow the macro to delete files and open other applications (outlook/word). It works fine on my private PC but i don't want to have this sitting in the office all day waiting for them to use it.
I would really like a web application to enabe them to upload their file complete the relevant merging and then respond by allowing them to download to the appropriate pdf file containing all the individual student data.


I apologise if this is the wrong place to ask -but would really appreciate your help. Either by saying its not possible - telling me what web app code i need to learn - if it would be worth the time to learn any additional code for this one puropse - etc...


I would really appreciate any help/advice you can give me
I dont know if i can attach the files to show -if it will help let me know how


Mark

 

[Kyle123]

Hi Mark,

What you want isn’t doable directly, however here are some options:

1. Have a web developer write this for you, this is likely expensive, but probably the only way you’ll get this done as you describe
2. Do it in access, you have a database on a network drive that is accessible by the teachers, they open this and submit the results (maybe using a form) and all your data is in one place and merged. Outputting reports is easy as access is designed to do it - you probably wouldn’t need any vba
3. This is where it starts getting a bit messy. It’s possible to hack the access option with Excel by having the spreadsheet sent out to teachers update a central spreadsheet on a shared drive that they can all access. You’d have to write vba to do all the merging and export the data back, you’d produce the pdf report within the workbook sent to the teachers
4. If there’s no central location they can all access, or you don’t want them to have full access to all the data, then it’s possible to expand on option 3. They save their spreadsheets to shared locations they can access (department shared drives for example). Your master workbook then uses vba to loop through all these locations and merge the data.

3 and 4 are a bit hacky, excel isn’t designed to do this. There are probably subscription services on the web that will do this for you where they provide a form and all the data is merged and avaiblable as a spreadsheet. However, this means pupil data leaving your site and most of these services are outside the EU and many are not GDPR compliant