Changing Avaya Agent Skills with Excel VBA

[N0 DICE]

I have a code that I was using to change agent skills with VBA. It turns out that the code is to large and I get a compile error. Procedure too large. Can anyone help me with a way to shorten it or possible modify it with a loop of some sort?

This is a piece of the code; I need it to repeat the SetArr piece 700 times. If anyone has any insights they would be greatly appreciated. Also I can send the excel workbook if anyone would like to take a look. It is kinda large (20 Mb)

Sub ChangeSkills()

Dim agents As String
Dim cvsApp As Object
Dim cvsConn As Object
Dim cvsSrv As Object
Dim SetArr() As Variant
Dim sWarn As String
Dim Skills() As Variant
Dim nSkills As String


Set cvsApp = CreateObject("ACSUP.cvsApplication")
Set cvsConn = CreateObject("ACSCN.cvsConnection")
Set cvsSrv = CreateObject("ACSUPSRV.cvsServer")



UserName = "***"
Password = "****"
AvayaIP = "*****"

If cvsApp.CreateServer(UserName, Password, "", AvayaIP, False, "ENU", cvsSrv, cvsConn) Then
If cvsConn.Login(UserName, Password, AvayaIP, "ENU") Then

On Error Resume Next
Set AgMngObj = cvsSrv.AgentMgmt

ReDim SetArr(12, 3)
SetArr(1, 1) = Range("B2").Value
SetArr(1, 2) = Range("B3").Value
SetArr(1, 3) = 0
SetArr(2, 1) = Range("C2").Value
SetArr(2, 2) = Range("C3").Value
SetArr(2, 3) = 0
SetArr(3, 1) = Range("D2").Value
SetArr(3, 2) = Range("D3").Value
SetArr(3, 3) = 0
SetArr(4, 1) = Range("E2").Value
SetArr(4, 2) = Range("E3").Value
SetArr(4, 3) = 0
SetArr(5, 1) = Range("F2").Value
SetArr(5, 2) = Range("F3").Value
SetArr(5, 3) = 0
SetArr(6, 1) = Range("G2").Value
SetArr(6, 2) = Range("G3").Value
SetArr(6, 3) = 0
SetArr(7, 1) = Range("H2").Value
SetArr(7, 2) = Range("H3").Value
SetArr(7, 3) = 0
SetArr(8, 1) = Range("I2").Value
SetArr(8, 2) = Range("I3").Value
SetArr(8, 3) = 0
SetArr(9, 1) = Range("J2").Value
SetArr(9, 2) = Range("J3").Value
SetArr(9, 3) = 0
SetArr(10, 1) = Range("K2").Value
SetArr(10, 2) = Range("K3").Value
SetArr(10, 3) = 0
SetArr(11, 1) = Range("L2").Value
SetArr(11, 2) = Range("L3").Value
SetArr(11, 3) = 0
SetArr(12, 1) = Range("M2").Value
SetArr(12, 2) = Range("M3").Value
SetArr(12, 3) = 0

sWarn = ""
agents = Range("A2").Value
nSkills = Range("A3").Value

AgMngObj.AcdStartUp -1, "", cvsSrv.ServerKey, -1
AgMngObj.OleAgentSetSkill 1, "" & agents & "", 1, 0, 0, 0, nSkills, SetArr, sWarn ' note the nSKills for # of skills in SetArr

On Error Resume Next
Set AgMngObj = cvsSrv.AgentMgmt

ReDim SetArr(12, 3)
SetArr(1, 1) = Range("B4").Value
SetArr(1, 2) = Range("B5").Value
SetArr(1, 3) = 0
SetArr(2, 1) = Range("C4").Value
SetArr(2, 2) = Range("C5").Value
SetArr(2, 3) = 0
SetArr(3, 1) = Range("D4").Value
SetArr(3, 2) = Range("D5").Value
SetArr(3, 3) = 0
SetArr(4, 1) = Range("E4").Value
SetArr(4, 2) = Range("E5").Value
SetArr(4, 3) = 0
SetArr(5, 1) = Range("F4").Value
SetArr(5, 2) = Range("F5").Value
SetArr(5, 3) = 0
SetArr(6, 1) = Range("G4").Value
SetArr(6, 2) = Range("G5").Value
SetArr(6, 3) = 0
SetArr(7, 1) = Range("H4").Value
SetArr(7, 2) = Range("H5").Value
SetArr(7, 3) = 0
SetArr(8, 1) = Range("I4").Value
SetArr(8, 2) = Range("I5").Value
SetArr(8, 3) = 0
SetArr(9, 1) = Range("J4").Value
SetArr(9, 2) = Range("J5").Value
SetArr(9, 3) = 0
SetArr(10, 1) = Range("K4").Value
SetArr(10, 2) = Range("K5").Value
SetArr(10, 3) = 0
SetArr(11, 1) = Range("L4").Value
SetArr(11, 2) = Range("L5").Value
SetArr(11, 3) = 0
SetArr(12, 1) = Range("M4").Value
SetArr(12, 2) = Range("M5").Value
SetArr(12, 3) = 0

sWarn = ""
agents = Range("A4").Value
nSkills = Range("A5").Value

AgMngObj.AcdStartUp -1, "", cvsSrv.ServerKey, -1
AgMngObj.OleAgentSetSkill 1, "" & agents & "", 1, 0, 0, 0, nSkills, SetArr, sWarn ' note the nSKills for # of skills in SetArr

On Error Resume Next
Set AgMngObj = cvsSrv.AgentMgmt

ReDim SetArr(12, 3)
SetArr(1, 1) = Range("B6").Value
SetArr(1, 2) = Range("B7").Value
SetArr(1, 3) = 0
SetArr(2, 1) = Range("C6").Value
SetArr(2, 2) = Range("C7").Value
SetArr(2, 3) = 0
SetArr(3, 1) = Range("D6").Value
SetArr(3, 2) = Range("D7").Value
SetArr(3, 3) = 0
SetArr(4, 1) = Range("E6").Value
SetArr(4, 2) = Range("E7").Value
SetArr(4, 3) = 0
SetArr(5, 1) = Range("F6").Value
SetArr(5, 2) = Range("F7").Value
SetArr(5, 3) = 0
SetArr(6, 1) = Range("G6").Value
SetArr(6, 2) = Range("G7").Value
SetArr(6, 3) = 0
SetArr(7, 1) = Range("H6").Value
SetArr(7, 2) = Range("H7").Value
SetArr(7, 3) = 0
SetArr(8, 1) = Range("I6").Value
SetArr(8, 2) = Range("I7").Value
SetArr(8, 3) = 0
SetArr(9, 1) = Range("J6").Value
SetArr(9, 2) = Range("J7").Value
SetArr(9, 3) = 0
SetArr(10, 1) = Range("K6").Value
SetArr(10, 2) = Range("K7").Value
SetArr(10, 3) = 0
SetArr(11, 1) = Range("L6").Value
SetArr(11, 2) = Range("L7").Value
SetArr(11, 3) = 0
SetArr(12, 1) = Range("M6").Value
SetArr(12, 2) = Range("M7").Value
SetArr(12, 3) = 0

sWarn = ""
agents = Range("A6").Value
nSkills = Range("A7").Value

AgMngObj.AcdStartUp -1, "", cvsSrv.ServerKey, -1
AgMngObj.OleAgentSetSkill 1, "" & agents & "", 1, 0, 0, 0, nSkills, SetArr, sWarn ' note the nSKills for # of skills in SetArr

On Error Resume Next
Set AgMngObj = cvsSrv.AgentMgmt

ReDim SetArr(12, 3)
SetArr(1, 1) = Range("B8").Value
SetArr(1, 2) = Range("B9").Value
SetArr(1, 3) = 0
SetArr(2, 1) = Range("C8").Value
SetArr(2, 2) = Range("C9").Value
SetArr(2, 3) = 0
SetArr(3, 1) = Range("D8").Value
SetArr(3, 2) = Range("D9").Value
SetArr(3, 3) = 0
SetArr(4, 1) = Range("E8").Value
SetArr(4, 2) = Range("E9").Value
SetArr(4, 3) = 0
SetArr(5, 1) = Range("F8").Value
SetArr(5, 2) = Range("F9").Value
SetArr(5, 3) = 0
SetArr(6, 1) = Range("G8").Value
SetArr(6, 2) = Range("G9").Value
SetArr(6, 3) = 0
SetArr(7, 1) = Range("H8").Value
SetArr(7, 2) = Range("H9").Value
SetArr(7, 3) = 0
SetArr(8, 1) = Range("I8").Value
SetArr(8, 2) = Range("I9").Value
SetArr(8, 3) = 0
SetArr(9, 1) = Range("J8").Value
SetArr(9, 2) = Range("J9").Value
SetArr(9, 3) = 0
SetArr(10, 1) = Range("K8").Value
SetArr(10, 2) = Range("K9").Value
SetArr(10, 3) = 0
SetArr(11, 1) = Range("L8").Value
SetArr(11, 2) = Range("L9").Value
SetArr(11, 3) = 0
SetArr(12, 1) = Range("M8").Value
SetArr(12, 2) = Range("M9").Value
SetArr(12, 3) = 0

sWarn = ""
agents = Range("A8").Value
nSkills = Range("A9").Value

AgMngObj.AcdStartUp -1, "", cvsSrv.ServerKey, -1
AgMngObj.OleAgentSetSkill 1, "" & agents & "", 1, 0, 0, 0, nSkills, SetArr, sWarn ' note the nSKills for # of skills in SetArr

On Error Resume Next
Set AgMngObj = cvsSrv.AgentMgmt

ReDim SetArr(12, 3)
SetArr(1, 1) = Range("B10").Value
SetArr(1, 2) = Range("B11").Value
SetArr(1, 3) = 0
SetArr(2, 1) = Range("C10").Value
SetArr(2, 2) = Range("C11").Value
SetArr(2, 3) = 0
SetArr(3, 1) = Range("D10").Value
SetArr(3, 2) = Range("D11").Value
SetArr(3, 3) = 0
SetArr(4, 1) = Range("E10").Value
SetArr(4, 2) = Range("E11").Value
SetArr(4, 3) = 0
SetArr(5, 1) = Range("F10").Value
SetArr(5, 2) = Range("F11").Value
SetArr(5, 3) = 0
SetArr(6, 1) = Range("G10").Value
SetArr(6, 2) = Range("G11").Value
SetArr(6, 3) = 0
SetArr(7, 1) = Range("H10").Value
SetArr(7, 2) = Range("H11").Value
SetArr(7, 3) = 0
SetArr(8, 1) = Range("I10").Value
SetArr(8, 2) = Range("I11").Value
SetArr(8, 3) = 0
SetArr(9, 1) = Range("J10").Value
SetArr(9, 2) = Range("J11").Value
SetArr(9, 3) = 0
SetArr(10, 1) = Range("K10").Value
SetArr(10, 2) = Range("K11").Value
SetArr(10, 3) = 0
SetArr(11, 1) = Range("L10").Value
SetArr(11, 2) = Range("L11").Value
SetArr(11, 3) = 0
SetArr(12, 1) = Range("M10").Value
SetArr(12, 2) = Range("M11").Value
SetArr(12, 3) = 0

sWarn = ""
agents = Range("A10").Value
nSkills = Range("A11").Value

AgMngObj.AcdStartUp -1, "", cvsSrv.ServerKey, -1
AgMngObj.OleAgentSetSkill 1, "" & agents & "", 1, 0, 0, 0, nSkills, SetArr, sWarn ' note the nSKills for # of skills in SetArr

Set AgMngObj = Nothing
Else

End If
End If

Set cvsApp = Nothing
Set cvsConn = Nothing
Set cvsSrv = Nothing

cvsConn.Logout
cvsConn.Disconnect


End Sub

 

[GlennUK]

At a guess, all your code from the If.. Login to the End Ifs could be:

On Error Resume Next
Set AgMngObj = cvsSrv.AgentMgmt

ReDim SetArr(12, 3)

For i = 1 To 700
    For j = 1 To 12
    
        SetArr(j, 1) = Cells(i * 2, j + 1).Value
        SetArr(j, 2) = Cells(i * 2 + 1, j + 1).Value
        SetArr(j, 3) = 0
        
    Next
    
    sWarn = ""
    agents = Cells(i * 2, 1).Value
    nSkills = Cells(i * 2 + 1, 1).Value
    
    AgMngObj.AcdStartUp -1, "", cvsSrv.ServerKey, -1
    AgMngObj.OleAgentSetSkill 1, "" & agents & "", 1, 0, 0, 0, nSkills, SetArr, sWarn ' note the nSKills for # of skills in SetArr
Next

 

[N0 DICE]

That worked out great thank you!

One more question, Say i didn't want to go to 700 agents, it there a way to change it to only do the last row with data?

 

[GlennUK]

So, if say,
Cells(i * 2, j + 1).Value
in the loop had a value that was "", then you could stop processing?

If so, you could do this:

    For j = 1 To 12
    
        SetArr(j, 1) = Cells(i * 2, j + 1).Value
        If Cells(i * 2, j + 1).Value = "" Then
              Set cvsApp = Nothing
              Set cvsConn = Nothing
              Set cvsSrv = Nothing
              cvsConn.Logout
              cvsConn.Disconnect
              End
        End If

... as a guess.

 

[JackDanIce]

Try:

 For j = 1 To 12
    
        SetArr(j, 1) = Cells(i * 2, j + 1).Value
        If Cells(i * 2, j + 1).Value = "" Then Exit For
 
Next j

 

[N0 DICE]

Instead of doing it with the SetArr, can it be done with the agents = Cells(i * 2, 1).Value line?

 

[GlennUK]

Instead of doing it with the SetArr, can it be done with the agents = Cells(i * 2, 1).Value line?

Er, yes, of course. Just try it.

 

[tarxb]

N0 DICE

Your code seems to be the holy grail for quite a few searchers.

Do you have any info you can share on the layout of the input data, i'm having trouble finding info on AbMngObj.oleagentsetskill.
is 'agents' agent id?
where is skill level set?

thanks

 

[tyger0951]

The skill level set is the array in the code. If you recorda script to reskill your agent then veiw the code saved you can easy copy and paste over the array built in the script if your gates never change. Feel free to ask for more specific help, I'm wworking on alot of vba avaya codes at the moment.

fracta.net :: Topic: Excel VBA macro automate Avaya CMS agent groups (2/2)


Check my code in yhe above link for screen shots and step by step directions to g the script method to help write your code.

 

[tarxb]

What we would like is to reset the skills of agents that are members of an "agent group" to match those of a specific agent id (as a template) on a nightly basis.

Therefore is there a way using CMS scripts to extract an "agent group" membership listing, then extract the skill listing for a specific (template) agent, then overwrite the skills of the agents extracted with the skill listing from the template?

Thanks

The skill level set is the array in the code. If you recorda script to reskill your agent then veiw the code saved you can easy copy and paste over the array built in the script if your gates never change. Feel free to ask for more specific help, I'm wworking on alot of vba avaya codes at the moment.

fracta.net :: Topic: Excel VBA macro automate Avaya CMS agent groups (2/2)


Check my code in yhe above link for screen shots and step by step directions to g the script method to help write your code.