- Thank you received: 9
Welcome to the fracta.net forum!
Share your coding ideas or ask questions.
Share your coding ideas or ask questions.
Question Excel VBA macro to automate Avaya CMS Supervisor reports
9 years 11 months ago #343
by roller
Replied by roller on topic Excel VBA macro to automate Avaya CMS Supervisor reports
Ahh Dim Rep As New Object should be Dim Rep As Object maybe!!
The topic has been locked.
- hcyeap
- Visitor
9 years 11 months ago - 9 years 11 months ago #344
by hcyeap
Replied by hcyeap on topic Excel VBA macro to automate Avaya CMS Supervisor reports
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
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
9 years 11 months ago #345
by hcyeap
Replied by hcyeap on topic Excel VBA macro to automate Avaya CMS Supervisor reports
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
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.
9 years 11 months ago #346
by roller
Replied by roller on topic Excel VBA macro to automate Avaya CMS Supervisor reports
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
9 years 11 months ago #347
by hcyeap
Replied by hcyeap on topic Excel VBA macro to automate Avaya CMS Supervisor reports
Hi Roller,
It's working!
Thank you very much!
YOu're the best! :lol:
It's working!
Thank you very much!
YOu're the best! :lol:
The topic has been locked.
- hcyeap
- Visitor
9 years 11 months ago - 9 years 11 months ago #348
by hcyeap
Replied by hcyeap on topic Excel VBA macro to automate Avaya CMS Supervisor reports
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!
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