× Welcome to the fracta.net forum!

Share your coding ideas or ask questions.

How? CMS data automatic export to Excel

More
4 years 7 months ago #595 by roller
OK, the original error was due to the spelling mistake. Now make sure you add back that 'on error resume next' line because it is needed.

As for the waiting on OLE error this maybe due to the fact you have to many orphaned Server and Connection Objects in the memory. You see every time you ran the code before and it failed you created those Objects in the memory and never had the chance to clean up as the code never reached the bottom where they are set to Nothing. To check this open the Windows Task Manager (CTRL + ALT + DEL) and look under processes. If you see many that look like acsApp, acsSRV, acs###, etc... then you have overwhelmed the application with dead Objects and it will not create any more connections for you.

To fix this close the Avaya application and terminate all those processes, or even better restart you PC. Then try running the code again and see if it goes past this line that it is getting stuck on.

Please Log in or Create an account to join the conversation.

More
4 years 5 months ago #613 by rahulr79
Hi Guys
I am trying to run below the code, but I am getting an error called "ActiveX component can't create object.
Sub getReps()
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 i As Integer
Dim repDate As String

UserForm1.Show
repDate = UserForm1.TextBox1.Value

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


serverAddress = "CMS"
For iServer = 1 To cvsApp.Servers.Count
    Set cvsSrv = cvsApp.Servers(iServer)
Set cvsConn = cvsSrv.Connection
    If cvsSrv.ServerKey Like "*" & serverAddress & "\*\*\*" Then
        bConnected = True
        Exit For
    End If
Next iServer
ThisWorkbook.Sheets(1).Cells.Clear
ThisWorkbook.Sheets(2).Cells.Clear
ThisWorkbook.Sheets(3).Cells.Clear

If bConnected = True Then
    
    cvsSrv.Reports.ACD = 1
    
   Set Info = cvsSrv.Reports.Reports("Historical\Agent\Group Summary Daily")

   If Info Is Nothing Then
      If cvsSrv.Interactive Then
          MsgBox "The report Historical\Agent\Group Summary Daily was not found on ACD 1.", vbCritical Or vbOKOnly, "Avaya CMS Supervisor"
      Else
          Set Log = CreateObject("ACSERR.cvsLog")
          Log.AutoLogWrite "The report Historical\Agent\Group Summary Daily was not found on ACD 1."
          Set Log = Nothing
      End If
   Else
       b = cvsSrv.Reports.CreateReport(Info, Rep)
       If b Then
          Rep.SetProperty "Agent Group", "Sales"
          'Rep.SetProperty "Date", "24/12/2013"
          Rep.SetProperty "Date", repDate
          b = Rep.ExportData("", 9, 0, True, True, False)
          Rep.Quit
          ThisWorkbook.Sheets(1).Cells(1, 1).PasteSpecial
          ThisWorkbook.Sheets(1).Range("A1:A2").EntireRow.Delete
              If Not cvsSrv.Interactive Then cvsSrv.ActiveTasks.Remove Rep.TaskID
          Set Rep = Nothing
       End If

   End If
   
      Set Info = cvsSrv.Reports.Reports("Historical\Agent\Group AUX Daily")

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

       b = cvsSrv.Reports.CreateReport(Info, Rep)
       If b Then
          Rep.Window.Top = 4740
          Rep.Window.Left = 6510
          Rep.Window.Width = 12195
          Rep.Window.Height = 6270
          Rep.SetProperty "Agent Group", "Sales"

          Rep.SetProperty "Date", repDate
          b = Rep.ExportData("", 9, 0, True, True, False)
          Rep.Quit
          ThisWorkbook.Sheets(2).Cells(1, 1).PasteSpecial
          ThisWorkbook.Sheets(2).Range("A1:A2").EntireRow.Delete
              If Not cvsSrv.Interactive Then cvsSrv.ActiveTasks.Remove Rep.TaskID
          Set Rep = Nothing
       End If
   End If
    
    Set Log = Nothing
    Set Rep = Nothing
    Set cvsSrv = Nothing
    Set cvsConn = Nothing
    Set cvsApp = Nothing

End If
Application.ScreenUpdating = True

End Sub

Please Log in or Create an account to join the conversation.

More
4 years 5 months ago #614 by roller
Are you using the 64bit version of Excel or 32 bit?

Please Log in or Create an account to join the conversation.

More
4 years 5 months ago #615 by rahulr79

Please Log in or Create an account to join the conversation.

More
4 years 5 months ago #616 by roller
I just pasted your code exactly as is in my Excel and change the serverAddress and agent group names to match mine and it worked just fine. With this code you need to make sure the CMS application is installed and running on the PC you are using the macro on - that's obvious but I better check that you know that.

Which line of code is generating the error?

Please Log in or Create an account to join the conversation.

More
4 years 5 months ago #617 by rahulr79
Yep I am currently logged in and running the cms.
Below is the line where it is giving the error

Set cvsApp = CreateObject("ACSUP.cvsApplication")

thanks

Please Log in or Create an account to join the conversation.

Time to create page: 0.265 seconds
Powered by Kunena Forum