Question Excel VBA macro to automate Avaya CMS Supervisor reports

same version, same settings as my laptop. Works for one server but the other two gets the same OLE error message. Checked their Excel settings, all proper items checked, pretty much the same as mine (doesn't have the analysis pack installed). All three Avaya CMS instances are the same versions as mine, same connect string (minus login and passcode). Even using my login he gets the same OLE message.

BTW, thanks for all you have been doing, using much of your code and love it.

rander

Interesting could it be a user permission on the local pc? Are you on a corporate network? What if you log on with your id on the his pc then run the script with his Avaya password. Not sure if this has anything to do with it but if you can edit files on c drive and he can't maybe that's it. One of my examples updates the log files I think.

Good Day Roller,

My name is Ulises, I work as WFM Supervisor, I do a lot of reporting from Avaya so that mean a lot of copy pasting, though your posts, I have been able to get Avaya to extract data, but not in the way that I would like to get it.

I will appreciate if you could help me to figure how to get it done as I wish:

The report I’m copying is located at :
Commands/Historical/Designer/SIMS (custom Report)
I copy the report but as export all data, I would like to copy the report as Export Table Data only.
I need to do this for 12 different skills, so I have to restart and input the new skills and repeat the same multiple times and paste the code in the same workbook in different sheets.
I will truly appreciated your help and donate to your post, if you could please help me to figure this out
Please see my code below:

Sub GetData()



Dim ReportName As String
ReportName = "Historical\Designer\SIMS"
Dim TextName, TextPath, WholePath, WithConnection As String
TextName = "Export"
TextPath = "C:\Users\uortega\Documents\"
WholePath = TextPath & TextName & ".txt"
WithConnection = "TEXT;" & TextPath & TextName & ".txt"


Dim LastRow As Range

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
Dim CMSRunning As String
Dim objWMIcimv2 As Object
Dim objProcess As Object
Dim objList As Object

CMSRunning = "acsSRV.exe"

Set objWMIcimv2 = GetObject("winmgmts:" _
& "{impersonationLevel=impersonate}!\\.\root\cimv2") 'Connect to CIMV2 Namespace

Set objList = objWMIcimv2.ExecQuery _
("select * from win32_process where name='" & CMSRunning & "'") 'determine if CMS is running

If objList.Count = 0 Then 'If 0 then process isn't running
GoTo e:
End If

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

On Error GoTo e:

Application.ScreenUpdating = 1

Set cvsSrv = cvsApp.Servers(1)
Application.ScreenUpdating = 1

RpDate = ExtRpDate

'Start code from CMS Export script
On Error Resume Next

cvsSrv.Reports.ACD = 1
Set Info = cvsSrv.Reports.Reports(ReportName)

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

Rep.Window.Top = 1830
Rep.Window.Left = 975
Rep.Window.Width = 17610
Rep.Window.Height = 11910

AgGrp = "187;71;308;19;186;182"

RpDate = "-1"

Rep.SetProperty "Splits/Skills", AgGrp 'change as needed for report variables

Rep.SetProperty "Dates", RpDates 'change as needed for report variables

Rep.SetProperty "Times", RpTimes 'change as needed for report variables

b = Rep.ExportData("C:\Users\uortega\Documents\ExportTest.txt", 44, 1, True, True, False)
Rep.Quit

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

Set Info = Nothing

With ActiveSheet.QueryTables.Add(Connection:= _
"TEXT;C:\Users\uortega\Documents\ExportTest.txt", _
Destination:=ActiveCell())
.Name = "Splits/Skills"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.TextFilePromptOnRefresh = False
.TextFilePlatform = 437
.TextFileStartRow = 1
.TextFileParseType = xlDelimited
.TextFileTextQualifier = xlTextQualifierDoubleQuote
.TextFileConsecutiveDelimiter = False
.TextFileTabDelimiter = False
.TextFileSemicolonDelimiter = False
.TextFileCommaDelimiter = True
.TextFileSpaceDelimiter = False
.TextFileColumnDataTypes = Array(1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1)
.TextFileTrailingMinusNumbers = True
.Refresh BackgroundQuery:=False
End With
' End code from CMS Export Script


Kill WholePath

cvsCONN.logout
cvsCONN.Disconnect
cvsSrv.Connected = False
Set Log = Nothing
Set Rep = Nothing
Set cvsSrv = Nothing
Set cvsCONN = Nothing
Set cvsApp = Nothing
Set Info = Nothing


e:
If Err.Number <> 53 Then
MsgBox Err.Number & "- You must be logged into CMS Supervisor before running this macro." & vbCrLf & vbCrLf & "Please log into CMS Supervisor and try again."
End If


Workbooks.OpenText Filename:=WholePath, Origin _
:=437, StartRow:=1, DataType:=xlDelimited, TextQualifier:=xlDoubleQuote _
, ConsecutiveDelimiter:=False, Tab:=False, Semicolon:=False, Comma:= _
True, Space:=False, Other:=False, FieldInfo:=Array(Array(1, 1), Array(2, 1), _
Array(3, 1), Array(4, 1), Array(5, 1), Array(6, 1), Array(7, 1), Array(8, 1), Array(9, 1), _
Array(10, 1), Array(11, 1), Array(12, 1), Array(13, 1), Array(14, 1), Array(15, 1), Array( _
16, 1), Array(17, 1), Array(18, 1), Array(19, 1), Array(20, 1)), TrailingMinusNumbers _
:=True


End Sub



Looking forward to get this resolve, Ulises!

No need to submit multiple posts I can see them all but your posts needs to be approved before you can see them due to spam. I will check this or later when I get some free time hopefully later today.

OK Ulises I finally had a look into your request. I have received many request recently where they asked to be able to automate the export of Avaya CMS reports, multiple ones, into different Excel sheets without having to re-run the basic script over and over.

I figured I should spend some time putting an easy to configure file together and make it available for download to anyone who needs it.

The download is here for a small fee:

fracta.net/products/index.php?main_page=...ath=1&products_id=12

Quick instructions

The purpose of this script is to allow you to run multiple CMS reports and save the results on different sheets.

First fill in your Server IP or Server name for the Avaya CMS, This can be found in the application settings, and enter your user name and password in the cells B1 to B3.

The CMS ACD is hard coded to ACD 1 and if you use another got to the VBA editor and edit in there.

From row 7 downwards enter the report name – now this has to be same as you see in the .acsauto script file. To get the .acsauto file save the report script from the report menu and open it in Notepad, you will see some code written in VB. Look for the exact name and enter, I left few examples in the file for you.

Enter the property name and value, these are also easily found in the .acsauto file or you can see the in the GUI. Make sure the spelling is exact, Date or Dates makes a difference.

If your report has two properties leave the third one blank.

On the last column enter the sheet name where the output will be exported to, I left three sheets as example in this workbook.

Click the RUN button and give it few seconds and the results will be there. With each subsequent run the new results will be appended to the bottom of the old results on each sheet.

Hi,

Can you help me with a new report?

As I can see, in the first post, the report is for the agents but I want a report for two skills in order to see the information (Number of calls, aban calls, …).

Could you help me to extract a simple report of this?

Thanks in advance.