1

I've been using the following script to run Agent trace reports on call center agents daily for our primary call center site, which has between 40 and 50 agents at any given time. I'm now expanding this reporting to our other sites, having to run the report for 234 agents at a time. I tried setting the loop for the full list of 234 agents, and after the 63rd agent is completed, I get the OLE error.

I've tried adding 'DoEvents" and 'Application.Wait' throughout the code to slow things down a bit and to ensure that the process completes before the next starts , I've also broken up the code from 1 loop of 234 into 4 loops of 60 (the last loop is only 54 for you math junkies), but I'm still getting the OLE error after agent 63 completes.

How can I determine what is causing this OLE error?

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
 Dim Agents(234) As String
 Dim I As Integer

 Set wk = ThisWorkbook

 serverAddress = "xxx.xxx.xxx.xxx"
 mydate = InputBox("Dates: ")
 UserName = InputBox("User ID: ")
 passW = InputBox("Password: ")

Agents(1) = "Abcede, Ronaldo"
Agents(2) = "Abella, Poltaqn"
Agents(3) = "Ablen, Armie"
Agents(4) = "Abon, Sherry Lou"
Agents(5) = "Acbang, MaryJane"
Agents(6) = "Acbang, MaryJane"
Agents(7) = "Alcantara, Luis Jaim"
Agents(8) = "Alfaro, Nio"
Agents(9) = "Aman, Sherwin"
Agents(10) = "Amar, Catherine"
Agents(11) = "Amor, Charlie"
Agents(12) = "Anonuevo, Hubert"
Agents(13) = "Antonio, Brian Antho"
Agents(14) = "Antonio, Diana Grace"
Agents(15) = "Aragon, Ramiro"
Agents(16) = "Araullo, Keith"
Agents(17) = "Arevalo, Jerome"
Agents(18) = "Arnel Decano"
Agents(19) = "Arriola, Paulo"
Agents(20) = "Asturias, Sheena"
Agents(21) = "Baguhin, Kathrin May"
Agents(22) = "Balatbat, Lauro"
Agents(23) = "Balena, Nathan"
Agents(24) = "Ballos, Evelyn"
Agents(25) = "Banaban, Nona"
Agents(26) = "Bansale, Jemma"
Agents(27) = "Bautista, Bernie"
Agents(28) = "Bautista, Domingo J"
Agents(29) = "Belen, Michelle"
Agents(30) = "Bicaldo, Catherine"
Agents(31) = "Bonifacio, Allan"
Agents(32) = "Borbon, Danny"
Agents(33) = "Brian Patrick Roxas"
Agents(34) = "Bundalian, Kristel J"
Agents(35) = "Bundalian, Paul"
Agents(36) = "Cabasa, Aidel Joy"
Agents(37) = "Cabauatan, Jacquelin"
Agents(38) = "Caldina, Joanne Nico"
Agents(39) = "Calumpiano, Jean"
Agents(40) = "Camarao, Maria There"
Agents(41) = "Capucao, Daisy"
Agents(42) = "Carpio, Rusell"
Agents(43) = "Caunca, Michael John"
Agents(44) = "Cequeña, Dianne"
Agents(45) = "Chinen, Jeffrey"
Agents(46) = "Chrisgeena Clemeno"
Agents(47) = "Clarin, Marco Leandr"
Agents(48) = "Clauag, Martin Paul"
Agents(49) = "Corbilla. Marcel"
Agents(50) = "Cruz, Cherrimei"
Agents(51) = "Cruz, Geran Judeth"
Agents(52) = "Cruz, Lorreine"
Agents(53) = "Cuaresma Sheila Mari"
Agents(54) = "Cudiamat, Catherine"
Agents(55) = "Cudiamat, Paul"
Agents(56) = "Cueva, Ruthie"
Agents(57) = "Dalida, Rowell"
Agents(58) = "De Guzman, Jennelyn"
Agents(59) = "De Vera, Chris Evert"
Agents(60) = "DeGuzman, Aubrey"
Agents(61) = "DeGuzman, Noel"
Agents(62) = "Del Monte, Mark"
Agents(63) = "Dela Fuente, Herner"
Agents(64) = "Dela Mesa, Randy"
Agents(65) = "Delape Jr., Evelio"
Agents(66) = "10404"
Agents(67) = "Delos Santos, Ervin"
Agents(68) = "Diaz, Jerosalem"
Agents(69) = "Diaz, Leila Ruth"
Agents(70) = "Dicen, Melanie"
Agents(71) = "Dimailig, Kenneth"
Agents(72) = "Dizon, Marc Anthony"
Agents(73) = "Doblas, Leora"
Agents(74) = "Domingo, Rafael"
Agents(75) = "Dulay, Susana Marie"
Agents(76) = "Dy, Jerwin"
Agents(77) = "Elnas, Bernadette"
Agents(78) = "Elnas, Elsha Marie"
Agents(79) = "Enumerable, Gary"
Agents(80) = "Escobal, Aubrey"
Agents(81) = "Escorpion, Genix"
Agents(82) = "Espaldon, Eveleen"
Agents(83) = "Esquivel, Jean Paul"
Agents(84) = "Estabillo,Reygienald"
Agents(85) = "Esteban, Aaron Paul"
Agents(86) = "Esteban, Richard"
Agents(87) = "Fe Marie Jopia"
Agents(88) = "Fermin, Maricris"
Agents(89) = "Fresado, Marie"
Agents(90) = "Gagarin, Wilson"
Agents(91) = "Galero, Roxanne"
Agents(92) = "Gamboa, Aleli"
Agents(93) = "Garcia, Jayson"
Agents(94) = "Garcia, Rey"
Agents(95) = "Garganta, Angelie"
Agents(96) = "Gepolane, Jacinth Ka"
Agents(97) = "Geroche, Raul"
Agents(98) = "Gilera, Erwin"
Agents(99) = "Go, Mark Dexter"
Agents(100) = "Godillo, Albert"
Agents(101) = "Grospe, Noemi"
Agents(102) = "Gualin, Raymond"
Agents(103) = "Guela, Antonette"
Agents(104) = "Guevarra, Randy"
Agents(105) = "Guiang, Mary Paula"
Agents(106) = "Gumera, Diana Corina"
Agents(107) = "Hernandez, Jules"
Agents(108) = "Hora, Gene Patrick"
Agents(109) = "Hu Kwong Man"
Agents(110) = "Iballar, Maria Chris"
Agents(111) = "Ignacio, Florabel"
Agents(112) = "Ilaga, Eunice"
Agents(113) = "Inovero, Michelle"
Agents(114) = "Ismail, Amir"
Agents(115) = "Jaen, Ritchiedel"
Agents(116) = "Jimeno, Evita"
Agents(117) = "Joanne Marie Sai"
Agents(118) = "John Michael Abellar"
Agents(119) = "Julian, Desiree"
Agents(120) = "Justine Joy Lucban"
Agents(121) = "Justiniano, Eufraime"
Agents(122) = "Karen Cathley Digman"
Agents(123) = "Lamparas, Nenita"
Agents(124) = "Laxa, Catherine"
Agents(125) = "Lecoto, Vener"
Agents(126) = "Leuterio, Ilene"
Agents(127) = "Limbo, Karen"
Agents(128) = "Lingad, Raymond"
Agents(129) = "Liza, Joseph Jay"
Agents(130) = "Lomongo, Wilbert"
Agents(131) = "Loreto, Aguilar"
Agents(132) = "Lugtu , Jude"
Agents(133) = "Luzon, Liza"
Agents(134) = "Ma. Eleonor Tabing"
Agents(135) = "Mabborang, Queenly"
Agents(136) = "Mabini, Eve"
Agents(137) = "Macababda, Marijo"
Agents(138) = "Macutay, Charlotte"
Agents(139) = "Macutay, Mark Anthon"
Agents(140) = "Magtangob, Julius"
Agents(141) = "Manago, Roman Carlo"
Agents(142) = "Manaloto, Marlon"
Agents(143) = "Mangaoang, Carmel Ri"
Agents(144) = "Mapolon, Amie"
Agents(145) = "Martin, Maricar"
Agents(146) = "Martin, Sharon"
Agents(147) = "Mendoza, Dann Kristo"
Agents(148) = "Mendoza, Liezl"
Agents(149) = "Mesias, Hannah"
Agents(150) = "Miranda, Nicola Elan"
Agents(151) = "Miranda, Sheila"
Agents(152) = "Mirandilla, Paulo"
Agents(153) = "Mislang Jr., Roberto"
Agents(154) = "Montances, Raquel"
Agents(155) = "Montefalcon, Erliza"
Agents(156) = "Munar, Rommel"
Agents(157) = "Naluz, Alexis Viktor"
Agents(158) = "Narbuada, Norman"
Agents(159) = "Nirza, Miguel"
Agents(160) = "Orteza, Gian Carlo"
Agents(161) = "Oteyza, Karen Rosell"
Agents(162) = "Panganiban, Karina"
Agents(163) = "Panganiban, Leah"
Agents(164) = "Papina, Roland"
Agents(165) = "Parel, Rose Ann Chri"
Agents(166) = "Parra, Nimfa"
Agents(167) = "Pascua, Ramon"
Agents(168) = "Pasuquin, Mary Grace"
Agents(169) = "Pauig, Sue Laine"
Agents(170) = "Perlas, Joby Jade"
Agents(171) = "Poñado, Rachal"
Agents(172) = "Puli, Ivan"
Agents(173) = "Pumar, Elora Ann"
Agents(174) = "Quintos, Stephen Ed"
Agents(175) = "Ramirez, Requel"
Agents(176) = "Ramos, Eumir"
Agents(177) = "Ramos, Rosemarie."
Agents(178) = "Rarangol, Jane"
Agents(179) = "Rebugio, Mark Joseph"
Agents(180) = "Reotutar, Neil Clark"
Agents(181) = "Resurreccion, Aimee"
Agents(182) = "Reyes, Lymuel"
Agents(183) = "Ricafranca, Nikolai"
Agents(184) = "Rino, Kim Eric"
Agents(185) = "Rioja, Daisy"
Agents(186) = "Robidillo, Fatima"
Agents(187) = "Rolando Lindog"
Agents(188) = "Romero, Darling"
Agents(189) = "Rosas, Bryan Carlo"
Agents(190) = "Rowena Rona"
Agents(191) = "Sacrez, Ma Aurora"
Agents(192) = "Sai, Joanna Marie"
Agents(193) = "Salvacion, Fairodz b"
Agents(194) = "Salvador Jr, Rolando"
Agents(195) = "Santibanez, Norman"
Agents(196) = "Santos, Mayleen Sa"
Agents(197) = "Saribay, Mark"
Agents(198) = "Saulog, Giovannie"
Agents(199) = "Seludo, Loida"
Agents(200) = "Sereno, Marjorie"
Agents(201) = "Serrano, Zacheus"
Agents(202) = "Sheremeil Tan"
Agents(203) = "Silverio II, Carlo"
Agents(204) = "Tambobong , Regina"
Agents(205) = "Tan, Boon Beng"
Agents(206) = "Tapia, Dianne Andrea"
Agents(207) = "Tingzon, Jan Therese"
Agents(208) = "Tolentino, Glennford"
Agents(209) = "Tolentino, Rodney"
Agents(210) = "Tolosa, Joy Maru"
Agents(211) = "Tolosa, Rod Jeffrey"
Agents(212) = "Toong, Rene"
Agents(213) = "Torilla, Nathaniel"
Agents(214) = "Tort, Mary Grace"
Agents(215) = "Trinidad, Francis"
Agents(216) = "Tubice, Janette"
Agents(217) = "Tudlasan, Mary Jo"
Agents(218) = "Tuguib, Rodney"
Agents(219) = "Tulawan, Joedelyn"
Agents(220) = "Uy, Gretchen Mari St"
Agents(221) = "Valebia, Kristine"
Agents(222) = "Valenzuela, Demsey"
Agents(223) = "Valenzuela, Myles"
Agents(224) = "Vargas, Christine"
Agents(225) = "Vargas, Jose"
Agents(226) = "Villanueva, Marcelle"
Agents(227) = "Villanueva, Vigie"
Agents(228) = "Villaver, Conrado"
Agents(229) = "Virtudazo, Joeurbett"
Agents(230) = "Viscante, Emelson"
Agents(231) = "Yap, Paul"
Agents(232) = "Ylagan, Celin"
Agents(233) = "Ylidon, Andrew"
Agents(234) = "Yrrverre, Neil Vince"




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

For I = 1 To 234
    agentName = Agents(I)

       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
           DoEvents
           Application.Wait (Now + TimeValue("0:00:01"))
           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"
                   DoEvents
                   Application.Wait (Now + TimeValue("0:00:01"))
                   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
               DoEvents
               Application.Wait (Now + TimeValue("0:00:01"))
               Else
               b = cvsSrv.Reports.CreateReport(Info, Rep)
                   If b Then
                   Debug.Print Rep.SetProperty("Agent", Agents(I))      ' agentName)
                   Debug.Print Rep.SetProperty("Dates", mydate)
                   Debug.Print Rep.SetProperty("Times", "00:00-23:59")
                   b = Rep.ExportData("", 9, 0, False, True, True)
                   DoEvents
                   Application.Wait (Now + TimeValue("0:00:01"))
                   wk.Sheets(I).Select
                   wk.Sheets(I).Cells.ClearContents
                   wk.Sheets(I).Cells(1, 1).PasteSpecial
                   DoEvents
                   Application.Wait (Now + TimeValue("0:00:01"))

                       If Not cvsSrv.Interactive Then cvsSrv.ActiveTasks.Remove Rep.TaskID
                       Set Rep = Nothing
                       Set Log = Nothing
                       Set Rep = Nothing
                       Set cvsSrv = Nothing
                       Set cvsConn = Nothing
                       Set cvsApp = Nothing
                       Set Info = Nothing
                       DoEvents
                       Application.Wait (Now + TimeValue("0:00:01"))
                       End If
                   End If
               End If
       End If
    ActiveWorkbook.Save
    DoEvents
    Application.Wait (Now + TimeValue("0:00:01"))
Next I

cvsConn.logout
cvsConn.Disconnect
cvsSrv.Connected = False
DoEvents

MsgBox "Completed"

End Sub
AlTheGreat
  • 19
  • 3
  • 63 is a suspicious number (64-1) like you're running up against some limit. Or, there is something wrong with your 63-rd agent. Have you tried rearranging the agent array to see if it stays at 63 or follows that agent? – n8wrl Dec 08 '15 at 20:38
  • 2
    I also notice you are creating the COM objects every time thru your loop but you don't release them. Can you reuse them? If so, move the "SET" statements outside of your loop. If not, destroy them at the bottom of the loop. – n8wrl Dec 08 '15 at 20:40
  • One more thought (sorry for stream of consciousness comments). Pull the code inside each loop out into a method to call that performs your logic. It is very confusing and impossible to maintain for you to duplicate that logic inside each seperate loop. Your loop them simply calls the method with whatever args are necessary. Also please use whitespace to organize your code - impossible to see what nested-if's are doing, etc. – n8wrl Dec 08 '15 at 20:43
  • I've reached agent 66 a couple of times, but 63 seems to be where it fails for the majority of the time. I've tried rearranging the agents, even using the same first few agents repeatedly since I know they work, but still get the error. – AlTheGreat Dec 08 '15 at 20:46
  • I was under the impression that the following, which are within the loop, would kill the COMs: If Not cvsSrv.Interactive Then cvsSrv.ActiveTasks.Remove Rep.TaskID Set Rep = Nothing Set Log = Nothing Set Rep = Nothing Set cvsSrv = Nothing Set cvsConn = Nothing Set cvsApp = Nothing Set Info = Nothing DoEvents – AlTheGreat Dec 08 '15 at 20:47
  • Test it. Get rid of the if or instrument it to see if the COM objects really are getting cleaned up. – n8wrl Dec 08 '15 at 21:20
  • sorry about that - cleaned up the code and went back to my original single loop routine. is it easier to read now? – AlTheGreat Dec 08 '15 at 21:27
  • Much! Does it work? :) I am not sure about the purpose of the cvsSrv.Interactive and the set ... = nothing in that IF. – n8wrl Dec 09 '15 at 16:02

0 Answers0