View Single Post
Old 01-05-2017, 01:52 AM
rnicul rnicul is offline
Join Date: Jan 2017
Posts: 3
rnicul has 10 reputation points
Default CMS Avaya to VBA report - Set up timezone

Hello guys,

Right now i am working on a new project where i have to automatize a dashboard.
This dashboard is getting information from avaya scripts.

I searched for the last 2 weeks a script in VBA that actually gets info from avaya reports and imports them to excel file in a certain sheet.

Well, my problem is that i have to export 1 report with 6 different timezones.

For example:
Historical\Designer\Multi Date Multi Split Skill interval - Europe/Brussels timezone
Historical\Designer\Multi Date Multi Split Skill interval - US/Eastern

Below is my VBA code that works with my cms but it does not take in account that my timezone is set as Europe/Brussels and it exports in default timezone.

Please, help me out so i can ease my work with few hours a week

Sub EMEA()

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

End If

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

Application.ScreenUpdating = 0

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

AgGrp = InputBox("Enter Agent Group Name", "Agent Group", "952;953;271;270;221;222;223;224;231;233;232;234;235;246;241;243;242;247;249;245;244;248;255;258;256;259;257;261;262;260") 'change as needed for variables to enter into report
RpDate = InputBox("Enter Date", "Date", "-1") 'change as needed for variables to enter into report

'Start code from CMS Export script
On Error Resume Next

cvsSrv.Reports.ACD = 1
Set Info = cvsSrv.Reports.Reports("Historical\Designer\Multi Date Multi Split Skill interval")

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

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

Rep.SetProperty "Dates", RpDate 'change as needed for report variables
Rep.SetProperty "Times", "00:00-23:30"
Rep.TimeZone = "Europe/Brussels"
b = Rep.ExportData("", 9, 0, True, True, True)

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

Set Info = Nothing

' End code from CMS Export Script

cvsSrv.Connected = False
Set Log = Nothing
Set Rep = Nothing
Set cvsSrv = Nothing
Set cvsConn = Nothing
Set cvsApp = Nothing
Set Info = Nothing

    End Sub
Thank you guys.
Reply With Quote