Help regarding CMS & Excel VBA

[jei eusebio]

Hi Ninjas,

I'm currently using the code below to get data from CMS .. but I still have to login to be able to run the macro .. not sure if its possible but here are my questions ..

I. Is there any way to incorporate my username and password to the code below so that It would automatically just log me in and get the data once I try to run the macro?
II. There are 2 screens that would pop up, one is asking for the Agent Code and the other one for the Date, then the CMS pop up for the agent code & date will pop up .. which seems kind of redundant .. You think it would be better to just remove the first two pop ups or if someone can help me connect it to the main pop up of CMS ..

Thanks in advance Ninjas!

-Jei

Sub GetData()
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 CMSRunning As String
Dim objWMIcimv2 As Object
Dim objProcess As Object
Dim objList As Object
CMSRunning = "acsSRV.exe"
Set objWMIcimv2 = GetObject("winmgmts:" _
& "{impersonationLevel=impersonate}!\\.\root\cimv2") 'Connect to CIMV2 Namespace
Set objList = objWMIcimv2.ExecQuery _
("select * from win32_process where name='" & CMSRunning & "'") 'determine if CMS is running
If objList.Count = 0 Then 'If 0 then process isn't running
GoTo e:
End If
Set cvsApp = CreateObject("ACSUP.cvsApplication")
Set cvsConn = CreateObject("ACSCN.cvsConnection")
Set cvsSrv = CreateObject("ACSUPSRV.cvsServer")
Set Rep = CreateObject("ACSREP.cvsReport")
Cells.Select
Selection.ClearContents
Range("A1").Select
On Error GoTo e:
Application.ScreenUpdating = 0
Set cvsSrv = cvsApp.Servers(1)
Application.ScreenUpdating = 1
AgGrp = InputBox("Enter Agent Group Name", "Agent Group") 'change as needed for variables to enter into report
RpDate = InputBox("Enter Date", "Date") 'change as needed for variables to enter into report

'Start code from CMS Export script
On Error Resume Next
cvsSrv.Reports.ACD = 1
Set Info = cvsSrv.Reports.Reports("Historical\Designer\Intraday Split/Skill Summary v3")
b = cvsSrv.Reports.CreateReport(Info, Rep)
If b Then
Rep.Window.Top = 1830
Rep.Window.Left = 975
Rep.Window.Width = 17610
Rep.Window.Height = 11910
Rep.SetProperty "Agent Group", AgGrp 'change as needed for report variables
Rep.SetProperty "Date", RpDate 'change as needed for report variables
b = Rep.ExportData("\\mia1fsc3fldr01\folders$\jeusebio\Desktop\CallOut Tracker\CMSRaw2xlsx", 44, 1, True, True, True)
Rep.Quit
If Not cvsSrv.Interactive Then cvsSrv.ActiveTasks.Remove Rep.TaskID
Set Rep = Nothing
End If
Set Info = Nothing
With ActiveSheet.QueryTables.Add(Connection:= _
"TEXT;\\mia1fsc3fldr01\folders$\jeusebio\Desktop\CallOut Tracker\CMSRaw.xlsx", _
Destination:=Range("$A$1"))
.Name = "Agent Group"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.TextFilePromptOnRefresh = False
.TextFilePlatform = 437
.TextFileStartRow = 1
.TextFileParseType = xlDelimited
.TextFileTextQualifier = xlTextQualifierDoubleQuote
.TextFileConsecutiveDelimiter = False
.TextFileTabDelimiter = False
.TextFileSemicolonDelimiter = False
.TextFileCommaDelimiter = True
.TextFileSpaceDelimiter = False
.TextFileColumnDataTypes = Array(1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1)
.TextFileTrailingMinusNumbers = True
.Refresh BackgroundQuery:=False
End With
' End code from CMS Export Script

Kill "\\mia1fsc3fldr01\folders$\jeusebio\Desktop\CallOut Tracker\CMSRaw.xlsx"
cvsConn.logout
cvsConn.Disconnect
cvsSrv.Connected = False
Set Log = Nothing
Set Rep = Nothing
Set cvsSrv = Nothing
Set cvsConn = Nothing
Set cvsApp = Nothing
Set Info = Nothing

e:
If Err.Number <> 91 Then
MsgBox "You must be logged into CMS Supervisor before running this macro." & vbCrLf & vbCrLf & "Please log into CMS Supervisor and try again."
End If
End Sub

 

[Hui]

I know at least 3 different systems that use CMS as an acronym
But I don't think that matters for this exercise

What about simply using the Onkey function in VBA
That sends a predefined set of keystrokes to the system
https://msdn.microsoft.com/en-us/library/office/ff197461.aspx

If it is a web based system there are other alternatives to complete user fields

 

[jei eusebio]

Thanks for the reply Hui .. not really sure how to do that but I'll review the link you posted and see what I can do ..