× Welcome to the fracta.net forum!

Share your coding ideas or ask questions.

Excel Macro to Automate Skill/Level changes for Avaya CMS

More
9 years 6 months ago - 9 years 6 months ago #7 by roller
Since everyone been asking how to change the agents skills in Avaya CMS using Excel VBA I finally got around writing a full example. The code below should work but you need to replace the user name and password to yours, e.g if your user name is user1 then replace with "user1". Don't forget to include the "":

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

ReDim SetArr(7, 3) ''Create Skill Array for CMS, 7 skills in total here to do
SetArr(1, 1) = 6 ' skill number 6
SetArr(1, 2) = 1 ' priority 1 or 2
SetArr(1, 3) = 0
SetArr(2, 1) = 14 'skill number 14 etc...
SetArr(2, 2) = 1
SetArr(2, 3) = 0
SetArr(3, 1) = 50
SetArr(3, 2) = 1
SetArr(3, 3) = 0
SetArr(4, 1) = 42
SetArr(4, 2) = 1
SetArr(4, 3) = 0
SetArr(5, 1) = 17
SetArr(5, 2) = 2
SetArr(5, 3) = 0
SetArr(6, 1) = 87
SetArr(6, 2) = 1
SetArr(6, 3) = 0
SetArr(7, 1) = 54
SetArr(7, 2) = 1
SetArr(7, 3) = 0

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

sWarn = ""

If cvsApp.CreateServer(username, "", "", "CMS", False, "ENU", cvsSrv, cvsConn) Then
If cvsConn.Login(username, password, "CMS", "ENU") Then
On Error Resume Next
Set AgMngObj = cvsSrv.AgentMgmt
'Change Skills
agents = "2849;2850;3010" ' 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
Set AgMngObj = Nothing
Else

MsgBox "Unable to Logon. Please Try Again."
End If
End If

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



End Sub
Last edit: 9 years 6 months ago by roller.

Please Log in or Create an account to join the conversation.

  • quende
  • Visitor
  • Visitor
7 years 3 months ago #287 by quende
Hey there everybody, hey roller!
In the first, Im really sorry for my English.
Ive got some questions about this macro. I read your article about "How to automate Avaya reports", and learned many new things from it. I want to thank you for this!
But here is my goal - i need to change skills for some group of agents depending on index from another report (w/o export to Excel), for example Service level of some queue. I can export data from one table of CMS to excel, then analize it, and change some agents skill, but thats takes too much time to do that. (i need to refresh queue every 5 seconds)
(like Real-Time options, see an attachment)
So here is my question: is there a possibility to take some parametrs from report CMS without exporting it to excel? If yes, can u explain how to do that?

Thank you very much for these posts!!
Attachments:

Please Log in or Create an account to join the conversation.

More
7 years 3 months ago #288 by roller
Hi quende

I haven't been across any methods of pulling data from CMS directly and live. As far as I am aware all CMS data is written to tables in intervals, Even real time reports are based on a small intervals of data. This is what I think I am not an expert on how the application is built.

Not that I ever tired this option but do you have access to the PABX, maybe it can provide real time data.

At the very least you can run the VBA code very 5 seconds to export your parameter, in a loop, and get your data to analyse programatically and do what you want. This is not very elegant solution but can't see why it wouldn't do the job.

One final method I heard of is getting data from CMS is by installing an ODBC driver and accessing the CMS database directly instead of through the application reports. Avaya sells this driver.

Please Log in or Create an account to join the conversation.

  • quende
  • Visitor
  • Visitor
7 years 3 months ago - 7 years 3 months ago #289 by quende

At the very least you can run the VBA code very 5 seconds to export your parameter, in a loop, and get your data to analyse programatically and do what you want


Well, im doing it right now and it works, but thats not right, and we both know it :). Because this path: CMS -> Excel -> CMS is a nonsense.
Unfortunately im "rights restricted" on this computer :( (workplace), so i dont have access to install some features.

I am really appreciated you for your help!
Last edit: 7 years 3 months ago by quende.

Please Log in or Create an account to join the conversation.

  • quende
  • Visitor
  • Visitor
7 years 2 months ago - 7 years 2 months ago #290 by quende
Hey everybody!
Roller, ive got a new question for you.
Well, i did that i wrote before and faced with the problem that my CMS "freezing" and going down after working about an hour (my Excel is freezing also because it cant get a query form CMS, something about "OLE" etc). Then i have to reboot my computer because of that.

So i think its because of frequent connection to CMS or something about that.

So here is my code, which works:


Sub test() 'just for test
agents = "27366;25204;25209"
skill = "Skill1"
Call change(agents, skill)
'MsgBox "ok"
End Sub


Function change(ByVal agents As String, skill As String) 'here is main function
Dim cvsApp As Object
Dim cvsConn As Object
Dim cvsSrv As Object
Dim SetArr() As Variant
Dim sWarn As String
Dim Rep
Dim top%

If skill = "Skill1" Then
ReDim SetArr(2, 3)
SetArr(1, 1) = 4
SetArr(1, 2) = 1
SetArr(1, 3) = 0
SetArr(2, 1) = 15
SetArr(2, 2) = 1
SetArr(2, 3) = 0
top = 2
End If

Set cvsApp = CreateObject("ACSUP.cvsApplication")
Set Rep = CreateObject("ACSREP.cvsReport")
Set cvsSrv = CreateObject("ACSUPSRV.cvsServer")

sWarn = ""

If cvsApp.CreateServer("login", "password", "", "serverCMS", False, "ENU", cvsSrv, cvsConn) Then
If cvsConn.Login("login", "password", "serverCMS", "ENU") Then
On Error Resume Next
Set AgMngObj = cvsSrv.AgentMgmt

'Change Skills
AgMngObj.AcdStartUp -1, "", cvsSrv.ServerKey, -1
AgMngObj.OleAgentSetSkill 1, "" & agents & "", 1, SetArr(1, 1), 0, 0, top, SetArr, sWarn
Set AgMngObj = Nothing
Else
MsgBox "Unable to Logon. Please Try Again."
End If
End If

cvsApp.Servers.Remove cvsSrv.ServerKey
cvsConn.Logout
cvsConn.Disconnect
cvsSrv.Connected = False
Set cvsSrv = Nothing
Set cvsConn = Nothing
Set cvsApp = Nothing

End Function


This code works if there are some conditions (service level, queue, calls waiting, etc.), but there is another code, which refreshes every 5 seconds another report and export it to Excel:

Function CMSConn(sUserID As String, sPassword As String, sServerIP As String, otchet As String)

Dim cvsApp As cvsApplication
Dim cvsSrv As cvsServer
Dim cvsConn As cvsConnection
'Dim cvsConn As Object
Dim iServer As Integer
Dim bConnected As Boolean

bConnected = False
Set cvsApp = New cvsApplication
Set cvsSrv = New cvsServer
Set cvsConn = New cvsConnection

' If connection fails, OLE request won't continue looping
cvsConn.bTimeOutEnable = True
cvsConn.lTimeOutSecs = 25

'Checks to see if already connected to server
For iServer = 1 To cvsApp.Servers.Count
Set cvsSrv = cvsApp.Servers(iServer)
If cvsSrv.ServerKey Like "*" & sServerIP & "\*\*\*" Then
bConnected = True
Exit For
End If
Next iServer

'Initiates connection if one not already established
If bConnected = False Then
If cvsApp.CreateServer(sUserID, sPassword, "", sServerIP, False, "ENU", cvsSrv, cvsConn) Then
If cvsConn.Login(sUserID, sPassword, sServerIP, "ENU") Then
End If
End If
End If

'Executes CMS report
Dim cvsRepInfo As Object
Dim cvsRepProp As Object
Dim cvsLog As Object
Dim b As Boolean
Dim ag As String, dt As String, sp As String

'*
If otchet = "comparison" Then

cvsSrv.Reports.ACD = 1
Set cvsRepInfo = cvsSrv.Reports.Reports("REPORTS PATH")
If cvsRepInfo Is Nothing Then
If cvsSrv.Interactive Then
MsgBox "The report was not found on ACD 1.", vbCritical Or vbOKOnly, "Avaya CMS Supervisor"
Else
Set cvsLog = CreateObject("ACSERR.cvsLog")
cvsLog.AutoLogWrite "The report was not found on ACD 1."
Set cvsLog = Nothing
End If
Else
b = cvsSrv.Reports.CreateReport(cvsRepInfo, cvsRepProp)
If b Then
cvsRepProp.Window.top = 40
cvsRepProp.Window.Left = 40
cvsRepProp.Window.Width = 40
cvsRepProp.Window.Height = 40
cvsRepProp.SetProperty "Splits/Skills", "SOME SKILLS"
b = cvsRepProp.ExportData("", 9, 0, True, True, False)
'Closes report
If bConnected = True Then
cvsRepProp.Quit
Else
If Not cvsSrv.Interactive Then cvsSrv.ActiveTasks.Remove cvsRepProp.TaskID
End If
Set cvsRepProp = Nothing
End If
End If

End If
'*

'Terminates server instance and connection
Set cvsRepInfo = Nothing
If Not cvsSrv.Interactive Then cvsApp.Servers.Remove cvsSrv.ServerKey
If bConnected = False Then
cvsConn.Logout
cvsConn.Disconnect
cvsSrv.Connected = False
End If


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

End Function


Well, i think it should connect to cms, "doing reports" then disconnect, but because of working this Macro about an hour, it freezes CMS and Excel.

What am i doing wrong ?

Thank you!
Last edit: 7 years 2 months ago by quende.

Please Log in or Create an account to join the conversation.

More
7 years 2 months ago #291 by roller
Hard to say, but did you try to bring up the Windows Task Manager and look for what tasks are being created and killed? you might find that some tasks maybe building up such as Server tasks or cvsSrv.AgentMgmt. Have a look and confirm if any are building up.

Also why not keep the Server connection open and re-use it each 5 seconds instead of killing it and re-creating it? Or even have the Avaya CMS application running and hook into it's server object.

Please Log in or Create an account to join the conversation.

Time to create page: 0.149 seconds
Powered by Kunena Forum