LinkBack URL
About LinkBacksHi 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
Register To Reply
Bookmarks