I use a fairly simple vba routine to extract data from a table contained in an Outlook email body. The result is an array which contains 1 entry with 7 columns for each email.
Here is a sample of one entry in the array:
arrList(1,1) = "Sent: Monday, August 28, 2017 10:07:28 AM (UTC-06:00) Central Time (US & Canada)"
arrList(1,2) = "Total Chats 10 7 "
arrList(1,3) = "Chats Completed 7 5 "
arrList(1,4) = "Chats Dropped 2 1 "
arrList(1,5) = "Chats Missed 1 1 "
arrList(1,6) = "Offline Messages 2 2 "
arrList(1,7) = "Avg Chat Duration 5m 15s 9m 13s "
Once the array is filled, I have to clean it up:
Just the date for column 1:
arrList(1,1) = "8/28/2017"
The first number in the set for columns 2-6:
arrList(1,2) = "10"
arrList(1,3) = "7"
arrList(1,4) = "2"
arrList(1,5) = "1"
arrList(1,6) = "2"
The total time in seconds from the first minute/second pair for column 7:
arrList(1,7) = "315" (5 minutes x 60 seconds plus 15 seconds)
(Note - if either pair is "0m 0s", it will be replaced by a dash: "- 9m 13s" instead of "0m 0s 9m 13s"
Right now I either do this using formulas in an excel spreadsheet, or by doing a series of manual operations on the data (CTL+H to strip out the text parts, for example).
I want to edit my VBA code so it cleans each element before loading it into the array. I can handle this with a combination of string operations (replace, split, test, join, etc.), but it takes a few steps for each element.
Which is going to be more efficient? A set of string operations on each element, or a RegEx to extract the data I need from each element? How do I determine whether the string contains tabs, spaces, or some other kind of separator?
Can you point me at a page which clearly explains how to build the RegEx if that's the best option?
(Note - typically this will be used with a pretty small data-set, so being easy to understand and modify is probably more important than being as fast as possible).
Thanks,
-geodekl
Here is a sample of one entry in the array:
arrList(1,1) = "Sent: Monday, August 28, 2017 10:07:28 AM (UTC-06:00) Central Time (US & Canada)"
arrList(1,2) = "Total Chats 10 7 "
arrList(1,3) = "Chats Completed 7 5 "
arrList(1,4) = "Chats Dropped 2 1 "
arrList(1,5) = "Chats Missed 1 1 "
arrList(1,6) = "Offline Messages 2 2 "
arrList(1,7) = "Avg Chat Duration 5m 15s 9m 13s "
Once the array is filled, I have to clean it up:
Just the date for column 1:
arrList(1,1) = "8/28/2017"
The first number in the set for columns 2-6:
arrList(1,2) = "10"
arrList(1,3) = "7"
arrList(1,4) = "2"
arrList(1,5) = "1"
arrList(1,6) = "2"
The total time in seconds from the first minute/second pair for column 7:
arrList(1,7) = "315" (5 minutes x 60 seconds plus 15 seconds)
(Note - if either pair is "0m 0s", it will be replaced by a dash: "- 9m 13s" instead of "0m 0s 9m 13s"
Right now I either do this using formulas in an excel spreadsheet, or by doing a series of manual operations on the data (CTL+H to strip out the text parts, for example).
I want to edit my VBA code so it cleans each element before loading it into the array. I can handle this with a combination of string operations (replace, split, test, join, etc.), but it takes a few steps for each element.
Which is going to be more efficient? A set of string operations on each element, or a RegEx to extract the data I need from each element? How do I determine whether the string contains tabs, spaces, or some other kind of separator?
Can you point me at a page which clearly explains how to build the RegEx if that's the best option?
(Note - typically this will be used with a pretty small data-set, so being easy to understand and modify is probably more important than being as fast as possible).
Thanks,
-geodekl
