In an effort to bring our call center to a median level it has been requested to change agents skill based off the # of calls they have taken throughout the day. Since Avaya Only maintains this information on a single Rep level or split by Skill I'm forced to bring the info into Excel to combine, Now I need VBA code to Change Skill levels once an Employee has taken 75 calls. I've set up a script to export data from CMS to excell in 15 minute increments. Here is the Code I have so far.
First off, this code logs me in CMS, If possible I'd like for it to utilize me already being logged into CMS (though that is not the most important thing). The Coding doen't seem to work. I'm still a bit new to VBA and definitely new to Avaya, but I'm trying to automate this process. Any help would be appreciated.
Thanks
-Red
Code:
Sub createConnection(server As cvsServer, connection As cvsConnection, application As cvsApplication, alreadyConnected As Boolean)
'this procedure will initialize the application using the server and connection variables, server login info needed.
Dim sUser As String
Dim sPassword As String
Dim sServer As String
sUser = "******" 'username
sPassword = "******" 'password
sServer = "**.**.*.**"
If alreadyConnected = False Then
If application.CreateServer(sUser, sPassword, "", sServer, False, "ENU", server, connection) Then
connection.lTimeOutSecs = 10
If connection.Login(sUser, sPassword, sServer, "ENU", "", False) = False Then
MsgBox "Unable to Logon. Please Try Again."
Else: alreadyConnected = True
End If
End If
End If
'The above code will initiate a connection to CMS for you, you'd follow it up with code to run a report.
'sUser, sPassword, and sServer would be replaced with the relevent strings
End Sub
Sub closeConnection(server As cvsServer, connection As cvsConnection, application As cvsApplication, alreadyConnected As Boolean)
'Will close connections if open.
If alreadyConnected Then
server.Close
application.Close
connection.logout
connection.Close
End If
End Sub
Function classifyReps(class As Characters) As Variant
SetArr() As Variant
If class = "p" Then
SetArr(1, 1) = 63 ' skill number 6
SetArr(1, 2) = 1 ' priority 1 or 2 First skill set
SetArr(1, 3) = 0
SetArr(2, 1) = 60 'skill number 14 etc...
SetArr(2, 2) = 1 ' Second Skill Set
SetArr(2, 3) = 0
SetArr(3, 1) = 70
SetArr(3, 2) = 1 ' Third Skill set
SetArr(3, 3) = 0
SetArr(4, 1) = 71
SetArr(4, 2) = 1
SetArr(4, 3) = 0
SetArr(5, 1) = 57
SetArr(5, 2) = 2
SetArr(5, 3) = 0
SetArr(6, 1) = 67
SetArr(6, 2) = 1
SetArr(6, 3) = 0
SetArr(7, 1) = 47
SetArr(7, 2) = 1
SetArr(7, 3) = 0
ElseIf class = "U" Then
End If
classifyReps = SetArr
End Function
Sub ChangeSkills(cvsSrv As cvsServer, cvsConn As cvsConnection, cvsApp As cvsApplication, repsToChange As Range)
Dim agents As String
Dim SetArr() As Variant
Dim sWarn As String
Dim Skills() As Variant
Dim AgMngObj As Object 'This variable was referenced but was not defined I went with object but dont know what it is.
Dim cell As Range
For Each cell In repsToChange 'add to the agents string the agent numbers found from findReps function
agents = agents + cell.value() + ";"
SetArr() = classifyReps("p") ''Create Skill Array for CMS, 7 skills in total here to do
sWarn = ""
AgMngObj = cvsSrv.AgentMgmt ' this appears to create a sub object with the ability to change
'Change Skills
agents = "3162" ' for these three agents numbers, you can add more
AgMngObj.AcdStartUp -1, "", cvsSrv.ServerKey, -1
AgMngObj.OleAgentSetSkill 1, "" & agents & "", 1, 6, 0, 0, 7, SetArr, sWarn ' note the 7 for 7 skills in SetArr and the 6 is the base skill number yours might be 1, you can find this by referencing the avaya script in notepad
'Function OleAgentSetSkill(
'ByVal iAcd As Integer, 1
'sAgentID As String, agents variable we created
'ByVal iAgentPref As Integer,
'ByVal iDirectSkill As Integer,
'ByVal iDirFirst As Integer,
'ByVal iSrvObj As Integer,
'ByVal iNumskills As Integer,
'cSkillResult As Variant,
'sWarn As String) As Boolean
Set AgMngObj = Nothing
End Sub
Sub findReps(reps As Range)
' this code should creat a range selecting all of the reps remaining in the pivot table.
' if your pivot table is coded into vba you may wish to create a subfunction for it
Dim start As Range
start = Sheet1.Cells("A", 1) 'modify with appropriate sheet, column and row.
reps = Range(start, start.End(xlDown)) 'use xlRight if data will be in a row instead of a column
End Sub
Sub Main()
Dim cvsSrv As New cvsServer
Dim cvsConn As New cvsConnection
Dim cvsApp As New cvsApplication
Dim bConnected As Boolean
Dim repsToChange As Range
Call createConnection(cvsSrv, cvsConn, cvsApp, bConnected)
Call findReps(repsToChange)
Call ChangeSkills(cvsSrv, cvsConn, cvsApp, repsToChange)
Call closeConnection(cvsSrv, cvsConn, cvsApp, bConnected)
End SubFirst off, this code logs me in CMS, If possible I'd like for it to utilize me already being logged into CMS (though that is not the most important thing). The Coding doen't seem to work. I'm still a bit new to VBA and definitely new to Avaya, but I'm trying to automate this process. Any help would be appreciated.
Thanks
-Red
