| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Extreme VB Scripting problems
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 |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Extreme VB Scripting problems
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 |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Extreme VB Scripting problems
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 "Ronald" wrote: 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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| esudden extreme slowdown of charting | Charts and Charting in Excel | |||
| Extreme, time-consuming processing | Excel Discussion (Misc queries) | |||
| Conditional Formating Extreme Question | Excel Worksheet Functions | |||
| How can I minimalize the difference between extreme values on a c. | Charts and Charting in Excel | |||
| Sharing macros - extreme newbie question I know. | Excel Programming | |||
Linear Mode
