0

I had a excel report that extracts data from Avaya CMS, however they moved the Avaya CMS application from my workstation to a RD Web Access. Now the VBA is not working anymore since the old CMS that was installed on my workstation is now in a RD web access. Is it possible to access Avaya CMS on a RD web access to extract a report to excel thru VBA?

Thanks in advance!!

Sub GetIntervalData()

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")

'Clear Data
Sheets("Domestic Interval Data").Select
Range("A1:AR300").ClearContents

Sheets("Domestic").Activate

serverAddress = Range("B1").Value
UserName = Range("B2").Value
Password1 = Range("C2").Value

If cvsApp.CreateServer(UserName, "", "", serverAddress, False, "ENU", cvsSrv, cvsConn) Then

If cvsConn.Login(UserName, Password1, serverAddress, "ENU") Then

On Error Resume Next

   cvsSrv.Reports.ACD = 1
   Set Info = cvsSrv.Reports.Reports("Historical\Designer\APS Report (MultiSkill)")

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

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

          Rep.Window.Top = 75
          Rep.Window.Left = 690
          Rep.Window.Width = 19140
          Rep.Window.Height = 11400

                  Rep.TimeZone = "default"

         Rep.SetProperty "Split/Skills","1555;1551;1552;1553;1554;1570;1998;1999"

         Rep.SetProperty "Dates", "2/14/2018"

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

          b = Rep.ExportData("", 9, 0, False, False, 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

cvsApp.Servers.Remove cvsSrv.ServerKey
cvsConn.Logout
cvsConn.Disconnect
cvsSrv.Connected = False

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

Sheets("Domestic Interval Data").Select
Range("A1").Select
ActiveSheet.Paste

End If

Sheets("Domestic").Activate

End Sub

**This code creates a avaya cms session and runs a report, extracts it and pastes directly to excel

joe
  • 1
  • 1
  • 2
  • Is there a back-end you can access & query? Your question isn't answerable as it stands BTW. – Mathieu Guindon Jun 15 '18 at 16:34
  • i can access the Avaya CMS on the RD web access and run reports but now i have to manually get the data and then put it on my excel report, before the CMS was moved to the RD web access i can extract the CMS report automatically thru excel vba now it's not working anymore. – joe Jun 15 '18 at 16:43
  • R u looking for how to setup a file path to the RD web access? – 1986G1988 Jun 15 '18 at 16:47
  • try like this if you looking to setup file path= \\remotehost\path\to\somefolder\somefile.txt – 1986G1988 Jun 15 '18 at 16:48
  • Would help to explain exactly what you mean by "RD web access" – Tim Williams Jun 15 '18 at 16:52
  • @TimWilliams i am assuming he is mentioning microsoft remote desktop web access – 1986G1988 Jun 15 '18 at 16:54
  • the RD web access only have 4 applications and when i click on the CMS icon it will give me a link to open the CMS application. After clicking that link it will open the CMS. When my CMS was not in that RD web access i can just run the exceln vba and it will automatically extract that CMS data. – joe Jun 15 '18 at 16:58
  • Yes it's a rmicrosoft remote desktop web access provided by the company – joe Jun 15 '18 at 16:59
  • OK thought as much but wanted to confirm. Are you logging in to a remote desktop session, or do the 4 applications run "virtualized" on your local desktop? – Tim Williams Jun 15 '18 at 17:00
  • I do need to login to the remote desktop session to access the avaya cms application – joe Jun 15 '18 at 17:01
  • @joe Post the code that you have, then i think someone can help u out.Most probably the vba will be looking for a file/folder in local computer. By changing the file path to remote computer may be u will be able run it.. – 1986G1988 Jun 15 '18 at 17:02
  • @joe Edit you question and add your code there, try to explain it as much as possible, like , What error you are getting, what issues have e.t.c – 1986G1988 Jun 15 '18 at 17:09
  • That code creates a CMS session and runs a report and extracts data and pastes it directly to my excel file – joe Jun 15 '18 at 17:10

1 Answers1

0

It has been over seven years since I used Avaya CMS but I use to design reports out of the CMS Supervisor. Do you have access to this? If so you should be able to write a simple report that will extract the data you need. I will see if I can find my old how to guide.

  • The problem is the CMS is intalled on the remote desktop web access, i need a code that will access the CMS application on the remote web access so i can pull that data – joe Jun 15 '18 at 17:52
  • I see. The only thing I can think of is to see if you contact whoever runs the server that holds the database. Maybe they can give you access and you can use SQL to pull the data. –  Jun 15 '18 at 18:31