If you have tasks in Microsoft Excel that you do repeatedly, you can either record a macro to automate those tasks or use the one (via import option) that we have shared on this article.
Prerequisite
Windows
Enable access to run/view Macros - Macros and VBA tools can be found on the Developer tab, which is hidden by default, so the first step is to enable it. For more information, see Show the Developer tab.
Mac
Make sure the Developer tab is visible on the ribbon. By default, the Developer tab is not visible, so do the following:
Go to Excel > Preferences… > Ribbon & Toolbar
In the Customize the Ribbon category, in the Main Tabs list, select the Developer check box, and then click Save
Get Leg1 & Leg2 Duration
You may’ve noticed that the call reports that you download from your Exotel dashboard, contains a PriceDetails column, with an array of data that also includes the Duration (in seconds) for either Leg1 ('From' number) or Leg2 ('To' number) or both
Note that for a ‘completed’ incoming call, you’d only get to see Leg2 duration
Once you have a call report that you'd want to analyse, follow through the below steps to achieve automation -
- Download LegDuration.bas
- Click on 'Visual Basic' from the 'Developer' tab
- Go to File > Import File...
- Choose the macro that you've downloaded and then return to the Exotel call report
- Finally, click on Macros, select the LegDuration macro and hit Run
Note: This macro has been created and optimised for Microsoft Office 2016 but we expect it to work on all latest versions of Excel and while we would not be able to support any issues or errors that you may encounter while using this, do please feel free to play around the source code to see if you can find a fix yourself.
Sub LegDuration() ' ' LegDuration Macro ' ' Keyboard Shortcut: Ctrl+Shift+L ' Columns("O:O").Select Selection.Copy Columns("S:S").Select ActiveSheet.Paste Application.CutCopyMode = False Columns("S:S").Select Selection.Replace What:="Divert", Replacement:="Exc", LookAt:=xlPart, _ SearchOrder:=xlByRows, MatchCase:=False Selection.TextToColumns Destination:=Range("S1"), DataType:=xlDelimited, _ TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _ Semicolon:=True, Comma:=False, Space:=False, Other:=False, FieldInfo _ :=Array(Array(1, 1), Array(2, 1), Array(3, 1), Array(4, 1), Array(5, 1)), _ TrailingMinusNumbers:=True Columns("T:T").Select Selection.Delete Shift:=xlToLeft Columns("U:U").Select Selection.Delete Shift:=xlToLeft Selection.Delete Shift:=xlToLeft Range("S1").Select Selection.AutoFilter sheetName = Worksheets(1).Name Set sht = Worksheets(sheetName) Set StartCell = Range("R3") LastRow = sht.Cells(sht.Rows.Count, StartCell.Column).End(xlUp).Row LastColumn = sht.Cells(StartCell.Row, sht.Columns.Count).End(xlToLeft).Column ActiveSheet.Range("$A$1:$T$" & CStr(LastRow)).AutoFilter Field:=19, Criteria1:="<>*Leg1*" Range("T1").Select Application.CutCopyMode = False ActiveCell.FormulaR1C1 = "=RC[-1]" Range("R1").Select Selection.End(xlDown).Select Range("T" & CStr(LastRow)).Select Range("T1:T" & CStr(LastRow)).Select Range("T" & CStr(LastRow)).Activate Selection.FillDown Range("T1").Select Selection.AutoFilter Columns("T:T").Select Selection.Copy Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False Range("S1").Select Application.CutCopyMode = False Selection.AutoFilter ActiveSheet.Range("$A$1:$T$" & CStr(LastRow)).AutoFilter Field:=19, Criteria1:="<>*Leg1*" Range("S2").Select Range(Selection, Selection.End(xlDown)).Select Selection.ClearContents Selection.End(xlUp).Select Selection.AutoFilter Columns("S:T").Select Selection.Replace What:="N/A", Replacement:="", LookAt:=xlPart, _ SearchOrder:=xlByRows, MatchCase:=False Columns("S:S").Select Selection.Cut Range("U1").Select ActiveSheet.Paste Columns("S:S").Select Selection.Delete Shift:=xlToLeft Columns("T:T").Select Selection.TextToColumns Destination:=Range("T1"), DataType:=xlDelimited, _ TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _ Semicolon:=False, Comma:=False, Space:=False, Other:=True, OtherChar _ :="D", FieldInfo:=Array(Array(1, 1), Array(2, 1)), TrailingMinusNumbers:=True Columns("U:U").Select Selection.TextToColumns Destination:=Range("U1"), DataType:=xlDelimited, _ TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _ Semicolon:=False, Comma:=False, Space:=False, Other:=True, OtherChar _ :="=", FieldInfo:=Array(Array(1, 1), Array(2, 1)), TrailingMinusNumbers:=True Columns("V:V").Select Selection.TextToColumns Destination:=Range("V1"), DataType:=xlDelimited, _ TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _ Semicolon:=False, Comma:=False, Space:=False, Other:=True, OtherChar _ :="s", FieldInfo:=Array(Array(1, 1), Array(2, 1)), TrailingMinusNumbers:=True Columns("T:T").Select Selection.Delete Shift:=xlToLeft Selection.Delete Shift:=xlToLeft Range("T1").Select ActiveCell.FormulaR1C1 = "Leg1" Columns("S:S").Select Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove Columns("U:U").Select Selection.Cut Range("S1").Select ActiveSheet.Paste Columns("T:T").Select Selection.TextToColumns Destination:=Range("T1"), DataType:=xlDelimited, _ TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _ Semicolon:=False, Comma:=False, Space:=False, Other:=True, OtherChar _ :="D", FieldInfo:=Array(Array(1, 1), Array(2, 1)), TrailingMinusNumbers:=True Columns("U:U").Select Selection.TextToColumns Destination:=Range("U1"), DataType:=xlDelimited, _ TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _ Semicolon:=False, Comma:=False, Space:=False, Other:=True, OtherChar _ :="=", FieldInfo:=Array(Array(1, 1), Array(2, 1)), TrailingMinusNumbers:=True Columns("V:V").Select Selection.TextToColumns Destination:=Range("V1"), DataType:=xlDelimited, _ TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _ Semicolon:=False, Comma:=False, Space:=False, Other:=True, OtherChar _ :="s", FieldInfo:=Array(Array(1, 1), Array(2, 1)), TrailingMinusNumbers:=True Columns("T:U").Select Selection.Delete Shift:=xlToLeft Selection.End(xlUp).Select ActiveCell.FormulaR1C1 = "Leg2" Range("S1").Select End Sub