Good day! I am working on a code that will extract data from CMS. It pulls up data per interval. I have 2 sets of reports with 3 different skills per report. One report pulls up fine, however, the other report pulls up the report only of 1 skill and pastes it all on the sheets for the other skills. I made sure that the skills are correct when i test the code line per line. Any suggestions?
Code:
Function cmsConn(sUserID As String, sPassword As String, sServerIP As String, sSkill As String, sDate As String, sReport, sReportloc)
Dim cvsApp As cvsApplication 'file required - acsApp.exe
Dim cvsSrv As cvsServer 'file required - acsSRV.exe
Dim CVSConn As cvsConnection 'file required - cvsCONN.dll
Dim iServer As Integer
Dim bConnected As Boolean
bConnected = False
Set cvsApp = New cvsApplication
Set cvsSrv = New cvsServer
Set CVSConn = New cvsConnection
'Checks to see if already connected to server
For iServer = 1 To cvsApp.Servers.Count
Set cvsSrv = cvsApp.Servers(iServer)
If cvsSrv.ServerKey Like "*\" & sServerIP & "\*\*\*" Then
bConnected = True
Exit For
End If
Next iServer
'Initiates connection if one is not yet established
If bConnected = False Then
If cvsApp.CreateServer(sUserID, sPassword, "", sServerIP, False, "ENU", cvsSrv, CVSConn) Then
If CVSConn.Login(sUserID, sPassword, sServerIP, "ENU") Then
End If
End If
End If
'Executes CMS report
Dim cvsRepInfo As Object
Dim cvsRepProp As Object
Dim cvsLog As Object
Dim b As Boolean
'********************** Customize report and its properties between the green lines ***********************
cvsSrv.Reports.ACD = 1
Set cvsRepInfo = cvsSrv.Reports.Reports(sReportloc & sReport)
'Set cvsRepInfo = cvsSrv.Reports.Reports("Real-Time\CMS custom\OVERALLSYS3")
If cvsRepInfo Is Nothing Then
If cvsSrv.Interactive Then
MsgBox "The report was not found on ACD 1.", vbCritical Or vbOKOnly, "Avaya CMS Supervisor"
Else
Set cvsLog = CreateObject("ACSERR.cvsLog")
cvsLog.AutoLogWrite "The report was not found on ACD 1."
Set cvsLog = Nothing
End If
Else
b = cvsSrv.Reports.CreateReport(cvsRepInfo, cvsRepProp)
If b Then
cvsRepProp.Window.Top = 40
cvsRepProp.Window.Left = 40
cvsRepProp.Window.Width = 40
cvsRepProp.Window.Height = 40
'MsgBox sSkill
cvsRepProp.SetProperty "Split/Skill", sSkill
'cvsRepProp.SetProperty "Refresh Every", "30"
cvsRepProp.SetProperty "Date(s)", sDate
cvsRepProp.SetProperty "Times", "00:00-23:30"
b = cvsRepProp.ExportData("", 9, 0, True, True, True)
'************************************************************************************************************
'Closes report
If bConnected = True Then
cvsRepProp.Quit
Else
If Not cvsSrv.Interactive Then cvsSrv.ActiveTasks.Remove cvsRepProp.TaskID
End If
Set cvsRepProp = Nothing
End If
End If
'Terminates server instance and connection
Set cvsRepInfo = Nothing
If Not cvsSrv.Interactive Then cvsApp.Servers.Remove cvsSrv.ServerKey
If bConnected = False Then
CVSConn.Logout
CVSConn.Disconnect
cvsSrv.Connected = False
End If
Set CVSConn = Nothing
Set cvsSrv = Nothing
Set cvsApp = Nothing
End Function
Sub Export_AHT()
Application.ScreenUpdating = False
Sheets("INFO").Visible = True
Sheets("INFO").Select
Call Export_AHT
Sheets("INFO").Visible = False
Sheets("Summary").Select
Range("A1").Select
MsgBox ("AHT Data Extracted!")
End Sub
Sub Export_AHT()
'i have 3 codes like this that assign three different codes like this that assign different values based on the skill
Application.ScreenUpdating = False
Sheets("INFO").Select
Dim UserID As String
Dim Password As String
UserID = Sheets("INFO").Range("C2").Value
Password = Sheets("INFO").Range("C3").Value
Dim Skill As String
Dim CHAServer As String
Dim DateNeeded As String
Dim CMSReport As String
Dim ReportLoc As String
Dim SheetName As String
Skill = Sheets("INFO").Range("E8").Value
CHAServer = Sheets("INFO").Range("F8").Value
DateNeeded = Sheets("INFO").Range("G8").Value
CMSReport = Sheets("INFO").Range("H8").Value
ReportLoc = Sheets("INFO").Range("I8").Value
SheetName = Sheets("INFO").Range("J8").Value
'Function CMSConn(sUserID As String, sPassword As String, sServerIP As String, sSkill As String, sDate As String, sReport)
Call cmsConn(UserID, Password, CHAServer, Skill, DateNeeded, CMSReport, ReportLoc)
Sheets(SheetName).Select
Range("A1").Select
ActiveSheet.Paste
Sheets("INFO").Select
End Sub
Topic Author
