CMS Avaya to VBA report - Set up timezone

[rnicul]

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
etc.

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

Code:

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)
Rep.Quit


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


Set Info = Nothing


' End code from CMS Export Script




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




    Range("A1").Select
    ActiveSheet.Paste
    
    End Sub

Thank you guys.

[shaikht]

Hi,

I have no exposure to VB scripting but depending on supervisor design you need to make changes in your script to ask timezone from user or script need to pick it from list. Whenever we pull report from supervisor, it will pull report on default timezone (set on CMS server), if user want different timezone, user need to manually pick it from the configured list and then pull report.

I hope it helps.

[rnicul]

Hello, i am 100% positive that it works. Someone gave me a tip but it didn't work.

I know that i have to take it manually but unfortunately all the reports i have to get from CMS are based on different timezones, meaning i waste a lot of time doing this instead of automatize it.

Thank you for your response and maybe someone else will have another idea.

[rnicul]

Hello guys,

None of you use this way of reporting from avaya cms?

Maybe you have another way of reporting automatically from cms.. please share.

Thanks
Razvan

[fernandez12]

Originally Posted by rnicul

Hello guys,

None of you use this way of reporting from avaya cms?

Maybe you have another way of reporting automatically from cms.. please share.

Thanks
Razvan

Well I've to make a change on a .acsauto file that we have, luckily it can be edited via TXT notepad
I've found an entry in it:
Rep.TimeZone = "default" which can be of course edited
I've revised the timezones from the dropdown on the (almost)lower left corner of the 'select a report screen' and considering i have to update it to GMT-4 i should change that to 'US/Pacific' as it's specified on the report.

I want to know if I'm being overly simple regarding it or if any of you has encountered issues about it.

Thanks a lot for the hand!

[ramos214]

I've used this method before when I had the same problem with the timezone. Haven't encountered any issues with since.