If you have tasks in Microsoft Excel that you do repeatedly, you can either record a macro to automate these 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