Question Export Data Skill Interval from Excel VBA

Hi There,

I'm new in this group and also in VBA coding with CMS. I need assistance, how to export data Skill interval from Excel VBA?

If this query has been shared, pls share me the link so that i can refer it?

There are few post in this forum that describe how to automate CMS using VBA and Excel. Look at this post:

fracta.net/fracta/index.php/forum/2-exce...atic-export-to-excel

This post is for the log in log out report but the Skill Interval reports are very similar. Just change the report name section in the code and the parameters.

If you export the script from Avaya (I assume you know how to do that) as .acsauto or whatever it is then open it in a notepad you can see the VB code. Use this code as a guide to modify the code from the above post. Give it a try.

Dear Roller,

Thanks for your feedback. I you dont mind, can u make a script for me?
i still not familiar with the coding.
below is the CMS coding...

'LANGUAGE=ENU
'SERVERNAME=10.21.150.96
Public Sub Main()

'## cvs_cmd_begin
'## ID = 2001
'## Description = "Report: Historical: Designer: TM_Skill Interval: Export Data"
'## Parameters.Add "Report: Historical: Designer: TM_Skill Interval: Export Data","_Desc"
'## Parameters.Add "Reports","_Catalog"
'## Parameters.Add "2","_Action"
'## Parameters.Add "1","_Quit"
'## Parameters.Add "Historical\Designer\TM_Skill Interval","_Report"
'## Parameters.Add "1","_ACD"
'## Parameters.Add "390","_Top"
'## Parameters.Add "45","_Left"
'## Parameters.Add "19110","_Width"
'## Parameters.Add "14595","_Height"
'## Parameters.Add "The report Historical\Designer\TM_Skill Interval was not found on ACD 1.","_ReportNotFound"
'## Parameters.Add "*","_BeginProperties"
'## Parameters.Add "151;201;203;289;290;287;288;265;266;263;264;1017;1201;1018;1202;271;272;269;270;1205;328;329","Splits/Skills"
'## Parameters.Add "13/02/2013","Date"
'## Parameters.Add "00:00-23:30","Times"
'## Parameters.Add "*","_EndProperties"
'## Parameters.Add "*","_BeginViews"
'## Parameters.Add "*","_EndViews"
'## Parameters.Add "C:\Documents and Settings\VT0189\Desktop\Daily RTM Report\Extract Data\TechnicalRaw.xls","_Output"
'## Parameters.Add "9","_FldSep"
'## Parameters.Add "0","_TextDelim"
'## Parameters.Add "True","_NullToZero"
'## Parameters.Add "True","_Labels"
'## Parameters.Add "False","_DurSecs"

On Error Resume Next

cvsSrv.Reports.ACD = 1
Set Info = cvsSrv.Reports.Reports("Historical\Designer\TM_Skill Interval")

If Info Is Nothing Then
If cvsSrv.Interactive Then
MsgBox "The report Historical\Designer\TM_Skill Interval was not found on ACD 1.", vbCritical Or vbOKOnly, "Avaya CMS Supervisor"
Else
Set Log = CreateObject("ACSERR.cvsLog")
Log.AutoLogWrite "The report Historical\Designer\TM_Skill Interval was not found on ACD 1."
Set Log = Nothing
End If
Else

b = cvsSrv.Reports.CreateReport(Info,Rep)
If b Then

Rep.Window.Top = 390
Rep.Window.Left = 45
Rep.Window.Width = 19110
Rep.Window.Height = 14595



Rep.SetProperty "Splits/Skills","151;201;203;289;290;287;288;265;266;263;264;1017;1201;1018;1202;271;272;269;270;1205;328;329"

Rep.SetProperty "Date","13/02/2013"

Rep.SetProperty "Times","00:00-23:30"




b = Rep.ExportData("C:\Documents and Settings\VT0189\Desktop\Daily RTM Report\Extract Data\TechnicalRaw.xls", 9, 0, True, True, False)





Rep.Quit



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

End If
Set Info = Nothing
'## cvs_cmd_end

I really should stop doing all the work for you guys , I am very busy at this time but if I get a spare moment I will try to do it. Meanwhile see if you can figure it out, read the other posts. You have everything you need, it's simple.

If anyone else reading this can help and have time please jump in.

OK just got a bit of time and wrote this - Not tested. Try it and see if you can work it out and getting running.

Sub CMSGetReport()
' www.fracta.net Edgar badawy 2013
Dim lwbk As Workbook
Dim lws As Worksheet
Set lwbk = ThisWorkbook
Set lws = lwbk.Sheets(1)
lws.Range("b2:e65536").ClearContents
Const Username As String = "yourusename" '' replace
Const Password1 As String = "yourpassword" ''' replace

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("ACSUP.cvsApplication")
Set cvsConn = CreateObject("ACSCN.cvsConnection")
Set cvsSrv = CreateObject("ACSUPSRV.cvsServer")
Set Rep = CreateObject("ACSREP.cvsReport")

If cvsApp.CreateServer(Username, "", "", "10.21.150.96", False, "ENU", cvsSrv, cvsConn) Then

If cvsConn.Login(Username, Password1, "10.21.150.96", "ENU") Then
On Error Resume Next

cvsSrv.Reports.ACD = 1
Set Info = cvsSrv.Reports.Reports("Historical\Designer\TM_Skill Interval")

If Info Is Nothing Then
If cvsSrv.Interactive Then
MsgBox "The report Historical\Designer\TM_Skill Interval was not found on ACD 1.", vbCritical Or vbOKOnly, "Avaya CMS Supervisor"
Else
Set Log = CreateObject("ACSERR.cvsLog")
Log.AutoLogWrite "The report Historical\Designer\TM_Skill Interval was not found on ACD 1."
Set Log = Nothing
End If
Else

b = cvsSrv.Reports.CreateReport(Info, Rep)
If b Then

Rep.SetProperty "Splits/Skills", "151;201;203;289;290;287;288;265;266;263;264;1017;1201;1018;1202;271;272;269;270;1205;328;329"
Rep.SetProperty "Date", "13/02/2013"
Rep.SetProperty "Times", "00:00-23:30"

b = Rep.ExportData("C:\Documents and Settings\VT0189\Desktop\Daily RTM Report\Extract Data\TechnicalRaw.xls", 9, 0, True, True, False)


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


lws.Activate
lws.Paste Destination:=lws.Cells(1, 1)

Rep.Quit

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

End If

End If

cvsApp.Servers.Remove cvsSrv.ServerKey
cvsCon.Logout
cvsCon.Disconnect

Set Log = Nothing
Set Rep = Nothing
Set cvsSrv = Nothing
Set cvsConn = Nothing
Set cvsApp = Nothing


End Sub

Sorry roller,

the coding not function. in sheet 1, it only appear coding that you just wrote. it any way for me to share to you the print screen?