Avaya Timezone and report date

[Leonidas75]

Hello,

I have the following vba code to run avaya reports into excel, however i have 3 issues i need to resolve. The first is i require code to set a timezone other than the default setting and secondly, if i run a report for a day less than the 10th of any month, the report date comes out wrong.

ie: 08/05/2015 will come back with report date for 05/08/2015.

The 3rd issue is, is there a code where i can avoid entering the login id and password if i am already logged into avaya?

Public Sub CMSConn()
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")
serverAddress = "cms"
mydate = "23/04/2010"
UserName = "yourUserName"
passW = "yourPassword"
agentName = "agent Name"
If cvsApp.CreateServer(UserName, "", "", serverAddress, False, "ENU", cvsSrv, cvsConn) Then
If cvsConn.login(UserName, passW, serverAddress, "ENU") Then
On Error Resume Next
cvsSrv.Reports.ACD = 1
Set Info = cvsSrv.Reports.Reports("Historical\Agent\Trace by Location")
If Info Is Nothing Then
If cvsSrv.Interactive Then
MsgBox "The Report " & "Historical\Agent\Trace by Location" & " was not found on ACD 1", vbCritical Or vbOKOnly, "CentreVu Supervisor"
Else
Set Log = CreateObject("ACSERR.cvslog")
Log.AutoLogWrite "The Report " & "Historical\Agent\Trace by Location" & " was not found on ACD 1"
Set Log = Nothing
End If
Else
b = cvsSrv.Reports.CreateReport(Info, Rep)
If b Then
Debug.Print Rep.SetProperty("Agent", agentName)
Debug.Print Rep.SetProperty("Dates", mydate)
Debug.Print Rep.SetProperty("Times", "00:00-23:59")
b = Rep.ExportData("", 9, 0, False, True, True)
Set wk = ThisWorkbook
wk.Sheets(1).Cells.ClearContents
wk.Sheets(1).Cells(1, 1).PasteSpecial

'b = Rep.ExportData(fileP, 9, 0, False, True, True)


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

Set Info = Nothing
End If

End If

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


End Sub

 

[Asala42]

It looks like it's trying to default the date to the wrong regional setting (say European vs US regional settings). You could try passing different date formats to see if it defaults to the version you intended, such as "2014-08-05".

I don't have a solution for your other two questions. I don't think there is one for the username/password issue though - my understanding is the usual process for that kind of thing is to have a separate application account used specifically for automation.

 

[Leonidas75]

I have managed to resolve the Timestamp issue. The only issue remaining is the date format. Originally i said up to 10th of any month but it is in fact the 12th (due to the 12 months of the year naturally).

If i manually run the avaya report and export it to excel the date comes through correctly. It only occurs when i run the vba code and i am not sure how to try passing different date formats you are suggesting. If you could please provide examples that would be great.

On closer inspection, i believe it is breaking down on the following line:

wk.Sheets(1).Cells(1, 1).PasteSpecial

When i attempt to paste special this report again directly below i am given 2 options and they are (from memory) : Unified Code and Text Only. Even when i select the 2 options they are pasted correctly.

The only other option i could possibly consider is exporting to a csv file but i dont know how to do that and secondly it would be a longer process to then revert it back to excel report.

Thoughts are greatly appreciated, however if you require any further information please let me know.

Thanks,

 

[Asala42]

I thought the issue was around this line:

mydate = "23/04/2010"

Maybe I misunderstood but I thought you were saying when you tried to pass a Date like "08/05/2015" Avaya was trying to run a report for "05/08/2015". I figured you could try setting mydate instead to "2015-08-05", a different but common date format, that Avaya may correctly read without accidentally swapping the Month/Day components.


Based on your second post though it sounds like what you're really saying is exported dates themselves are coming out wrong. That leaves the question of whether or not the dates are actually being brought in wrong or if it's just a minor problem that can be corrected with a quick reformatting.

Try tossing in this code towards the end, modified for whichever column is populated by dates.

Columns("A:A").Select
Selection.NumberFormat = "mmm-dd yyyy"

This will at least show you if it's a cosmetic format issue or if the month/day is getting swapped in the data export itself.

 

[Leonidas75]

Hi Asala, thank you for responding to my post again.

I just re-read my last post and it was not very clear - apologies. Let me re-phrase:

My issue is the date not exporting correctly so if i run a report for the 04/05/2015 it will come out as the 05/04/2015 (note: if i manually copy/paste report into excel the date comes through as i expect it to do).

If i run a report for the 13/05/2015 it will export correctly as the 13/05/2015 (regardless of whether this is done manually or through VBA).

I have tried various methods including the latest response above and still nothing.

I have also added another macro to format the cell the date is pasted into the excel spreadsheet to customise the date to dd/mm/yyyy without success.

 

[Asala42]

I have tried various methods including the latest response above and still nothing.

mm/dd/yyyy is the US standard date format, dd/mm/yyyy is the European standard - so double check Excel and Avaya's regional settings. That might end up being an easy fix.

What happened when you tossed the above code in? Applying a "mmm-dd yyyy" custom format to entries like "05/04/2015" should produce one of three results:
1) The date will stay the same (Excel is treating these entries as text)
2) It will change but still show the wrong month/day (eg change to "May-04 2015")
3) Or correct the actual problem (eg change to "Apr-05 2015" - meaning this is just a cosmetic format issue but the values themselves are actually the correct dates)

If you can't find a regional setting that needs correcting I'd probably just add some code to flip the month/day for dates that have a "day" <= 12. You could do this via formula in an adjacent column or a loop through cells, but the specifics depends on what exactly is getting exported (is excel seeing these entries as dates or text, are there trailing/leading spaces, are the dates in a single column or spread around, etc).