Welcome to the fracta.net forum!

Share your coding ideas or ask questions.

Question Excel VBA macro to automate Avaya CMS Supervisor reports

More
9 years 11 months ago #343 by roller
Ahh Dim Rep As New Object should be Dim Rep As Object maybe!!
The topic has been locked.
  • hcyeap
  • Visitor
  • Visitor
9 years 11 months ago - 9 years 11 months ago #344 by hcyeap
Hi roller,

I changed Dim Rep As New Object to Dim Rep As Object and it's working now!

Thank you for your assistance!
Very much appreciated!

Regards,
HC
Last edit: 9 years 11 months ago by hcyeap.
The topic has been locked.
  • hcyeap
  • Visitor
  • Visitor
9 years 11 months ago #345 by hcyeap
Hi Roller, need help from you again.
Is there a possible way to make the date range dynamic? Say I have named ranges for start and end date, and the codes will refer to this range.

I tried declaring the ranges as string and dates, but they don't work.
Appreciate if you can assist me in this matter.

Thank you!

Below is the code that i tried which is not working:

Public Sub CMSConn()

Dim StartDate As Date
Dim EndDate As Date
Dim DateRange As String
StartDate = Range("StartDate")
EndDate = Range("EndDate")
DateRange = StartDate & "-" & EndDate

ThisWorkbook.Sheets(1).Cells.ClearContents

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

yourUserName = "hcyeap" '''' put yours in between the " "
yourPassword = "12345678"
SERVERNAME = "aaa-bbb-ccc" ''' change it to your IP I omited for security

If cvsApp.CreateServer(yourUserName, yourPassword, "", SERVERNAME, False, "ENU", cvsSrv, cvsConn) Then
If cvsConn.login(yourUserName, yourPassword, SERVERNAME, "ENU") Then
On Error Resume Next
cvsSrv.Reports.ACD = 1 '/ Changed to 1
Set Info = cvsSrv.Reports.Reports("Historical\Designer\CCE METRICS 2010 (New)")
If Info Is Nothing Then
MsgBox "It didn't work"
Else
On Error Resume Next
b = cvsSrv.Reports.CreateReport(Info, Rep)
If b Then
'Debug.Print Rep.TimeZone = "" ' you may or may need this
Debug.Print Rep.SetProperty("Splits/Skills", "22;33;46")
Debug.Print Rep.SetProperty("Dates", "DateRange")
Debug.Print Rep.SetProperty("ACD", "1") 'Added
b = Rep.ExportData("", 9, 0, False, True, True)
ThisWorkbook.Sheets(1).Cells(1, 1).PasteSpecial
Rep.Quit
End If
End If
End If
End If
Set Info = Nothing
cvsConn.logout
cvsConn.Disconnect
cvsSrv.Connected = False
Set Rep = Nothing
Set cvsSrv = Nothing
Set cvsConn = Nothing
Set cvsApp = Nothing

End Sub
The topic has been locked.
More
9 years 11 months ago #346 by roller
make sure the range is one cell and try Range("StartDate").text or simply cells(3,3).text of whatever the cell is. and below in the code it would be like this Debug.Print Rep.SetProperty("Dates", DateRange) not Debug.Print Rep.SetProperty("Dates", "DateRange")
The topic has been locked.
  • hcyeap
  • Visitor
  • Visitor
9 years 11 months ago #347 by hcyeap
Hi Roller,

It's working!

Thank you very much!
YOu're the best! :lol:
The topic has been locked.
  • hcyeap
  • Visitor
  • Visitor
9 years 11 months ago - 9 years 11 months ago #348 by hcyeap
Hi Roller,

I need your assistance for the below code....

I have to generate the same report for 11 different skill sets. I tried by putting all the codes together by changing the skills sets. It worked but the codes are very long and slow.

I tried something like this... which is not working properly

Sub UpdateSkill()
Dim strTotal As String
Dim strEngSPK As String
Dim strChiSPK As String
Dim strSP As String
Dim strANZ As String
Dim strUK As String
Dim strUS As String
Dim strIND As String

Dim lnArray As Long
Dim varSkills As Variant
Dim varSheets As Variant

strTotal = Range("Total")
strEngSPK = Range("EngSPK")
strChiSPK = Range("ChiSPK")
strSP = Range("SP")
strANZ = Range("ANZ")
strUK = Range("UK")
strUS = Range("US")
strIND = Range("IND")

varSheets = Array("Total", "Eng SPK", "Chi SPK", "SP", "ANZ", "UK", "US", "IND")
varSkills = Array(strTotal, strEngSPK, strChiSPK, strSP, strANZ, strUK, strUS, strIND)

dtStart = Range("StartDate")
dtEnd = Range("EndDate")
strDateRange = dtStart & "-" & dtEnd

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

'/ Skills
If cvsApp.CreateServer(user, password, "", server, False, "ENU", cvsSrv, cvsConn) Then
If cvsConn.login(user, password, server, "ENU") Then
On Error Resume Next
cvsSrv.Reports.ACD = 1
Set Info = cvsSrv.Reports.Reports("Historical\Designer\CCE METRICS 2010 (New)")
If Info Is Nothing Then
MsgBox "It didn't work"
Else
On Error Resume Next
b = cvsSrv.Reports.CreateReport(Info, Rep)
If b Then
For lnArray = LBound(varSkills) To UBound(varSkills)
'Debug.Print Rep.TimeZone = "" ' you may or may need this
Debug.Print Rep.SetProperty("Splits/Skills", varSkills(lnArray))
Debug.Print Rep.SetProperty("Dates", strDateRange)
Debug.Print Rep.SetProperty("ACD", "1")
b = Rep.ExportData("", 9, 0, False, True, True)
Sheets(varSheets(lnArray)).Cells(1, 1).PasteSpecial
Rep.Quit
Next lnArray
End If
End If
End If
End If

If Not cvsSrv.Interactive Then
cvsSrv.ActiveTasks.Remove Rep.TaskID
cvsApp.Servers.Remove cvsSrv.ServerKey
End If

cvsConn.Logout
cvsConn.Disconnect
cvsApp.Disconnect
cvsSrv.Connected = False

Set Info = Nothing
Set Rep = Nothing
Set cvsSrv = Nothing
Set cvsConn = Nothing
Set cvsApp = Nothing
End Sub


It just generated the same data and paste them throughout the sheets.
Do you have any idea to make them work?

Thank you!
Last edit: 9 years 11 months ago by hcyeap.
The topic has been locked.
Time to create page: 0.877 seconds
Powered by Kunena Forum