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:

  1. Go to Excel > Preferences… > Ribbon & Toolbar

  2. 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 -

  1. Download LegDuration.bas
  2. Click on 'Visual Basic' from the 'Developer' tab
  3. Go to File > Import File...
  4. Choose the macro that you've downloaded and then return to the Exotel call report
  5. 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