× Welcome to the fracta.net forum!

Share your coding ideas or ask questions.

Excel Macro to Automate Skill/Level changes for Avaya CMS

  • botsblitz31
  • Visitor
  • Visitor
6 years 5 months ago #430 by botsblitz31
Hi Roller,

I'm trying to setup an automated skill template within excel that can skill multiple agents but I can't seem to make it work, what happens is even though I set the loop to stop until the its done checking all the agents, it can only process 1 skill and thats it.


Here's the code that I have
Sub SkillThemAgents()

Dim cvsApp As New ACSUP.cvsApplication
Dim cvsConn As New ACSCN.cvsConnection
Dim cvsSrv As New ACSUPSRV.cvsServer
Dim op As ACSFE.cvsOperation
Dim AgMngObj As acsaa.cvsAgentMgmt

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

'===========================================================

Dim Lastrow As Long
Dim LastCol As Long

Dim wsMain As Worksheet
Set wsMain = Sheets("Main")

Dim l As Integer
Dim c As Integer
Dim p As Integer
Dim S As Integer
Dim t As Integer
Dim Skill As String
Dim Prtr As Integer
Dim SetArr() As Variant

S = 1

Lastrow = wsMain.Range("a" & wsMain.Rows.Count).End(xlUp).Row

For l = 2 To Lastrow

LastCol = wsMain.Range("a" & l).End(xlToRight).Column

For c = 5 To LastCol Step 2
    
On Error Resume Next

Skill = wsMain.Cells(l, c).Value
Prtr = wsMain.Cells(l, c + 1).Value
   
Set AgMngObj = cvsSrv.AgentMgmt
ReDim SetArr(3, 3)

SetArr(S, 1) = Skill
SetArr(S, 2) = Prtr
SetArr(S, 3) = 0
S = S + 1
Next c

AgMngObj.AcdStartUp -1, "", cvsSrv.ServerKey, -1
AgMngObj.OleAgentSetSkill 2, "1029968", 1, 0, 0, 0, 3, SetArr, ""

S = 1
Next l

MsgBox "Skill Change completed."


'===================com======================================

Else
Select Case cvsConn.LoginState
Case cpBadLogin
    Call MsgBox("CMS Username Invalid: " & cvsConn.sInputBuffer & " " & cvsConn.User, vbOKOnly + vbInformation, "CMS")
Case cpWaiting
    Call MsgBox("Waiting for CMS Server to respond." & vbCrLf & _
                "Verify server address is correct." & vbCrLf & _
                "Aborted", vbOKOnly + vbInformation, "CMS")
Case Else
    Call MsgBox("Can not connect to CMS Server (" & CStr(cvsConn.LoginState) & ")", vbOKOnly + vbInformation, "CMS")
End Select
End If
Else
Call MsgBox("Unable to create CMS Server connection." & vbCrLf & _
    "Verify CMS is installed properly.", vbOKOnly + vbInformation)
End If

On Error GoTo 0
Set AgMngObj = Nothing
cvsSrv.ActiveTasks.Refresh
cvsApp.Servers.Remove cvsSrv.ServerKey
cvsSrv.Connected = False
cvsConn.logout
cvsConn.Disconnect
cvsSrv.ActiveTasks.Refresh
Set cvsConn = Nothing
Set cvsSrv = Nothing
Set cvsApp = Nothing
Set op = Nothing
Set sh = Nothing
End Sub

Here's the snapshot of what the excel sheet looks like
[/URL]

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

More
6 years 5 months ago - 6 years 5 months ago #431 by roller
Only one post please per question. If you are a new user you will not see your post until approved by a moderator. This is to avoid those nasty forum slammer.

You are correct with your method only only skill will change. To change more that one you have to do them in bulk by creating the array for all changes required.

Some previous post show how to structure and array with multiple skill changes. The Pproblem arise though when you have agents each with a different sets of skills. You need to find a way to recreate that array each time.

Give that a go and if you get stuck I'll write something and make it available for download for Cole of dollar via the products link.

So problem I think is at redim set array (3,3). The array may should be (s,3) and s should start at 1 not zero.
Last edit: 6 years 5 months ago by roller.

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

  • botsblitz31
  • Visitor
  • Visitor
6 years 5 months ago #432 by botsblitz31
Sorry bout that I was getting some sort of error when I clicked ob submit. I did try to change the set array to s instead so itll move once the loop goes next and the s starts at 1. But still, it can only recognize the first skill. Im sort of stuck right now :/

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

  • Tyger0951
  • Visitor
  • Visitor
6 years 5 months ago - 6 years 5 months ago #433 by Tyger0951
S= 1
C = 5
Skill = wsMain.Cells(l, c).Value
Prtr = wsMain.Cells(l, c + 1).Value
   
ReDim SetArr(3, 3)

SetArr(S, 1) = Skill
SetArr(S, 2) = Prtr
SetArr(S, 3) = 0
S = S + 1
C=c+1 ' next skilland prt
SetArr(S, 1) = Skill
SetArr(S, 2) = Prtr
SetArr(S, 3) = 0
S = S + 1 
C=c+1 ' next skilland prt
SetArr(S, 1) = Skill
SetArr(S, 2) = Prtr
SetArr(S, 3) = 0

So it creates
SetArr(1, 1) = Skill '1st
SetArr(1, 2) = Prtr 
SetArr(1, 3) = 0
SetArr(2, 1) = Skill '2nd
SetArr(2, 2) = Prtr
SetArr(2, 3) = 0
SetArr(3, 1) = Skill '3rd
SetArr(3, 2) = Prtr
SetArr(3, 3) = 0
Last edit: 6 years 5 months ago by Tyger0951.

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

  • Tyger0951
  • Visitor
  • Visitor
6 years 5 months ago - 6 years 5 months ago #434 by Tyger0951
Can't make out what the snapshot of excel looks like. Can youpostlarger picture?

Here's the snapshot of what the excel sheet looks like

[/URL]

Last edit: 6 years 5 months ago by Tyger0951.

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

More
6 years 5 months ago #435 by roller
You have dim skill as string, try to dim skill as integer. Long shot. ...

Also try to set the array manually like mentioned in the previous post to see if that works at least.

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

Time to create page: 0.658 seconds
Powered by Kunena Forum