AVAYA CMS Scripting through Excel VBA

[Maxi]

Private Function CMSGetReport(sServerIP As String, iACD As Integer,
sReportName As String, sProperty1Name As String, sProperty1Value As
String, sProperty2Name As String, sProperty2Value As String,
sExportName As String) As Boolean
Dim cvsApp As CVS.cvsApplication
Dim cvsConn As CVSCN.cvsConnection
Dim cvsSrv As CVSUPSRV.cvsServer
Dim Rep As CVSREP.cvsReport
Dim Info As Object, Log As Object, b As Object

Set cvsApp = New CVSUP.cvsApplication
If cvsApp.CreateServer(getusername(sServerIP), "", "", sServerIP,
False, "ENU", cvsSrv, cvsConn) Then
If cvsConn.Login(getusername(sServerIP), getpassword(sServerIP),
sServerIP, "ENU") Then

CMSGetReport = False

On Error Resume Next
cvsSrv.Reports.ACD = iACD
Set Info = cvsSrv.Reports.Reports(sReportName)
If Info Is Nothing Then
If cvsSrv.Interactive Then
MsgBox "The Report " & sReportName & " was not found on
ACD" & iACD & ".", vbCritical Or vbOKOnly, "CentreVu Supervisor"
Else
Set Log = CreateObject("CVSERR.cvslog")
Log.AutoLogWrite "The Report " & sReportName & " was not
found on ACD" & iACD & "."
Set Log = Nothing
End If
Else
b = cvsSrv.Reports.CreateReport(Info, Rep)
If b Then
Debug.Print Rep.SetProperty(sProperty1Name,
sProperty1Value)
Debug.Print Rep.SetProperty(sProperty2Name,
sProperty2Value)
' Debug.Print Rep.SetProperty(sProperty3Name,
sProperty3Value)
b = Rep.ExportData(sExportName, 9, 0, True, False, True)
Rep.Quit
CMSGetReport = True
If Not cvsSrv.Interactive Then cvsSrv.ActiveTasks.Remove
Rep.TaskID
Set Rep = Nothing
End If
End If
Set Info = Nothing
End If
End If
End Function

I got the above fuction from
http://www.dbforums.com/archive/index.php/t-1003776.html. What it does
is, it extracts reports from AVAYA CenterVu Supervisor 9.0 software
used by call centers.

But when I used it in excel VBA, it gives me compile error
"User-defined type not defined in the first line "Dim cvsApp As
CVS.cvsApplication" when I try to call the function see my code below.

More info:

I searched few files on my comp in the C:\Program Files\Avaya\CentreVu
Supervisor 9.0 directory and go to know that

cvsAPP is an .exe file
cvsConn is a .dll file
cvsSrv is an .exe file
CVSREP is an .exe file

Sub a()
Call CMSGetReport("172.18.3.60", 1, "Upstream Stats Rpt", "Date(s)",
"03/01/06--1", "Split Numbers", "71;72;74;79;82;83",
"m:\ReportsAutomation\abc.xls")
End Sub

How can I get rid of the error?

Note: The above function might not display properly due to word wrap

 

[Jim Cone]

In the VBE, go to Tools | References and checkmark anything having to
do with CVS.
--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware



....
I got the above fuction from
http://www.dbforums.com/archive/index.php/t-1003776.html. What it does
is, it extracts reports from AVAYA CenterVu Supervisor 9.0 software
used by call centers.
....
But when I used it in excel VBA, it gives me compile error
"User-defined type not defined in the first line "Dim cvsApp As
CVS.cvsApplication" when I try to call the function see my code below.
....
How can I get rid of the error?

 

[Maxi]

Private Function CMSGetReport( _
sServerIP As String, _
iACD As Integer, _
sReportName As String, _
sProperty1Name As String, _
sProperty1Value As String, _
sProperty2Name As String, _
sProperty2Value As String, _
sExportName As String) As Boolean

Const Username As String = "india"
Const Password As String = "mumbai11"

Dim cvsApp As Object
Dim cvsConn As Object
Dim cvsSrv As Object
Dim Rep As Object

Dim Info As Object, Log As Object, b As Object

Set cvsApp = CreateObject("CVS.cvsApplication")
Set cvsConn = CreateObject("CVSCN.cvsConnection")
Set cvsSrv = CreateObject("CVSUPSRV.cvsServer")
Set Rep = CreateObject("CVSREP.cvsReport")
'Set cvsApp = New CVSUP.cvsApplication

If cvsApp.CreateServer(Username, "", "", sServerIP, False, "ENU",
cvsSrv, cvsConn) Then
If cvsConn.Login(Username, Password, sServerIP, "ENU") Then

CMSGetReport = False

On Error Resume Next
cvsSrv.Reports.ACD = iACD
Set Info = cvsSrv.Reports.Reports(sReportName)
If Info Is Nothing Then
If cvsSrv.Interactive Then
MsgBox "The Report " & sReportName & " was not found on
ACD" & iACD & ".", vbCritical Or vbOKOnly, "CentreVu Supervisor"
Else
Set Log = CreateObject("CVSERR.cvslog")
Log.AutoLogWrite "The Report " & sReportName & " was not
found on ACD" & iACD & "."
Set Log = Nothing
End If
Else
b = cvsSrv.Reports.CreateReport(Info, Rep)
If b Then
Debug.Print Rep.SetProperty(sProperty1Name,
sProperty1Value)
Debug.Print Rep.SetProperty(sProperty2Name,
sProperty2Value)
' Debug.Print Rep.SetProperty(sProperty3Name,
sProperty3Value)
b = Rep.ExportData(sExportName, 9, 0, True, False, True)
Rep.Quit
CMSGetReport = True

If Not cvsSrv.Interactive Then cvsSrv.ActiveTasks.Remove
Rep.TaskID
Set Rep = Nothing
End If

End If

Set Info = Nothing

End If
End If

End Function

Sub b()
Call CMSGetReport("172.18.3.60", 1, "Upstream Stats Rpt", "Date(s)",
"03/01/06--1", "Split Numbers", "71;72;74;79;82;83",
"m:\ReportsAutomation\abc.xls")
End Sub


The code I posted earlier is changed. Above is the new code helped by
user Right_Click @ mrexcel.com forum.

The problem now is:

Run-time error '429':

ActiveX componenet can't create object

line: Set cvsApp = CreateObject("CVS.cvsApplication")

Looks like I haven't set the right references.

I went to Tools->References and selected the checked CVS Application
Component and the location it is poiting out is c:\Program
Files\Avaya\CentreVu Supervisor 9.0\cvsAPP.exe which is correct.

Then what I did was, went through all the controls in the references
window and checked all of them that were pointing to c:\Program
Files\Avaya\CentreVu Supervisor 9.0\ directory but still the same
error.

What am I doing wrong?

 

[Jim Cone]

I am guessing, as I don't have a CVS application...

Set cvsApp = Getobject(, "CVS.cvsApplication")
or...
Set cvsApp = Getobject(c:\Program Files\Avaya\CentreVu Supervisor 9.0\cvsAPP.exe")

Mr Right_Click may be Mr. right for you.

Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware



....
The code I posted earlier is changed.
Above is the new code helped by user Right_Click @ mrexcel.com forum.
The problem now is:
Run-time error '429':
ActiveX componenet can't create object

line: Set cvsApp = CreateObject("CVS.cvsApplication")

Looks like I haven't set the right references.
I went to Tools->References and selected the checked CVS Application
Component and the location it is poiting out is c:\Program
Files\Avaya\CentreVu Supervisor 9.0\cvsAPP.exe which is correct.

Then what I did was, went through all the controls in the references
window and checked all of them that were pointing to c:\Program
Files\Avaya\CentreVu Supervisor 9.0\ directory but still the same
error.
What am I doing wrong?

 

[Maxi]

He asked me to try
------------------------------------------------------------------------------------------
Try editing this line
Set cvsApp = CreateObject("CVS.cvsApplication")
by changing it to
Set cvsApp = CreateObject("CVSUP.cvsApplication")
------------------------------------------------------------------------------------------

Yes, that did the trick but it does not create abc.xls file mentioned
in the parameters of CMSGetReport()

I opened Windows Task Manager to see what is happenning when the code
is running.

First it opens cvsAPP which I beleive is the .exe application file for
the software, then it opens cvsServer and then it opens Report Engine.
Looks like the report is generated but is not exported.

Can it be just copied to sheet1 instead of exporting?

 

[Jim Cone]

I don't know.

Jim Cone


He asked me to try
------------------------------------------------------------------------------------------
Try editing this line
Set cvsApp = CreateObject("CVS.cvsApplication")
by changing it to
Set cvsApp = CreateObject("CVSUP.cvsApplication")
------------------------------------------------------------------------------------------

Yes, that did the trick but it does not create abc.xls file mentioned
in the parameters of CMSGetReport()
I opened Windows Task Manager to see what is happenning when the code
is running.
First it opens cvsAPP which I beleive is the .exe application file for
the software, then it opens cvsServer and then it opens Report Engine.
Looks like the report is generated but is not exported.
Can it be just copied to sheet1 instead of exporting?