VB Script Start

[pentopad]

Good Afternoon,

I have been getting back into VB Script for the first time in a loong while and could use some help with the logic and/or code for a custom solution I need to create.

Basically I have an email alert that is sorted into a folder in my outlook. From there, I have an Access file that I have automatically syn with that Outlook folder. From there, I export the data to excel whenever I want to run a report.

Once in excel, I use the Countifs formula to count the number of occurrences, which automatically feeds graphs I have. Everything is essentially automated in this, which is great.

The problem I have, is another critical piece of Data, the total time of the incident, I currently only know how to do this manually, by doing a sort by name, then by time, then subtract the incident end time by the begin time, and add 30 minutes for the trigger time for the event to have been emailed in the first place.

It works, but is too cumbersome to be performed on a production scale. I know some of the aspects of the logic, and presume part of it can be handled by if/then statements, however, it has just been too long since I used these skills, 20 plus years long since I wrote in Basic.

I know I need to search for the subject line of the email to begin the process, which I had the person setting up the alerts set to the office location, because that location will have the starting event/ time email, then the ending event/ time email associated with it.

My main issue is not knowing what to research to begin to put this code together. If anyone had any pointers it would be greatly appreciated.

 

[pentopad]

Update:

I went through to create a Rough Draft Logic breakdown Plan for the potential script:



VB Script Plan
1) Event type, a Button inside of the active worksheet, with an on-click event to run script.
2) Choose Active Worksheet: 'Alert Test Query'!
3) Choose Active Cells: Columns A:C
4) Sort: by Column C (C1 ‘Subject’) Alphabetical A to Z, then by Column B (B1 ‘Received’) Oldest to Newest
5) Begin Time Calculations:
In row 2, look for 1st unique site name, located in Column C (e.g., Agnews in cell C2), then find received time (e.g., 4/27/2012 3:51:39 PM), store time for first value for calculation, confirm event is the initiating event (90 for over 30). In this case it was, so there would be no else event, however, the step would need to be repeated until the first initiating event (90 for over 30) were found, and in the case where you do not find the initiating event first you would need to ignore the other rows for calculation purposes.
6) In row 3, look to see if there is a closing event (Normal) in Column A, for the prior initiating event. In this case there is a second initiating event (90 for over 30), so because we have two start times in a row, and no corresponding end time to the first start time the first start time, and corresponding data, in row 2 will be ignored for calculation purposes and the current row, row 2, will be the initiating event whose time, in this case 4/29/2012 9:54:48 PM, will be recorded as the start time for the new event.
7) In row 4, look to see if there is a closing event (Normal) in column A for the initiating event. In this case there is a closing event. So, after confirming that the subject lines, in this case Agnews, match, the end time of the event, from Row 3, will be recorded.
8) The calculation for a confirmed, completed pair is end time, minus start time + 30 minutes (the initial time that lapses before the event email is triggered), as represented by the formula, in this case, of =SUM(B4-B3+0.03). This Data can go wherever, perhaps in column D.
9) The next step will then be to add up all the values for column D, for corresponding values in column C that match each other, in this case there was only one event for Agnews, so it would be the sum of all the events for Agnews during the reporting time period. These values could go anywhere, say column E.
10) Each of the 97 locations are broken down into three data worksheets, named for the time zone they fall into: EST, CST & PST/MST. In the case of Agnews, it lies in the PST/MST worksheet, so, on that worksheet there is a permanent location for each of the sites, in this case, Agnews is found at: 'PST-MST - DATA'!B5:F5, the total time for the event at Agnews then is located at 'Alert Test Query'!E2 and will then be needed to be fed to the permanent cell for the total event time for Agnews at: ‘PST-MST - DATA'!F5, which will then feed the appropriate reports. Since there is no permanent location for the total outage time for Agnews events, and sometimes there may be no events, so there would need to be some sort of if/ then statement; starting by finding Agnews in Column C, confirming that there was a initiating w/ corresponding ending events in column A, then taking the total for said sites in Column E to be fed somehow to its permanent location on workseet 'PST-MST - DATA'! as previously described.

Column A Column B Column C Column D Column E
R1 From Received Subject Time Site Time
R2 90 for over 30 27-Apr-12 Agnews 0:00:00 // Row Ignored
R3 90 for over 30 29-Apr-12 Agnews 1:03:14 1:03:14 // Initiating Event
R4 Normal 29-Apr-12 Agnews // Ending Event