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