my company uses all 3 cms verion. My code work great for 9 & 11. However 13 will not export. there are no error msg. the loop just keeps going as there seems to be no data coming from the application. this is where it keeps looping:
freetmpnum = FreeFile()
For i = 1 To 9
Line Input #1, linestring
Next
While Not EOF(1)
Line Input #1, linestring
Outline = Location & "," & Skill & "," & incDate & "," & linestring
Print #2, Outline
Wend
Please see below the complete code:
I can be reach @
robert.wynter@rci.rogers.com
wynter@rogers.com
ltworf13@hotmail.com - messenger only not email on this one
freetmpnum = FreeFile()
For i = 1 To 9
Line Input #1, linestring
Next
While Not EOF(1)
Line Input #1, linestring
Outline = Location & "," & Skill & "," & incDate & "," & linestring
Print #2, Outline
Wend
Please see below the complete code:
Code:
Sub getCMSIntervalData()
Dim fs As Scripting.FileSystemObject
Dim info As Object
Dim REP As Object
Dim b As Boolean
Dim cvsapp9 As New CVSUP.cvsApplication
Dim cvssrv9 As New CVSUPSRV.cvsServer
'Dim cvsconn As cvsConnection
Dim status As Boolean
Dim log As Object
Dim cvsSrv As Object
Dim incDate As Date
Dim freetmpnum As Integer
Dim linestring As String
Dim tmp As String
Dim startdate As Date
Dim enddate As Date
Dim Header As String
Dim outfile As String
Dim CMSReport As String
'DELETE OLD CSV TEMP FILES
Dim batfile As Variant
batfile = Shell("\\WCAB855766\ACCESS\DeleteExportFile_tmpexpcms.Bat", vbHide)
Header = """Location""," & """Skill""," & """Date""," & """startint""," & """dummy"","
Header = Header & """endint""," & """SeviceLevel""," & """ACDCallsWflowOut""," & """AvgSpeedAns"","
Header = Header & """AvgAbanTime""," & """ACDCalls ""," & """AvgACDTime"","
Header = Header & """AvgACWTime""," & """AbanCalls""," & """MaxDelay""," & """FlowIn"","
Header = Header & """FlowOut""," & """ExtnOutCalls""," & """BAHT"","
Header = Header & """OtherTime""," & """acdTime""," & """AuxTime""," & """AvgPosStaff""," & """Occupancy"""
ExportCVfile = "\\wcab855766\f\intervals\CMSIntervals\tmpexpcms.csv"
tmp = "\\wcab855766\f\intervals\CMSIntervals\tmpdata.csv"
outfile = "\\wcab855766\f\intervals\CMSIntervals\intervaldata.csv"
strSQLIP = "SELECT tblCMSData.IP, tblCMSData.Login, tblCMSData.Password, tblCMSData.CMSVersion " & _
"FROM tblCMSData " & _
"GROUP BY tblCMSData.IP, tblCMSData.Login, tblCMSData.Password, tblCMSData.CMSVersion"
DoCmd.OpenQuery "qryUpdate_CMDData_Date"
' DoCmd.RunSQL "UPDATE tblCMSData SET tblCMSData.EndDate = format(Now(),""mm/dd/yyyy"")"
Open tmp For Output As #2
Print #2, Header
Set dbCMStemp = OpenDatabase(CurrentDb.Name)
Set rsIP = dbCMStemp.OpenRecordset(strSQLIP, dbOpenDynaset, dbReadOnly)
With rsIP
While Not rsIP.EOF
CurrentIP = ![IP]
Login = ![Login]
password = ![password]
CMSVersion = ![CMSVersion]
Select Case CMSVersion
Case 9
Set cvsapp9 = CreateObject("CVSUP.cvsApplication")
status = cvsapp9.CreateServer(Login, password, "", CurrentIP, False, "ENU", cvssrv9)
Case 11
Dim cvsapp11 As New AVSUP.cvsApplication
status = cvsapp11.CreateServer(Login, password, "", CurrentIP, False, "ENU", cvsSrv)
Case 13
' Dim cvsapp13 As Object
Dim cvsConn As Object
Dim cvsapp13 As New ACSUP.cvsApplication
' Create a new CMS Application, Connection, Server, & Report
Set cvsapp13 = CreateObject("ACSUP.cvsApplication")
Set cvsConn = CreateObject("ACSCN.cvsConnection")
Set cvsSrv = CreateObject("ACSUPSRV.cvsServer")
Set REP = CreateObject("ACSREP.cvsReport")
' Assign values to the instance of the cvsServer created above
status = cvsapp13.CreateServer(Login, password, "", CurrentIP, False, "ENU", cvsSrv, cvsConn)
Case Else
MsgBox "CMSVersion missing"
End Select
strSQLRec = "SELECT * FROM tblCMSData where IP = '" & CurrentIP & "'"
Set rsRec = dbCMStemp.OpenRecordset(strSQLRec, dbOpenDynaset, dbReadOnly)
With rsRec
While Not rsRec.EOF
Skill = ![Skill]
startdate = ![startdate]
enddate = ![enddate]
Location = ![Location]
CMSReport = ![CMSReport]
sDate = CStr(startdate)
eDate = CStr(enddate)
Dim ReturnValue As Variant
Dim maxvalue As Double
Dim stepp As Double
' maxvalue = 700000
' stepp = 100 / maxvalue
' ReturnValue = SysCmd(SYSCMD_INITMETER, Location & " " & Skill & " " & sDate, 100)
If sDate <= eDate Then
Select Case CMSVersion
Case 13
For incDate = sDate To eDate
maxvalue = 700000
stepp = 100 / maxvalue
ReturnValue = SysCmd(SYSCMD_INITMETER, "CentreVu " & "" & Location & " " & Skill & " " & incDate, 100)
strSQLDelete = "Delete * from tblCMSIntervalData where Date = #" & incDate & "# and Location = '" & Location & "' and Skill = " & Skill
DoCmd.RunSQL strSQLDelete
On Error Resume Next
cvsSrv.Reports.ACD = 1
Set info = cvsSrv.Reports.Reports(CMSReport)
If info Is Nothing Then
If cvsSrv.Interactive Then
MsgBox "The report Historical\Designer\Johns Interval Performance Report2 was not found on ACD 1.", vbCritical Or vbOKOnly, "Avaya CMS Supervisor"
Else
Set log = CreateObject("ACSERR.cvsLog")
log.AutoLogWrite "The report Historical\Designer\Johns Interval Performance Report2 was not found on ACD 1."
Set log = Nothing
End If
Else
b = cvsSrv.Reports.CreateReport(info, REP)
If b Then
REP.Window.Top = 1830
REP.Window.Left = 7800
REP.Window.Width = 15030
REP.Window.Height = 14865
REP.SetProperty "Split/Skill", Skill
REP.SetProperty "Date", incDate
REP.SetProperty "Times", "00:00-23:30"
b = REP.ExportData(ExportCVfile, 44, 0, True, True, True)
REP.Quit
'If Not cvssrv.Interactive Then cvssrv.ActiveTasks.Remove REP.TaskID
Set REP = Nothing
Else
Set rsmissing = dbIDPtemp.OpenRecordset("tblMissingCMS", dbOpenDynaset)
With rsmissing
.AddNew
!Date = incDate
!Location = Location
!Skill = Skill
.Update
End With
rsmissing.Close
Set rsmissing = Nothing
End If
End If
Set info = Nothing
Open ExportCVfile For Input As #1
freetmpnum = FreeFile()
For i = 1 To 9
Line Input #1, linestring
Next
While Not EOF(1)
Line Input #1, linestring
Outline = Location & "," & Skill & "," & incDate & "," & linestring
Print #2, Outline
Wend
Close #1
Next incDate
' Else: End If
'
' .MoveNext
'
' Wend
' End With
'
' rsRec.Close
' Set rsRec = Nothing
Case 11
For incDate = sDate To eDate
maxvalue = 700000
stepp = 100 / maxvalue
ReturnValue = SysCmd(SYSCMD_INITMETER, "CentreVu " & "" & Location & " " & Skill & " " & incDate, 100)
strSQLDelete = "Delete * from tblCMSIntervalData where Date = #" & incDate & "# and Location = '" & Location & "' and Skill = " & Skill
DoCmd.RunSQL strSQLDelete
cvsSrv.Reports.ACD = 1
Set info = cvsSrv.Reports.Reports(CMSReport)
If info Is Nothing Then
If cvsSrv.Interactive Then
MsgBox "The report Historical\CMS custom\SK Sum by Int Rev2 was not found on ACD 1.", vbCritical Or vbOKOnly, "CentreVu Supervisor"
Exit Sub
Else
Set log = CreateObject("CVSERR.cvsLog")
log.AutoLogWrite "The report Historical\CMS custom\SK Sum by Int Rev2 was not found on ACD 1."
Set log = Nothing
Exit Sub
End If
Else
b = cvsSrv.Reports.CreateReport(info, REP)
If b Then
REP.Window.Top = 2835
REP.Window.Left = 420
REP.Window.Width = 14580
REP.Window.Height = 5910
REP.SetProperty "Split/Skill", Skill
REP.SetProperty "Date", incDate
REP.SetProperty "Times", "00:00-23:30"
b = REP.ExportData(ExportCVfile, 44, 0, True, True, True)
REP.Quit
'If Not cvssrv.Interactive Then cvssrv.ActiveTasks.Remove REP.TaskID
Set REP = Nothing
Else
Set rsmissing = dbIDPtemp.OpenRecordset("tblMissingCMS", dbOpenDynaset)
With rsmissing
.AddNew
!Date = incDate
!Location = Location
!Skill = Skill
.Update
End With
rsmissing.Close
Set rsmissing = Nothing
End If
End If
Set info = Nothing
Open ExportCVfile For Input As #1
freetmpnum = FreeFile()
For i = 1 To 9
Line Input #1, linestring
Next
While Not EOF(1)
Line Input #1, linestring
Outline = Location & "," & Skill & "," & incDate & "," & linestring
Print #2, Outline
Wend
Close #1
Next incDate
' Else: End If
'
' .MoveNext
'
' Wend
' End With
'
' rsRec.Close
' Set rsRec = Nothing
Case 9
For incDate = sDate To eDate
maxvalue = 700000
stepp = 100 / maxvalue
ReturnValue = SysCmd(SYSCMD_INITMETER, "CentreVu " & "" & Location & " " & Skill & " " & incDate, 100)
strSQLDelete = "Delete * from tblCMSIntervalData where Date = #" & incDate & "# and Location = '" & Location & "' and Skill = " & Skill
DoCmd.RunSQL strSQLDelete
cvssrv9.Reports.ACD = 1
Set info = cvssrv9.Reports.Reports(CMSReport)
If info Is Nothing Then
If cvssrv9.Interactive Then
MsgBox "The report Historical\CMS custom\SK Sum by Int Rev2 was not found on ACD 1.", vbCritical Or vbOKOnly, "CentreVu Supervisor"
Exit Sub
Else
Set log = CreateObject("CVSERR.cvsLog")
log.AutoLogWrite "The report Historical\CMS custom\SK Sum by Int Rev2 was not found on ACD 1."
Set log = Nothing
Exit Sub
End If
Else
b = cvssrv9.Reports.CreateReport(info, REP)
If b Then
REP.Window.Top = 2835
REP.Window.Left = 420
REP.Window.Width = 14580
REP.Window.Height = 5910
REP.SetProperty "Split/Skill", Skill
REP.SetProperty "Date", incDate
REP.SetProperty "Times", "00:00-23:30"
b = REP.ExportData(ExportCVfile, 44, 0, True, True, True)
REP.Quit
'If Not cvssrv9.Interactive Then cvssrv9.ActiveTasks.Remove REP.TaskID
Set REP = Nothing
Else
Set rsmissing = dbIDPtemp.OpenRecordset("tblMissingCMS", dbOpenDynaset)
With rsmissing
.AddNew
!Date = incDate
!Location = Location
!Skill = Skill
.Update
End With
rsmissing.Close
Set rsmissing = Nothing
End If
End If
Set info = Nothing
Open ExportCVfile For Input As #1
freetmpnum = FreeFile()
For i = 1 To 9
Line Input #1, linestring
Next
While Not EOF(1)
Line Input #1, linestring
Outline = Location & "," & Skill & "," & incDate & "," & linestring
Print #2, Outline
Wend
Close #1
Next incDate
End Select
Else: End If
.MoveNext
Wend
End With
rsRec.Close
Set rsRec = Nothing
Select Case CMSVersion
Case 9
Set cvsSrv = Nothing
Set cvsapp9 = Nothing
Case 11
Set cvsSrv = Nothing
Set cvsapp11 = Nothing
End Select
.MoveNext
Wend
End With
rsIP.Close
Set rsIP = Nothing
Close #2
DoCmd.RunSQL "Delete * from tmpCMSIntervalData"
DoCmd.TransferText acImportDelim, "CMS Import Specification", "tmpCMSIntervalData", tmp
'THIS IS TO CONVERT ANY MONCTON DATE/TIME FROM ATL TO EST
'START INTERVAL DATE/TIME COVERTER
DoCmd.OpenQuery "A1_Start_Stop_to_24hrs" 'adds start stop time in 24 hour format to new field in tmp table"
DoCmd.OpenQuery "A2_Add_IntervalID" 'adds interval ID to new field in tmp table
DoCmd.OpenQuery "A3_Convert2_EST_IntervalID" 'uses new field (MonToEst) to update the TIME field"
DoCmd.OpenQuery "A4_Convert2_EST_Date" 'adds Date to new field in tmp table
DoCmd.OpenQuery "A5_Convert2_EST_Start_Stop_Time" 'adds EST start stop time in 24 hour format to new field in tmp table
'
DoCmd.SetWarnings False
DoCmd.OpenQuery "qapptblCMSIntervalDatafromtmp"
DoCmd.OpenQuery "C1_AllCentreVu_By_loc_By_Date"
DoCmd.OpenQuery "E1_mkINTERVALS_By_Day_Dept_Grp"
DoCmd.OpenQuery "E1_mkINTERVALS_By_Day_Loc_AGG"
DoCmd.OpenQuery "F1_mkTrend_By_Days"
DoCmd.OpenQuery "1_qry_RemoveNCO4Normalized"
DoCmd.OpenQuery "3_qryNormalized_Metrics_by_Interval"
DoCmd.RunSQL "UPDATE tblCMSData SET tblCMSData.startdate = ''"
DoCmd.RunSQL "UPDATE tblCMSData SET tblCMSData.EndDate = ''"
DoCmd.SetWarnings True
maxvalue = 700000
stepp = 100 / maxvalue
ReturnValue = SysCmd(SYSCMD_INITMETER, "CentreVu Completed", 100)
End SubI can be reach @
robert.wynter@rci.rogers.com
wynter@rogers.com
ltworf13@hotmail.com - messenger only not email on this one
