Blade Hunter
Well-known Member
- Joined
- Mar 13, 2008
- Messages
- 3,147
Nearly 3,000 posts and I only just found out about the lounge (thanks vcoolio 
)
So I thought in celebration of my upcoming 3K I could start a thread to VBA projects you have worked on or designed which is outside the bounds of "normal"
For example, I built an end to end reporting system called ARES (Automated Response Email System). ARES was designed as an auto response system to stop me from having to run 30+ report requests per day.
The basic structure was:
Email came in to ARES@company.com
VBA code in outlook on a VM interpreted the email then posts a request to a database table
Secondary process an Excel VBA process call the RRC (Remote Report Controller) ran as a scheduled task once a minute on the VM, it looked for requests and ran the one with the lowest ID number, first off it changed a flag from 0 to 1 to show instead of pending it was running, then it ran the report, then it emailed it out then finally set the flag to 2 for complete.
What made this so out of the bounds of "normal" that I built this thing to work on common English. So someone could email it "How many units did we sell last week of ArtistName Product Title" and it would work out what it all meant.
At its peak it was pushing 20 reports a day so nothing massive but when I stress tested it I fired 20 requests in a minute and it dealt just fine.
The key was having the 2 stage process, this mean outlook very quickly posted the request then quit back to waiting for emails no matter how long Excel got tied up running a report, another RRC process would fire off 1 min later to run subsequent requests.
The plan was to then set up trigger events in the database so when new sales figures were ingested a flag could be set for the RRC to run daily reports, this way they were run and emailed out as soon as data was made available to us. I built the code for it but got made redundant shortly before I was going to release V2
.
Lastly I put a little treat in there for my team, people were always sending us a bunch of product codes or UPC's without details, all they needed to do was forward it to ARES with the title GetProducts and it would send back a spreadsheet with the products and all details in within about 10 seconds
.
I do actually miss working on that system, it was my design, theory, creation and code.
Happy to share bits with anyone that is interested. Particularly the routine to work out date ranges from common phrases, that bit was fun to build.
)So I thought in celebration of my upcoming 3K I could start a thread to VBA projects you have worked on or designed which is outside the bounds of "normal"
For example, I built an end to end reporting system called ARES (Automated Response Email System). ARES was designed as an auto response system to stop me from having to run 30+ report requests per day.
The basic structure was:
Email came in to ARES@company.com
VBA code in outlook on a VM interpreted the email then posts a request to a database table
Secondary process an Excel VBA process call the RRC (Remote Report Controller) ran as a scheduled task once a minute on the VM, it looked for requests and ran the one with the lowest ID number, first off it changed a flag from 0 to 1 to show instead of pending it was running, then it ran the report, then it emailed it out then finally set the flag to 2 for complete.
What made this so out of the bounds of "normal" that I built this thing to work on common English. So someone could email it "How many units did we sell last week of ArtistName Product Title" and it would work out what it all meant.
At its peak it was pushing 20 reports a day so nothing massive but when I stress tested it I fired 20 requests in a minute and it dealt just fine.
The key was having the 2 stage process, this mean outlook very quickly posted the request then quit back to waiting for emails no matter how long Excel got tied up running a report, another RRC process would fire off 1 min later to run subsequent requests.
The plan was to then set up trigger events in the database so when new sales figures were ingested a flag could be set for the RRC to run daily reports, this way they were run and emailed out as soon as data was made available to us. I built the code for it but got made redundant shortly before I was going to release V2
.Lastly I put a little treat in there for my team, people were always sending us a bunch of product codes or UPC's without details, all they needed to do was forward it to ARES with the title GetProducts and it would send back a spreadsheet with the products and all details in within about 10 seconds
.I do actually miss working on that system, it was my design, theory, creation and code
.Happy to share bits with anyone that is interested. Particularly the routine to work out date ranges from common phrases, that bit was fun to build.
