Help with CMS Script(ver 13) using VBA

[itsyash]

Hi All,

When I try to use the code below I get an error message (at the login
stage) saying Microsoft excel is waiting for another application to
finish the OLE function. Why could this be happening?

Please help me if anyone knows this.

Thanks,
Yash

Sub CMSGetReportTrav()


Dim DatesAs String
Dim Splits As String
Dim RepName As String

RepName = "Historical\CMS custom\Copy of Agent Staffing Rpt"

Splits = "51"

Dates = Date - 7

Dim sServerIP As String
Dim iACD As Integer
Dim sReportName As String
Dim sProperty1Name As String
Dim sProperty1Value As String
Dim sProperty2Name As String
Dim sProperty2Value As String

sServerIP = "xxx.xx.xx.xxx"
iACD = 1
sReportName = RepName(Rcounter)

sProperty1Name = "Date(s)"
sProperty1Value = Dates(Counter)
sProperty2Name = "Split Number(s)"
sProperty2Value = Splits(Scounter)

Const Username As String = "****"
Const Password As String = "****"

Dim cvsApp As ACSUP.cvsApplication
Dim cvsConn As ACSCN.cvsConnection
Dim cvsSrv As ACSUPSRV.cvsServer
Dim Rep As ACSREP.cvsReport

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

On Error Resume Next

If cvsApp.CreateServer(Username, "", "", sServerIP, False, "ENU",
cvsSrv, cvsConn) Then

'****************************************After this step I get the
error message
If cvsConn.Login(Username, Password, sServerIP, "ENU") Then
'****************************************

' CMSGetReport = False

On Error Resume Next
cvsSrv.Reports.ACD = iACD
Set Info = cvsSrv.Reports.Reports(sReportName)
If Info Is Nothing Then
If cvsSrv.Interactive Then
MsgBox "The Report " & sReportName & " was not found on
ACD" & iACD & ".", vbCritical Or vbOKOnly, "CentreVu Supervisor"
Else
Set Log = CreateObject("CVSERR.cvslog")
Log.AutoLogWrite "The Report " & sReportName & " was not
found on ACD" & iACD & "."
Set Log = Nothing
End If
Else

b = cvsSrv.Reports.CreateReport(Info, Rep)
If b Then
Debug.Print Rep.SetProperty(sProperty1Name,
sProperty1Value)
Debug.Print Rep.SetProperty(sProperty2Name,
sProperty2Value)



b = Rep.ExportData("", 9, 0, True, True, False)

Range("Ah1").Select
ActiveSheet.Paste
Range("Ah10:au1000").Select
Selection.Clear
Range("Ah1:au9").Copy
Range("A" & PasteLocation).Select
ActiveSheet.Paste
PasteLocation = PasteLocation + 15
Counter = Counter + 1

If sReportName = "Historical\CMS custom\Custom Daily
Stats" Then

sProperty1Name = "Date(s)"
sProperty1Value = Dates(Counter)
sProperty2Name = "Split Number(s)"
sProperty2Value = Splits(Scounter)

Else

sProperty1Name = "Split(s)"
sProperty1Value = Splits(Scounter)
sProperty2Name = "Date(s)"
sProperty2Value = Dates(Counter)

End If




Rep.Quit
' CMSGetReport = True
If Not cvsSrv.Interactive Then cvsSrv.ActiveTasks.Remove
Rep.TaskID
Set Rep = Nothing
End If

End If
Wend

Range("Ah1:au25").Select
Selection.ClearContents
Selection.NumberFormat = "General"

Rcounter = Rcounter + 1
sReportName = RepName(Rcounter)

Counter = 0

If sReportName = "Historical\CMS custom\Custom Daily
Stats" Then

sProperty1Name = "Date(s)"
sProperty1Value = Dates(Counter)
sProperty2Name = "Split Number(s)"
sProperty2Value = Splits(Scounter)

Else

sProperty1Name = "Split(s)"
sProperty1Value = Splits(Scounter)
sProperty2Name = "Date(s)"
sProperty2Value = Dates(Counter)

End If


Wend





Counter = 0
Rcounter = 0


sReportName = RepName(Rcounter)
Scounter = Scounter + 1

If sReportName = "Historical\CMS custom\Custom Daily
Stats" Then

sProperty1Name = "Date(s)"
sProperty1Value = Dates(Counter)
sProperty2Name = "Split Number(s)"
sProperty2Value = Splits(Scounter)

Else

sProperty1Name = "Split(s)"
sProperty1Value = Splits(Scounter)
sProperty2Name = "Date(s)"
sProperty2Value = Dates(Counter)

End If


Wend

Set cvsApp = Nothing
Set cvsConn = Nothing
Set cvsSrv = Nothing
Set b = Nothing
Set Log = Nothing

End If
End If

Range("AB2").Select


End Sub

 

[NickHK]

Not familiar with this CMS Script etc, but remove the "On Error Resume Next"
and see what the real cause/error is .

NickHK

Hi All,

When I try to use the code below I get an error message (at the login
stage) saying Microsoft excel is waiting for another application to
finish the OLE function. Why could this be happening?

Please help me if anyone knows this.

Thanks,
Yash

Sub CMSGetReportTrav()

----- CUT --------------------

Dim cvsApp As ACSUP.cvsApplication
Dim cvsConn As ACSCN.cvsConnection
Dim cvsSrv As ACSUPSRV.cvsServer
Dim Rep As ACSREP.cvsReport

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

On Error Resume Next

If cvsApp.CreateServer(Username, "", "", sServerIP, False, "ENU",
cvsSrv, cvsConn) Then

'****************************************After this step I get the
error message
If cvsConn.Login(Username, Password, sServerIP, "ENU") Then
'****************************************

----- CUT --------------------

 

[itsyash]

Hi Nick. The OLE error is the one that I get when i remove "On Error
Resume Next"

Is there a reason why this happens?

Thanks,
Yash

 

[NickHK]

As I don't know this app, I'm guessing that you have to Login before you can
use the object ?
You should check the documentation for this app to make sure you are using
it correctly.

'This creates a Server Object ??
Set cvsSrv = CreateObject("ACSUPSRV.cvsServer")
'What does do then
If cvsApp.CreateServer(Username, "", "", sServerIP, False, "ENU",cvsSrv,
cvsConn) Then

Also, this codes it differently :
http://www.avayausers.com/showthread.php?s=2b42362cd6c42c11ea939651269a3ea1&threadid=3020

NickHK