Hi Mike,
Attached the sample text file.

Meanwhile, I realised that I can run the text file if it is Feb05 onwards.
the only problem is it just can't recognised 01/01/2005.

Take your time as I had manually input the data for 01/01/2005 as a work
around

Tue Feb 1 17:16:21 2005 Page 1

ACD Agent Group Performance Periodic Totals

Date.......: 01/01/2005
Day of Week: Saturday
Queue Group:
__________________________________________________ __________________________________________________ __________________________________________________ __________________________________________________ ___
Period Avg Agents No. Calls Calls Avg Calls ASA TSF Avg
Talk Avg Hold Avg Not Avg Wait Ansd Longest Avg Abandoned No. Abandoned
DN Xfr/Conf Xfr/Conf Avg NonACD Avg Time Position
Ending Act'l Req'd Accepted Answered per Agent
Time:DCP Time:HDCP Rdy:PCP Time Wait Wait Calls
Calls on DN on ACD Call Time Busy Logged-In
______ ___________ __________ ________ _________ _____ ________
________ _________ ________ ________ ____________ ______________
_____________ _____ ________ ________ __________ _________________
ACD-DN...............: 45399
Queue Name...........:
Target Answer Time (for Avg Agents Req'd calculation): 0:20
Target TSF % (Telephone Service Factor)..............: 80.00%
0:30 1 1 2 2 2 0:20 50
0:35 0:00 0:00 14:23 0:24 **** 0
0 0 0 **** 1:10 29:56
2:00 1 1 1 1 1 0:18 100
2:06 0:00 0:00 27:54 0:18 **** 0
0 0 0 **** 2:06 30:00
3:30 1 1 5 3 3 0:30 40
1:43 0:00 0:00 7:45 0:58 0:51 2
1 0 0 1:34 6:44 30:00
4:00 1 1 1 1 1 0:24 0
11:08 0:00 0:00 18:52 0:24 **** 0
0 0 0 **** 11:08 30:00
4:30 1 1 2 2 2 0:15 50
3:44 0:00 0:00 11:16 0:22 **** 0
0 0 0 **** 7:28 30:00
5:30 1 2 2 1 1 0:08 50
3:16 0:00 0:00 13:36 0:08 1:40 1
6 1 0 1:39 16:24 30:00
6:30 1 1 1 1 1 0:14 100
1:14 0:00 0:00 28:46 0:14 **** 0
0 0 0 **** 1:14 30:00
7:00 1 1 2 2 2 0:21 50
1:40 0:00 0:00 13:20 0:32 **** 0
0 0 0 **** 3:20 30:00
8:00 1 1 1 1 1 0:14 100
0:44 0:00 0:00 31:16 0:14 **** 0
4 0 0 1:01 1:37 12:02
8:30 2 1 1 1 0 0:04 100
5:18 0:00 0:00 54:06 0:04 **** 0
0 0 0 **** 2:57 30:00
9:00 2 2 3 3 1 0:06 100
5:00 0:00 0:00 14:47 0:10 **** 0
1 0 0 0:38 7:49 30:00
9:30 2 1 1 1 0 0:04 100
1:10 0:00 0:00 58:50 0:04 **** 0
0 0 0 **** 0:35 30:00
10:00 2 1 2 2 1 0:07 100
2:32 0:02 0:00 25:58 0:10 **** 0
1 0 1 1:30 4:02 30:00
11:00 2 0 1 1 0 0:08 100
0:42 0:00 0:00 59:18 0:08 **** 0
0 0 0 **** 0:21 30:00
11:30 2 1 1 1 0 0:06 100
11:00 0:00 0:00 49:00 0:06 **** 0
0 0 0 **** 5:30 30:00
12:00 1 1 1 1 1 0:04 100
0:56 0:00 5:06 30:52 0:04 **** 0
3 0 0 0:19 3:29 18:55
13:00 1 2 2 1 1 3:12 50
2:46 1:30 0:00 3:36 3:12 0:06 1
2 0 1 7:52 26:24 30:00
14:30 2 1 1 1 0 0:02 100
4:22 0:00 0:00 55:12 0:02 **** 0
1 0 0 0:26 2:24 30:00
17:00 2 2 1 1 0 0:08 100
13:54 0:00 0:00 46:02 0:08 **** 0
0 0 0 **** 6:57 29:58
18:00 2 0 1 1 0 0:02 100
0:30 0:00 0:02 46:18 0:02 **** 0
0 0 0 **** 0:16 23:25
18:30 2 1 4 4 2 0:03 100
2:20 0:04 0:00 12:33 0:04 **** 0
0 0 1 **** 3:13 19:57
21:00 2 1 1 1 0 0:08 100
3:40 0:00 0:00 56:20 0:08 **** 0
0 0 0 **** 1:50 30:00
21:30 2 1 1 1 0 0:24 0
10:54 1:12 0:00 47:58 0:24 **** 0
0 0 1 **** 6:03 30:02
22:30 2 1 2 2 1 0:11 100
1:15 0:01 0:00 28:25 0:12 **** 0
1 0 1 0:20 1:35 30:00
24:00 2 1 2 2 1 0:12 100
1:03 0:01 0:00 58:19 0:14 **** 0
1 0 1 0:40 1:43 1:00:02
----- ----- ---------- ----- ----- ----- -----
-------- -------- -------- -------- -------- -------- -----
----- ----- ----- -------- -------- --------
SUB-TOTAL (for 80.00%):
2 1 42 38 1 0:17 76
3:17 0:05 0:08 27:36 8:46 0:52 4
21 1 6 1:42 4:24 29:56
SUB-AVERAGE (for 25 records):
76



----- ----- ---------- ----- ----- ----- -----
-------- -------- -------- -------- -------- -------- -----
----- ----- ----- -------- -------- --------
SUB-TOTAL (for 45399):
2 1 42 38 1 0:17 76
3:17 0:05 0:08 27:36 8:46 0:52 4
21 1 6 1:42 4:24 29:56
SUB-AVERAGE (for 25 records):
76



----- ----- ---------- ----- ----- ----- -----
-------- -------- -------- -------- -------- -------- -----
----- ----- ----- -------- -------- --------
SUB-TOTAL (for ):
2 1 42 38 1 0:17 76
3:17 0:05 0:08 27:36 8:46 0:52 4
21 1 6 1:42 4:24 29:56

Tue Feb 1 17:16:21 2005 Page 2

ACD Agent Group Performance Periodic Totals

Date.......: 01/01/2005 (cont.)
Day of Week: Saturday (cont.)
Queue Group: (cont.)
__________________________________________________ __________________________________________________ __________________________________________________ __________________________________________________ ___
Period Avg Agents No. Calls Calls Avg Calls ASA TSF Avg
Talk Avg Hold Avg Not Avg Wait Ansd Longest Avg Abandoned No. Abandoned
DN Xfr/Conf Xfr/Conf Avg NonACD Avg Time Position
Ending Act'l Req'd Accepted Answered per Agent
Time:DCP Time:HDCP Rdy:PCP Time Wait Wait Calls
Calls on DN on ACD Call Time Busy Logged-In
______ ___________ __________ ________ _________ _____ ________
________ _________ ________ ________ ____________ ______________
_____________ _____ ________ ________ __________ _________________
ACD-DN...............: 45399 (cont.)
Queue Name...........: (cont.)
Target Answer Time (for Avg Agents Req'd calculation): 0:20 (cont.)
Target TSF % (Telephone Service Factor)..............: 80.00% (cont.)
SUB-AVERAGE (for 25 records):
76



----- ----- ---------- ----- ----- ----- -----
-------- -------- -------- -------- -------- -------- -----
----- ----- ----- -------- -------- --------
SUB-TOTAL (for 01/01/2005):
2 1 42 38 1 0:17 76
3:17 0:05 0:08 27:36 8:46 0:52 4
21 1 6 1:42 4:24 29:56
SUB-AVERAGE (for 25 records):
76




Tue Feb 1 17:16:21 2005 Page 3

ACD Agent Group Performance Periodic Totals

Date.......: 02/01/2005
Day of Week: Sunday
Queue Group:
__________________________________________________ __________________________________________________ __________________________________________________ __________________________________________________ ___
Period Avg Agents No. Calls Calls Avg Calls ASA TSF Avg
Talk Avg Hold Avg Not Avg Wait Ansd Longest Avg Abandoned No. Abandoned
DN Xfr/Conf Xfr/Conf Avg NonACD Avg Time Position
Ending Act'l Req'd Accepted Answered per Agent
Time:DCP Time:HDCP Rdy:PCP Time Wait Wait Calls
Calls on DN on ACD Call Time Busy Logged-In
______ ___________ __________ ________ _________ _____ ________
________ _________ ________ ________ ____________ ______________
_____________ _____ ________ ________ __________ _________________
ACD-DN...............: 45399
Queue Name...........:
Target Answer Time (for Avg Agents Req'd calculation): 0:20
Target TSF % (Telephone Service Factor)..............: 80.00%
2:00 2 1 1 1 0 0:10 100
7:42 0:00 0:00 52:18 0:10 **** 0
0 0 0 **** 3:51 30:00
4:00 2 1 1 1 0 0:06 100
1:08 0:00 0:00 58:52 0:06 **** 0
0 0 0 **** 0:34 30:00
7:30 1 1 2 2 2 0:13 100
2:11 0:00 0:02 18:52 0:18 **** 0
0 0 0 **** 1:45 14:19
8:30 1 1 1 1 1 0:06 100
5:50 0:00 0:00 24:10 0:06 **** 0
0 0 0 **** 5:50 30:00
11:30 1 1 1 1 1 0:06 100
1:14 0:00 3:24 25:22 0:06 **** 0
0 0 0 **** 4:38 30:00
13:00 1 1 2 2 2 0:08 100
1:44 0:00 0:00 13:16 0:10 **** 0
0 0 0 **** 3:28 30:00
14:30 1 0 1 1 1 0:02 100
0:08 0:00 0:00 29:52 0:02 **** 0
0 0 0 **** 0:08 30:00
15:00 1 3 2 1 1 0:04 50
14:56 0:00 8:46 6:18 0:04 1:14 1
0 0 0 **** 23:42 30:00
15:30 1 2 1 1 1 0:06 100
18:12 0:00 0:00 11:48 0:06 **** 0
0 0 0 **** 18:12 30:00
16:00 1 2 2 1 1 0:06 50
13:28 0:00 0:00 16:32 0:06 0:46 1
0 0 0 **** 13:28 30:00
16:30 1 2 2 1 1 0:04 100
7:14 0:00 0:00 20:54 0:04 0:14 1
2 0 0 0:56 9:06 30:00
17:00 1 1 1 1 1 0:10 100
1:58 0:00 0:00 28:02 0:10 **** 0
0 0 0 **** 1:58 30:00
17:30 1 0 1 1 1 0:08 100
0:06 0:00 0:00 29:54 0:08 **** 0
0 0 0 **** 0:06 30:00
18:00 1 1 1 1 1 0:06 100
4:18 0:00 6:28 19:14 0:06 **** 0
0 0 0 **** 10:46 30:00
20:30 1 1 2 2 2 0:13 100
1:18 0:00 0:00 13:42 0:14 **** 0
0 0 0 **** 2:36 30:00
22:00 1 2 2 1 1 0:16 50
7:42 0:00 0:00 22:18 0:16 0:32 1
0 0 0 **** 7:42 30:00
23:00 1 1 1 1 1 0:10 100
2:08 0:00 0:00 27:52 0:10 **** 0
0 0 0 **** 2:08 30:00
24:00 1 1 7 1 1 0:22 20
0:52 0:00 0:00 59:08 0:22 0:34 4
0 0 0 **** 0:52 1:00:00
----- ----- ---------- ----- ----- ----- -----
-------- -------- -------- -------- -------- -------- -----
----- ----- ----- -------- -------- --------
SUB-TOTAL (for 80.00%):
1 1 31 21 1 0:09 72
4:38 0:00 0:53 24:58 2:44 0:38 8
2 0 0 0:56 5:46 30:43
SUB-AVERAGE (for 18 records):
72


Thanks & Regards
Ronald

"crazybass2" wrote:

Ronald,

This is a lot to review. I'd like to help, but it's hard to evaluate what
is going on without a sample of the text file. Could you post an example of
what the text file looks like?

Mike

"Ronald" wrote:

Hi Experts,
The following is the module that has been created.

What it does is the script import and reaad line by lines from a rpt file
and extract certain information into a new excel worksheet each time it runs.

There is a standard worksheet call "template" that it will copy and rename
the worksheet to " MMM 'YY"

I start facing problem when date as indicated below from the raw data.
Date.......: 01/01/2005
Day of Week: Saturday

The problem only happens when date value Year 2004.

It read the data correctly but it bypasses the copy template to template
(2), hence the rename of the worksheet will not work.

I do hope some excel experts out there can share some experience in
resolving this issue.

The script as follow

Sub Start()
Sheets("Abandon Calls (Rpt Mth)").Activate 'Select Worksheet
ActiveSheet.EnableCalculation = False 'Disable calculation
Sheets("Abandon Calls (12 mths history)").Activate 'Select Worksheet
ActiveSheet.EnableCalculation = False 'Disable calculation

Sheets("Setup").Activate 'Select Worksheet
RowCount = 11 'Read line from Row 11
Do
ReadIn (Sheets("Setup").Cells(7, 3).Value + "\" +
Sheets("Setup").Cells(RowCount, 3).Value) 'Run sub program Read File
RowCount = RowCount + 1
'Do loop untill
Loop Until (Sheets("Setup").Cells(RowCount, 3).Value = "")
'Row is empty

Sheets("Abandon Calls (Rpt Mth)").Activate
'Select Worksheet
ActiveSheet.EnableCalculation = True
'Enable calculation
Sheets("Abandon Calls (12 mths history)").Activate
'Select Worksheet
ActiveSheet.EnableCalculation = True
'Enable calculation
End Sub
Sub ReadIn(SrcFile)
LineRd = 0 'Line Read starting
from Line 0
Set fs = CreateObject("Scripting.FileSystemObject") 'Setup and create
the object
Set FF = fs.OpentextFile(SrcFile, 1) 'Setup FF as the
instance of the text file
Rep_Mnth = #1/1/2004# 'Assign variable
Rep_Mnth = 1/1/2004
Rpt_Mth = "" 'Assign variable
Rpt_Mth = ""
TmpV = "" 'Assign variable
TmpV = ""
Dim Field_Data(5) 'Declare each record
have 5 data

Do While Not (FF.AtEndOfStream) 'Do while source
file is not End of stream
LineRd = LineRd + 1 'Line + 1
Rec_Read = FF.ReadLine 'Temporary Store
Line in Rec_Read

If ((InStr(1, Rec_Read, ":") = 3) And (Right(ACD, 5) = "45399")) Then
'If Read Line contains 3 : or ACD 45399 then
If InStr(1, Rec_Read, ":") = 3 Then
'If Read Line contains 3 :
Rec_Tme = Right("00" + LTrim(Mid(Rec_Read, 5 - 4, 5)), 5)
'Trim the time
TmpV = RTrim(LTrim(Mid(Rec_Read, 78 - 4, 5)))
'Temporary value

Field_Data(1) = CInt(Mid(Rec_Read, 146 - 2, 3)) 'Assign
Abandon value to Field Data 1
Field_Data(2) = CInt(Mid(Rec_Read, 12 - 2, 3)) 'Assign
Actual Agent to Field Data 2
Field_Data(3) = CInt(Mid(Rec_Read, 49 - 2, 3)) 'Assign
Average Call per aent to Field Data 3
Field_Data(4) = Round(CInt(Left(TmpV, InStr(TmpV, ":") - 1)) +
(CInt(Right(TmpV, 2)) / 60), 2) 'Assign Average Talk Time b seconds to Field
Data 4
Field_Data(5) = CInt(Mid(Rec_Read, 39 - 2, 3)) 'Assign
call received to Field Data 5

If New_Mnth Then 'If It is new month
ActiveSheet.EnableCalculation = True 'Enable calculation
Sht_Found = False 'Assign Sht_Found value
= false
For TmpV = 4 To Sheets.Count 'For loop till Tmp =
4th sheet
If Sheets(TmpV).Name = Rpt_Mth Then 'If 4th sheet = Rpt_Mth
Sht_Found = True 'Assign Sht_Found value
= true
TmpV = Sheets.Count 'TmpV = sheet count
End If 'End if
Next

If Not Sht_Found Then 'If
Worksheets("Template").Copy After:=Worksheets("Template")
'Copy Template Worksheet
Sheets("Template (2)").Select
'Select Template(2)
Sheets("Template (2)").Name = Rpt_Mth
'Rename Template (2) to Rpt_Mth
End If
Sheets(Rpt_Mth).Activate
'Select Worksheet
Sheets(Rpt_Mth).EnableCalculation = False
'Disable calculation
End If

Sheets("Jan '05").Select
'Select Worksheet
Col_Indx = Day(Rec_Date) + 3
Cells(3, Col_Indx).Select
Row_Indx = 4 + (CInt(Left(Rec_Tme, 2)) + CInt(Right(Rec_Tme, 2)) /
60) / 0.5
For TmpV = 1 To 5
If (Field_Data(TmpV) 0) Then
Cells(Row_Indx + (TmpV - 1) * 50, Col_Indx).Value =
Field_Data(TmpV)
End If
Next
End If
Else
If (InStr(1, Rec_Read, "ACD-DN....") 0) Then
ACD = LTrim(Left(LTrim(RTrim(Right(Rec_Read, Len(Rec_Read) - 23))),
5))
Else
If ((InStr(1, Rec_Read, "Date.......:") 0) And (InStr(1, Rec_Read,
"(cont.)") = 0)) Then 'If read string starting after "Date...:" consist of
value and string with cont.
Rec_Date = LTrim(RTrim(Right(Rec_Read, Len(Rec_Read) - 13))) 'Get
date in format 01/01/2005
Rec_Date = Mid(Rec_Date, 4, 2) + "/" + Mid(Rec_Date, 1, 2) + "/" +
Mid(Rec_Date, 7, 4) '
'Returns a Variant (String) containing a specified number of
characters from a string.
'Syntax
'Mid(string, start[, length])

Rec_Date = CDate(Rec_Date)

If Not (Month(Rep_Mnth) = Month(Rec_Date)) Then
Rep_Mnth = Rec_Date
Rpt_Mth = Format(Rep_Mnth, "mmm 'yy")
Sheets("Setup").Cells(3, 3).Value = Rep_Mnth
New_Mnth = True
Else
New_Mnth = False
End If 'End If Not (Month(Rep_Mnth) = Month(Rec_Date))
End If 'Enf If ((InStr(1, Rec_Read, "Date.......:") 0) And
(InStr(1, Rec_Read, "(cont.)") = 0)) Then
End If
End If
Loop
'End Loop
FF.Close
'Close file
Sheets(Rpt_Mth).EnableCalculation = True
'Enbable Calculation
MsgBox ("Extraction of Data Completed, " + CStr(LineRd) + " lines
processed.") 'Completion Message
End Sub