Gingertrees
Well-known Member
- Joined
- Sep 21, 2009
- Messages
- 697
(Reworded repost of http://www.vbaexpress.com/forum/showthread.php?t=39170 )
Overall questions (details to follow)
===========================
QUESTION 1: Is there a way for VBA to search for specific data in a text file, copy a set length to another text file, before opening in Excel?
QUESTION 2: Any ideas to accomplish my report in a better way?
Details:
========
I have to change the text data that the phone system spits out into meaningful, organized data for a report in Excel. I know my current method is clumsy, but even automating the clumsy method would help me.
(I'm open to ideas to do this better)
Big long text file with 15 reports (5 categories x 3 reports each). Currently I copy each report into a separate text file, and run a macro that opens each using text import, then copies into my main spreadsheet where needed.
But I still have to manually do the first part, copying from long text file to the individual ones. And I mix them up some times, omit ones... it's clumsy, I know.
Important facts:
* The indiv. reports are the same length each day - Position Perf. Widgets will always have 5 lines of relevant data, answrd calls sales will always have 23, etc
* but they come in a DIFFERENT ORDER every day - sometimes Widgets are at the top, sometimes Sales... sometimes the summaries come first, others answered calls...
Here are the three types of report:
So, assuming "Position Performance Widgets" could be anywhere in the dozens of lines of text, can I copy it somewhere daily, and save user errors? Input appreciated, VBA wizards...
Overall questions (details to follow)
===========================
QUESTION 1: Is there a way for VBA to search for specific data in a text file, copy a set length to another text file, before opening in Excel?
QUESTION 2: Any ideas to accomplish my report in a better way?
Details:
========
I have to change the text data that the phone system spits out into meaningful, organized data for a report in Excel. I know my current method is clumsy, but even automating the clumsy method would help me.
(I'm open to ideas to do this better)
Big long text file with 15 reports (5 categories x 3 reports each). Currently I copy each report into a separate text file, and run a macro that opens each using text import, then copies into my main spreadsheet where needed.
But I still have to manually do the first part, copying from long text file to the individual ones. And I mix them up some times, omit ones... it's clumsy, I know.
Important facts:
* The indiv. reports are the same length each day - Position Perf. Widgets will always have 5 lines of relevant data, answrd calls sales will always have 23, etc
* but they come in a DIFFERENT ORDER every day - sometimes Widgets are at the top, sometimes Sales... sometimes the summaries come first, others answered calls...
Here are the three types of report:
PHP:
ACD POSITION PERFORMANCE REPORT
FROM: July 29, 2010 08:00
TO: July 29, 2010 17:00
+------NUMBER OF CALLS------+ +---AVERAGE TIMES (MM:SS)---+ +--DISTRIBUTION OF LOGGED-ON TIME (PERCENT)---+
ACD EXTENSION ACD +----NON-ACD----+ +---ACD---+ +----NON-ACD----+ ACD +----NON-ACD----+
GROUP NUMBER ANSD INC OUT INT XFER TALK WORK INC OUT INT AVAIL UNAVL WORK IN INC OUT INT OTHER
-------- ------- ----- ----- ----- ----- ----- ----- ----- ----- ----- ----- ----- ----- ----- ----- ----- ----- ----- -----
WIDGETS 100 16 0 5 0 5 05:22 55:07 00:00 02:11 00:00 0.0 0.0 81.7 15.9 0.0 2.0 0.0 0.4
102 42 0 10 0 5 03:52 02:06 00:00 02:39 00:00 23.5 37.0 3.1 30.1 0.0 4.9 0.0 1.4
104 0 0 0 0 0 00:00 00:00 00:00 00:00 00:00 0.0 100.0 0.0 0.0 0.0 0.0 0.0 0.0
-------- ----- ----- ----- ----- ----- ----- ----- ----- ----- ----- ----- ----- ----- ----- ----- ----- ----- -----
TOTAL 58 0 15 0 10 04:17 28:37 00:00 02:30 00:00 7.8 45.7 28.3 15.3 0.0 2.3 0.0 0.6
L SEQUENCE # 00006
ACD SYSTEM SUMMARY REPORT
FROM: July 29, 2010 08:00
TO: July 29, 2010 17:00
ACD +-------------------------------NUMBER OF CALLS------------------------------+ +---------AVERAGE TIMES (MM:SS)---------+
GROUP +---RECEIVED----+ +---ANSWERED----+ +---ABANDONED---+ OVFL OVFL 1ST +-----TALK------+ +---ANSWERED----+
NAME TOT PRIM OVFL TOT PRIM OVFL TOT PRIM OVFL ACD OUT XFER RECORD TOT PRIM OVFL TOT PRIM OVFL WORK
-------- ----- ----- ----- ----- ----- ----- ----- ----- ----- ----- ----- ----- ----- ----- ----- ----- ----- ----- ----- -----
SALES 82 82 0 73 73 0 9 9 0 0 0 14 44 01:43 01:43 00:00 01:46 01:46 00:00 16:23
L SEQUENCE # 00005
ANSWERED-CALL PROFILE
ACD GROUP SALES
July 29, 2010
TIME NO. OF FROM % OF CALLS ANSW'D / AVG. TIME-BEFORE-ANSWER
OF CALLS OTH WITHIN X SECONDS / (SECONDS)
DAY ANSW'D ACD 10 20 30 40 50 60 / 0 10 20 30 40 50
---- ------ ---- --- --- --- --- --- --- / +----+----+----+----+----+
/ +
08:00-08:30 0 0 0 0 0 0 0 0 / 0 +
08:30-09:00 0 0 0 0 0 0 0 0 / 0 +
09:00-09:30 3 0 33 33 33 67 67 67 / 58 +XXXXXXXXXXXXXXXXXXXXXXXXX
09:30-10:00 4 0 0 0 0 0 0 0 / 160 +XXXXXXXXXXXXXXXXXXXXXXXXX
10:00-10:30 2 0 50 100 100 100 100 100 / 12 +XXXXXX
10:30-11:00 3 0 0 0 67 67 67 67 / 206 +XXXXXXXXXXXXXXXXXXXXXXXXX
11:00-11:30 6 0 0 17 17 33 33 33 / 146 +XXXXXXXXXXXXXXXXXXXXXXXXX
11:30-12:00 7 0 14 14 14 29 43 43 / 124 +XXXXXXXXXXXXXXXXXXXXXXXXX
12:00-12:30 5 0 20 20 20 20 40 40 / 57 +XXXXXXXXXXXXXXXXXXXXXXXXX
12:30-13:00 5 0 20 20 20 40 40 40 / 199 +XXXXXXXXXXXXXXXXXXXXXXXXX
13:00-13:30 8 0 0 13 13 25 25 25 / 270 +XXXXXXXXXXXXXXXXXXXXXXXXX
13:30-14:00 6 0 67 83 100 100 100 100 / 8 +XXXX
14:00-14:30 3 0 100 100 100 100 100 100 / 1 +
14:30-15:00 12 0 50 50 50 50 50 50 / 87 +XXXXXXXXXXXXXXXXXXXXXXXXX
15:00-15:30 4 0 100 100 100 100 100 100 / 3 +X
15:30-16:00 2 0 50 100 100 100 100 100 / 10 +XXXXX
16:00-16:30 2 0 100 100 100 100 100 100 / 2 +X
16:30-17:00 1 0 100 100 100 100 100 100 / 1 +
17:00-17:00 0 0 0 0 0 0 0 0 / 0 +
/ +
/ +----+----+----+----+----+
------ ---- --- --- --- --- --- --- /
TOTAL 73 0 36 42 47 53 56 56 / 106
L SEQUENCE # 00004So, assuming "Position Performance Widgets" could be anywhere in the dozens of lines of text, can I copy it somewhere daily, and save user errors? Input appreciated, VBA wizards...
