Extreme VB Scripting problems

[Ronald]

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

 

[crazybass2]

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

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

 

[Ronald]

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
TimeCP Time:HDCP RdyCP 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
TimeCP Time:HDCP RdyCP 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
TimeCP Time:HDCP RdyCP 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

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

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

 

[crazybass2]

Ok Ron, I think I found the trouble.

Within the second IF statement you have a line in your code 'Sheets("Jan
'05").Select' (shown below with the ----> in front of it). When you create
each sheet that sheet becomes active by default. I don't think you need to
select the sheet again. I've tested it without that line with the data you
sent me and it appears to function properly. I also duplicated the data and
changed the dates so that I have several months worth of data to work with.
Without that line it appears to run fine on my side.

You code read:

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 = 4
th 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
...etc.

Let me know if that's not it...
Mike