Question codes

CMS AVAYA MACRO

Option Explicit

Function CMSConn(sUserID As String, sPassword As String, sServerIP As String)

Dim cvsApp As cvsApplication
Dim cvsSrv As cvsServer
Dim cvsConn As cvsConnection
Dim iServer As Integer
Dim bConnected As Boolean

bConnected = False
Set cvsApp = New cvsApplication
Set cvsSrv = New cvsServer
Set cvsConn = New cvsConnection

For iServer = 1 To cvsApp.Servers.Count
Set cvsSrv = cvsApp.Servers(iServer)
If cvsSrv.ServerKey Like "*\" & sServerIP & "\*\*\*" Then
bConnected = True
Exit For
End If
Next iServer


If bConnected = False Then
If cvsApp.CreateServer(sUserID, sPassword, "", sServerIP, False, "ENU", cvsSrv, cvsConn) Then
If cvsConn.Login(sUserID, sPassword, sServerIP, "ENU") Then
End If
End If
End If


Dim cvsRepInfo As Object
Dim cvsRepProp As Object
Dim cvsLog As Object
Dim b As Boolean



cvsSrv.Reports.ACD = 1
Set cvsRepInfo = cvsSrv.Reports.Reports("Historical\Designer\TUI Group AUX Daily")

If cvsRepInfo Is Nothing Then
If cvsSrv.Interactive Then
MsgBox "The report was not found on ACD 1.", vbCritical Or vbOKOnly, "Avaya CMS Supervisor"
Else
Set cvsLog = CreateObject("ACSERR.cvsLog")
cvsLog.AutoLogWrite "The report was not found on ACD 1."
Set cvsLog = Nothing
End If
Else
b = cvsSrv.Reports.CreateReport(cvsRepInfo, cvsRepProp)
If b Then
cvsRepProp.Window.Top = 40
cvsRepProp.Window.Left = 40
cvsRepProp.Window.Width = 40
cvsRepProp.Window.Height = 40

cvsRepProp.SetProperty "Agent Group", Sheets("MTD Per Team - Agent").Range("L5").Value
cvsRepProp.SetProperty "Date", Sheets("MTD Per Team - Agent").Range("L6").Value



b = cvsRepProp.ExportData("", 9, 0, True, True, True)




If bConnected = True Then
cvsRepProp.Quit
Else
If Not cvsSrv.Interactive Then cvsSrv.ActiveTasks.Remove cvsRepProp.TaskID
End If

Set cvsRepProp = Nothing
End If
End If


Set cvsRepInfo = Nothing
If Not cvsSrv.Interactive Then cvsApp.Servers.Remove cvsSrv.ServerKey

If bConnected = False Then
cvsConn.Logout
cvsConn.Disconnect
cvsSrv.Connected = False
End If

Set cvsConn = Nothing
Set cvsSrv = Nothing
Set cvsApp = Nothing

End Function


Sub TUI()

Call CMSConn("u163621", "wns@1234", "10.64.252.31")

Dim WS As Worksheet
Set WS = ThisWorkbook.Sheets("Do Not Delete")

Sheets("Do Not Delete").Select
WS.Range("D1").Select
WS.Paste

Range("A1").Select
Sheets("MTD Per Team - Agent").Select
Range("A1").Select
End Sub

=================================================================


Copy and off set Dump

Sub FIX_DUMP2()
'
' FIX_DUMP2 Macro
'

'
Sheets("Do Not Delete").Select
Range("D5").Select
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlDown)).Select
ActiveWorkbook.Worksheets("Do Not Delete").SORT.SortFields.Clear
ActiveWorkbook.Worksheets("Do Not Delete").SORT.SortFields.Add Key:=Range( _
"D5:D500"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
xlSortNormal
With ActiveWorkbook.Worksheets("Do Not Delete").SORT
.SetRange Range("D5:R500")
.Header = xlGuess
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
Selection.Copy
Sheets("Dump").Select
Range("K65536").End(xlUp).Offset(1, 0).Select
ActiveSheet.Paste
Sheets("MTD Per Team - Agent").Select
Range("A1").Select
Range("A1").Select
Sheets("MTD Per Team - Agent").Select
Range("A1:B1").Select
End Sub


================================================================


Add Date


Sub ADD_DATE()
'
' ADD_DATE Macro
'

'
Sheets("Do Not Delete").Select
Range("D65536").Select


Selection.End(xlUp).Offset(0, -1).Select

Range(Selection, Selection.End(xlToLeft)).Select
Range(Selection, Selection.End(xlUp)).Select
Selection.Copy

Sheets("Dump").Select
Range("K65536").End(xlUp).Offset(0, 0).Select
Range(Selection, Selection.End(xlToLeft)).Offset(0, -1).Select
Range(Selection, Selection.End(xlToLeft)).Select
Range(Selection, Selection.End(xlUp)).Offset(1, 0).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False


End Sub

=================================================================


Hide Sheet


Sub RunReport()

Sheets("MTD Per Team - Agent").Select
Sheets("Do Not Delete").Visible = True
Call TUI
Call FIX_DUMP2
Call ADD_DATE
Call Delete
Call GroupSummary
Call FIX_DUMP3
Call Delete


Sheets("Do Not Delete").Select
ActiveWindow.SelectedSheets.Visible = False

Call Refresh

End Sub


================================================================



Hide Cells Upon Dump

Sub HIDE()



If Worksheets("FTE Commit & Delivery").Range("C5").Value = Empty Then
Range("C5").Select
Selection.EntireRow.Hidden = True
Else
Range("C5").Select
Selection.EntireRow.Hidden = False
End If
If Worksheets("FTE Commit & Delivery").Range("C6").Value = Empty Then
Range("C6").Select
Selection.EntireRow.Hidden = True
Else
Range("C6").Select
Selection.EntireRow.Hidden = False
End If
If Worksheets("FTE Commit & Delivery").Range("C7").Value = Empty Then
Range("C7").Select
Selection.EntireRow.Hidden = True
Else
Range("C7").Select
Selection.EntireRow.Hidden = False
End If
If Worksheets("FTE Commit & Delivery").Range("C8").Value = Empty Then
Range("C8").Select
Selection.EntireRow.Hidden = True
Else
Range("C8").Select
Selection.EntireRow.Hidden = False
End If
If Worksheets("FTE Commit & Delivery").Range("C9").Value = Empty Then
Range("C9").Select
Selection.EntireRow.Hidden = True
Else
Range("C9").Select
Selection.EntireRow.Hidden = False
End If
If Worksheets("FTE Commit & Delivery").Range("C10").Value = Empty Then
Range("C10").Select
Selection.EntireRow.Hidden = True
Else
Range("C10").Select
Selection.EntireRow.Hidden = False
End If
If Worksheets("FTE Commit & Delivery").Range("C11").Value = Empty Then
Range("C11").Select
Selection.EntireRow.Hidden = True
Else
Range("C11").Select
Selection.EntireRow.Hidden = False
End If
If Worksheets("FTE Commit & Delivery").Range("C12").Value = Empty Then
Range("C12").Select
Selection.EntireRow.Hidden = True
Else
Range("C12").Select
Selection.EntireRow.Hidden = False
End If
If Worksheets("FTE Commit & Delivery").Range("C13").Value = Empty Then
Range("C13").Select
Selection.EntireRow.Hidden = True
Else
Range("C13").Select
Selection.EntireRow.Hidden = False
End If
If Worksheets("FTE Commit & Delivery").Range("C14").Value = Empty Then
Range("C14").Select
Selection.EntireRow.Hidden = True
Else
Range("C14").Select
Selection.EntireRow.Hidden = False
End If
If Worksheets("FTE Commit & Delivery").Range("C15").Value = Empty Then
Range("C15").Select
Selection.EntireRow.Hidden = True
Else
Range("C15").Select
Selection.EntireRow.Hidden = False
End If
If Worksheets("FTE Commit & Delivery").Range("C16").Value = Empty Then
Range("C16").Select
Selection.EntireRow.Hidden = True
Else
Range("C16").Select
Selection.EntireRow.Hidden = False
End If
If Worksheets("FTE Commit & Delivery").Range("C17").Value = Empty Then
Range("C17").Select
Selection.EntireRow.Hidden = True
Else
Range("C17").Select
Selection.EntireRow.Hidden = False
End If
If Worksheets("FTE Commit & Delivery").Range("C18").Value = Empty Then
Range("C18").Select
Selection.EntireRow.Hidden = True
Else
Range("C18").Select
Selection.EntireRow.Hidden = False
End If
If Worksheets("FTE Commit & Delivery").Range("C19").Value = Empty Then
Range("C19").Select
Selection.EntireRow.Hidden = True
Else
Range("C19").Select
Selection.EntireRow.Hidden = False
End If
If Worksheets("FTE Commit & Delivery").Range("C20").Value = Empty Then
Range("C20").Select
Selection.EntireRow.Hidden = True
Else
Range("C20").Select
Selection.EntireRow.Hidden = False
End If
If Worksheets("FTE Commit & Delivery").Range("C21").Value = Empty Then
Range("C21").Select
Selection.EntireRow.Hidden = True
Else
Range("C21").Select
Selection.EntireRow.Hidden = False
End If
If Worksheets("FTE Commit & Delivery").Range("C22").Value = Empty Then
Range("C22").Select
Selection.EntireRow.Hidden = True
Else
Range("C22").Select
Selection.EntireRow.Hidden = False
End If
If Worksheets("FTE Commit & Delivery").Range("C23").Value = Empty Then
Range("C23").Select
Selection.EntireRow.Hidden = True
Else
Range("C23").Select
Selection.EntireRow.Hidden = False
End If
If Worksheets("FTE Commit & Delivery").Range("C24").Value = Empty Then
Range("C24").Select
Selection.EntireRow.Hidden = True
Else
Range("C24").Select
Selection.EntireRow.Hidden = False
End If
If Worksheets("FTE Commit & Delivery").Range("C25").Value = Empty Then
Range("C25").Select
Selection.EntireRow.Hidden = True
Else
Range("C25").Select
Selection.EntireRow.Hidden = False
End If
If Worksheets("FTE Commit & Delivery").Range("C26").Value = Empty Then
Range("C26").Select
Selection.EntireRow.Hidden = True
Else
Range("C26").Select
Selection.EntireRow.Hidden = False
End If
If Worksheets("FTE Commit & Delivery").Range("C27").Value = Empty Then
Range("C27").Select
Selection.EntireRow.Hidden = True
Else
Range("C27").Select
Selection.EntireRow.Hidden = False
End If
If Worksheets("FTE Commit & Delivery").Range("C28").Value = Empty Then
Range("C28").Select
Selection.EntireRow.Hidden = True
Else
Range("C28").Select
Selection.EntireRow.Hidden = False
End If
If Worksheets("FTE Commit & Delivery").Range("C29").Value = Empty Then
Range("C29").Select
Selection.EntireRow.Hidden = True
Else
Range("C29").Select
Selection.EntireRow.Hidden = False
End If
If Worksheets("FTE Commit & Delivery").Range("C30").Value = Empty Then
Range("C30").Select
Selection.EntireRow.Hidden = True
Else
Range("C30").Select
Selection.EntireRow.Hidden = False
End If
If Worksheets("FTE Commit & Delivery").Range("C31").Value = Empty Then
Range("C31").Select
Selection.EntireRow.Hidden = True
Else
Range("C31").Select
Selection.EntireRow.Hidden = False
End If
If Worksheets("FTE Commit & Delivery").Range("C32").Value = Empty Then
Range("C32").Select
Selection.EntireRow.Hidden = True
Else
Range("C32").Select
Selection.EntireRow.Hidden = False
End If
If Worksheets("FTE Commit & Delivery").Range("C33").Value = Empty Then
Range("C33").Select
Selection.EntireRow.Hidden = True
Else
Range("C33").Select
Selection.EntireRow.Hidden = False
End If
If Worksheets("FTE Commit & Delivery").Range("C34").Value = Empty Then
Range("C34").Select
Selection.EntireRow.Hidden = True
Else
Range("C34").Select
Selection.EntireRow.Hidden = False
End If
If Worksheets("FTE Commit & Delivery").Range("C35").Value = Empty Then
Range("C35").Select
Selection.EntireRow.Hidden = True
Else
Range("C35").Select
Selection.EntireRow.Hidden = False
End If
If Worksheets("FTE Commit & Delivery").Range("C36").Value = Empty Then
Range("C36").Select
Selection.EntireRow.Hidden = True
Else
Range("C36").Select
Selection.EntireRow.Hidden = False
End If
If Worksheets("FTE Commit & Delivery").Range("C37").Value = Empty Then
Range("C37").Select
Selection.EntireRow.Hidden = True
Else
Range("C37").Select
Selection.EntireRow.Hidden = False
End If
If Worksheets("FTE Commit & Delivery").Range("C38").Value = Empty Then
Range("C38").Select
Selection.EntireRow.Hidden = True
Else
Range("C38").Select
Selection.EntireRow.Hidden = False
End If
If Worksheets("FTE Commit & Delivery").Range("C39").Value = Empty Then
Range("C39").Select
Selection.EntireRow.Hidden = True
Else
Range("C39").Select
Selection.EntireRow.Hidden = False
End If
If Worksheets("FTE Commit & Delivery").Range("C40").Value = Empty Then
Range("C40").Select
Selection.EntireRow.Hidden = True
Else
Range("C40").Select
Selection.EntireRow.Hidden = False
End If
If Worksheets("FTE Commit & Delivery").Range("C41").Value = Empty Then
Range("C41").Select
Selection.EntireRow.Hidden = True
Else
Range("C41").Select
Selection.EntireRow.Hidden = False
End If
If Worksheets("FTE Commit & Delivery").Range("C42").Value = Empty Then
Range("C42").Select
Selection.EntireRow.Hidden = True
Else
Range("C42").Select
Selection.EntireRow.Hidden = False
End If
If Worksheets("FTE Commit & Delivery").Range("C43").Value = Empty Then
Range("C43").Select
Selection.EntireRow.Hidden = True
Else
Range("C43").Select
Selection.EntireRow.Hidden = False
End If
If Worksheets("FTE Commit & Delivery").Range("C44").Value = Empty Then
Range("C44").Select
Selection.EntireRow.Hidden = True
Else
Range("C44").Select
Selection.EntireRow.Hidden = False
End If
If Worksheets("FTE Commit & Delivery").Range("C45").Value = Empty Then
Range("C45").Select
Selection.EntireRow.Hidden = True
Else
Range("C45").Select
Selection.EntireRow.Hidden = False
End If
If Worksheets("FTE Commit & Delivery").Range("C46").Value = Empty Then
Range("C46").Select
Selection.EntireRow.Hidden = True
Else
Range("C46").Select
Selection.EntireRow.Hidden = False
End If
If Worksheets("FTE Commit & Delivery").Range("C47").Value = Empty Then
Range("C47").Select
Selection.EntireRow.Hidden = True
Else
Range("C47").Select
Selection.EntireRow.Hidden = False
End If
If Worksheets("FTE Commit & Delivery").Range("C48").Value = Empty Then
Range("C48").Select
Selection.EntireRow.Hidden = True
Else
Range("C48").Select
Selection.EntireRow.Hidden = False
End If
If Worksheets("FTE Commit & Delivery").Range("C49").Value = Empty Then
Range("C49").Select
Selection.EntireRow.Hidden = True
Else
Range("C49").Select
Selection.EntireRow.Hidden = False
End If
If Worksheets("FTE Commit & Delivery").Range("C50").Value = Empty Then
Range("C50").Select
Selection.EntireRow.Hidden = True
Else
Range("C50").Select
Selection.EntireRow.Hidden = False
End If
If Worksheets("FTE Commit & Delivery").Range("C51").Value = Empty Then
Range("C51").Select
Selection.EntireRow.Hidden = True
Else
Range("C51").Select
Selection.EntireRow.Hidden = False
End If
If Worksheets("FTE Commit & Delivery").Range("C52").Value = Empty Then
Range("C52").Select
Selection.EntireRow.Hidden = True
Else
RaAnge("C52").Select
Selection.EntireRow.Hidden = False
End If


End Sub


=================================================================

Automail


Sub Automail1()
Dim strTo As String
Dim strCC As String
Dim strfilename As String
Dim strpath As String
Dim length As Integer
Dim AWorksheet As Worksheet
Dim Sendrng As Range
Dim rng As Range
Dim strbody As String
Dim strDate As String
Dim strmonth As String
Dim stryear As String
Dim strtime As String
Dim strrevunueper As Double
Dim strabandon As Double
Dim strAHT As Double
Dim strres As Double
Dim stractive As String
Dim strfilezip As String
Dim strruninterval As String
Dim strattachfile As String
Dim strfrom As String
Dim strsub As String
Dim Now As String
Dim Path As String
Dim path1 As String

Application.DisplayAlerts = False
Application.ScreenUpdating = False
ActiveWorkbook.CheckCompatibility = False

Application.CutCopyMode = False
Windows("Template1.xlsx").Activate
Application.CutCopyMode = False
Application.DisplayAlerts = True
Calculate


Sheets("Sheet1").Select
Sheets("Setting").Select
stractive = ActiveWorkbook.name
ActiveSheet.Range("D16").Select
strTo = ActiveCell.Value
ActiveSheet.Range("D17").Select
strCC = ActiveCell.Value
ActiveSheet.Range("D11").Select
strDate = ActiveCell.Value
ActiveSheet.Range("D12").Select
strmonth = ActiveCell.Value
ActiveSheet.Range("D13").Select
stryear = ActiveCell.Value
ActiveSheet.Range("D18").Select
strfilezip = ActiveCell.Value
ActiveSheet.Range("D19").Select
strattachfile = ActiveCell.Value
ActiveSheet.Range("D8").Select
strruninterval = ActiveCell.Value
ActiveSheet.Range("D23").Select
strfrom = ActiveCell.Value
ActiveSheet.Range("D24").Select
Now = ActiveCell.Value
ActiveSheet.Range("D25").Select
strsub = ActiveCell.Value

'

---

If strruninterval <> "00:00 - 02:00" Then
strpath = ActiveSheet.Range("D15").Value
strfilename = "Intraday Report " & strDate & " " & strmonth & ".xls"
Workbooks.Open strpath & strfilename
'Windows(strfilename).Activate
Sheets("Snapshot").Select
ActiveSheet.Range("E12").Select
ActiveCell.Value = ActiveCell.Value
ActiveCell.NumberFormat = "0.00%"
strabandon = Round(ActiveCell.Value * 100, 0)
ActiveSheet.Range("E14").Select
ActiveCell.NumberFormat = "0.00"
strAHT = Round(ActiveCell.Value, 0)
ActiveSheet.Range("F7").Select
ActiveCell.NumberFormat = "0.00"
strres = Round(ActiveCell.Value, 0)
ActiveSheet.Range("F9").Select
ActiveCell.Value = ActiveCell.Value
ActiveCell.NumberFormat = "0.00%"
'strrevunueper = Round(ActiveCell.Value * 100, 2)


'Sheets("Snapshot").Select
ActiveSheet.Range("C6").Select
Selection.CurrentRegion.Select
Selection.Copy
Windows(stractive).Activate
'Sheets("Sheet1").Visible = True
Sheets("Sheet1").Select
ActiveSheet.Range("A6").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Selection.Columns.AutoFit


Windows(strfilename).Close
'Zip_File_Or_Files
Sheets("Snapshot").Select
On Error GoTo StopMacro

With Application
.ScreenUpdating = False
.EnableEvents = False
End With

Set Sendrng = Worksheets("Sheet1").Range("A1:G37")
Set AWorksheet = ActiveSheet

With Sendrng

.Parent.Select
Set rng = ActiveCell
.Select

ActiveWorkbook.EnvelopeVisible = True
Application.DisplayAlerts = False
With .Parent.MailEnvelope
With .Item
'.From = "WFM-AVIS@wns.com"
.To = strTo
.sentonbehalfofname = strfrom
.cc = strCC
.Subject = strsub
.Attachments.Add (strfilezip)
' .Body = "Please Find attached Interval-wise Report"
.Send
'.Display
Application.DisplayAlerts = False
End With
End With
rng.Select
End With
AWorksheet.Select
'

---

Else
Sheets("Snapshot").Select
Range("B1:M14").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Sheet1").Select
Range("B10").Select
Selection.PasteSpecial Paste:=xlPasteValues
'ActiveSheet.Paste
Calculate

Range("A1").Select
Sheets("Setting").Select
ActiveWorkbook.Save
Sheets("Sheet1").Select
'

---

On Error GoTo StopMacro

With Application
.ScreenUpdating = False
.EnableEvents = False
End With

Set Sendrng = Worksheets("Sheet1").Range("A1:P50")
Set AWorksheet = ActiveSheet

With Sendrng

.Parent.Select
Set rng = ActiveCell
.Select

ActiveWorkbook.EnvelopeVisible = True
With .Parent.MailEnvelope
With .Item
Application.DisplayAlerts = False
'.From = strfrom
.sentonbehalfofname = strfrom
.To = strTo
.cc = strCC
.Subject = strsub
'.Attachments.Add (strfilezip)
.Attachments.Add (strfilezip)
'.Body = "Please Find attached Interval-wise Report"
.Send
'.Display
End With
Application.DisplayAlerts = True
End With
rng.Select
End With
AWorksheet.Select

End If
StopMacro:
With Application
.ScreenUpdating = True
.EnableEvents = True
End With
ActiveWorkbook.EnvelopeVisible = False
Windows("Template1.xlsx").Activate
ActiveWorkbook.Save
ActiveWindow.Close
Windows("Auto Interval Report WCT & TPN MNL.xlsm").Activate

Kill "\\wpmfs1\WFM_MNL_Real Time Monitoring\Real Time Automation\WCT TPN\Dump\Manila.xls"

Application.DisplayAlerts = True
Application.ScreenUpdating = True
ActiveWorkbook.CheckCompatibility = True



End Sub



=================================================================


ZIP file


Declare Function OpenProcess Lib "kernel32" _
(ByVal dwDesiredAccess As Long, _
ByVal bInheritHandle As Long, _
ByVal dwProcessId As Long) As Long

Declare Function GetExitCodeProcess Lib "kernel32" _
(ByVal hProcess As Long, _
lpExitCode As Long) As Long

Public Const PROCESS_QUERY_INFORMATION = &H400
Public Const STILL_ACTIVE = &H103


Public Sub ShellAndWait(ByVal PathName As String, Optional WindowState)
Dim hProg As Long
Dim hProcess As Long, ExitCode As Long
'fill in the missing parameter and execute the program
If IsMissing(WindowState) Then WindowState = 1
hProg = Shell(PathName, WindowState)
'hProg is a "process ID under Win32. To get the process handle:
hProcess = OpenProcess(PROCESS_QUERY_INFORMATION, False, hProg)
Do
'populate Exitcode variable
GetExitCodeProcess hProcess, ExitCode
DoEvents
Loop While ExitCode = STILL_ACTIVE
End Sub


Function bIsBookOpen(ByRef szBookName As String) As Boolean
' Rob Bovey
On Error Resume Next
bIsBookOpen = Not (Application.Workbooks(szBookName) Is Nothing)
End Function



'With this example you browse to the folder you want to zip
'The zip file will be saved in: DefPath = Application.DefaultFilePath
'Normal if you have not change it this will be your Documents folder
'You can change this folder to this if you want to use another folder
'DefPath = "C:\Users\Ron\ZipFolder"
'There is no need to change the code before you test it

Sub A_Zip_Folder_And_SubFolders_Browse()
Dim PathZipProgram As String, NameZipFile As String, FolderName As String
Dim ShellStr As String, strDate As String, DefPath As String
Dim Fld As Object

'Path of the Zip program
PathZipProgram = "C:\Program Files (x86)\7-Zip\"
If Right(PathZipProgram, 1) <> "\" Then
PathZipProgram = PathZipProgram & "\"
End If

'Check if this is the path where 7z is installed.
If Dir(PathZipProgram & "7z.exe") = "" Then
MsgBox "Please find your copy of 7z.exe and try again"
Exit Sub
End If

'Create Path and name of the new zip file
'The zip file will be saved in: DefPath = Application.DefaultFilePath
'Normal if you have not change it this will be your Documents folder
'You can change the folder if you want to another folder like this
'DefPath = "C:\Users\Ron\ZipFolder"
DefPath = Application.DefaultFilePath
If Right(DefPath, 1) <> "\" Then
DefPath = DefPath & "\"
End If

'Create date/Time string, also the name of the Zip in this example
strDate = Format(Now, "yyyy-mm-dd h-mm-ss")

'Set NameZipFile to the full path/name of the Zip file
'If you want to add the word "MyZip" before the date/time use
'NameZipFile = DefPath & "MyZip " & strDate & ".zip"
NameZipFile = DefPath & strDate & ".zip"

'Browse to the folder with the files that you want to Zip
Set Fld = CreateObject("Shell.Application").BrowseForFolder(0, "Select folder to Zip", 512)
If Not Fld Is Nothing Then
FolderName = Fld.Self.Path
If Right(FolderName, 1) <> "\" Then
FolderName = FolderName & "\"
End If

'Zip all the files in the folder and subfolders, -r is Include subfolders
ShellStr = PathZipProgram & "7z.exe a -r" _
& " " & Chr(34) & NameZipFile & Chr(34) _
& " " & Chr(34) & FolderName & "*.*" & Chr(34)

'Note: you can replace the ShellStr with one of the example ShellStrings
'below to test one of the examples


'Zip the txt files in the folder and subfolders, use "*.xl*" for all excel files
' ShellStr = PathZipProgram & "7z.exe a -r" _
' & " " & Chr(34) & NameZipFile & Chr(34) _
' & " " & Chr(34) & FolderName & "*.txt" & Chr(34)

'Zip all files in the folder and subfolders with a name that start with Week
' ShellStr = PathZipProgram & "7z.exe a -r" _
' & " " & Chr(34) & NameZipFile & Chr(34) _
' & " " & Chr(34) & FolderName & "Week*.*" & Chr(34)

'Zip every file with the name ron.xlsx in the folder and subfolders
' ShellStr = PathZipProgram & "7z.exe a -r" _
' & " " & Chr(34) & NameZipFile & Chr(34) _
' & " " & Chr(34) & FolderName & "ron.xlsx" & Chr(34)

'Add -ppassword -mhe of you want to add a password to the zip file(only .7z files)
' ShellStr = PathZipProgram & "7z.exe a -r -ppassword -mhe" _
' & " " & Chr(34) & NameZipFile & Chr(34) _
' & " " & Chr(34) & FolderName & "*.*" & Chr(34)

'Add -seml if you want to open a mail with the zip attached
' ShellStr = PathZipProgram & "7z.exe a -r -seml" _
' & " " & Chr(34) & NameZipFile & Chr(34) _
' & " " & Chr(34) & FolderName & "*.*" & Chr(34)

ShellAndWait ShellStr, vbHide

MsgBox "You will find the zip file here: " & NameZipFile
End If
End Sub



'With this example you zip a fixed folder: FolderName = "C:\Users\Ron\Desktop\TestFolder"
'Note this folder must exist, this is the only thing that you must change before you test it
'The zip file will be saved in: DefPath = Application.DefaultFilePath
'Normal if you have not change it this will be your Documents folder
'You can change this folder to this if you want to use another folder
'DefPath = "C:\Users\Ron\ZipFolder"

Sub B_Zip_Fixed_Folder_And_SubFolders()
Dim PathZipProgram As String, NameZipFile As String, FolderName As String
Dim ShellStr As String, strDate As String, DefPath As String

'Path of the Zip program
PathZipProgram = "C:\Program Files (x86)\7-Zip\"
If Right(PathZipProgram, 1) <> "\" Then
PathZipProgram = PathZipProgram & "\"
End If

'Check if this is the path where 7z is installed.
If Dir(PathZipProgram & "7z.exe") = "" Then
MsgBox "Please find your copy of 7z.exe and try again"
Exit Sub
End If

'Create Path and name of the new zip file
'The zip file will be saved in: DefPath = Application.DefaultFilePath
'Normal if you have not change it this will be your Documents folder
'You can change the folder if you want to another folder like this
'DefPath = "C:\Users\Ron\ZipFolder"
DefPath = Application.DefaultFilePath
If Right(DefPath, 1) <> "\" Then
DefPath = DefPath & "\"
End If

'Create date/Time string, also the name of the Zip in this example
strDate = Format(Now, "yyyy-mm-dd h-mm-ss")

'Set NameZipFile to the full path/name of the Zip file
'If you want to add the word "MyZip" before the date/time use
'NameZipFile = DefPath & "MyZip " & strDate & ".zip"
NameZipFile = DefPath & strDate & ".zip"

'Fill in the folder name
FolderName = "C:\Users\Ron\Desktop\TestFolder"
If Right(FolderName, 1) <> "\" Then
FolderName = FolderName & "\"
End If

'Zip all the files in the folder and subfolders, -r is Include subfolders
ShellStr = PathZipProgram & "7z.exe a -r" _
& " " & Chr(34) & NameZipFile & Chr(34) _
& " " & Chr(34) & FolderName & "*.*" & Chr(34)

'Note: you can replace the ShellStr with one of the example ShellStrings
'in the first macro example on this page

ShellAndWait ShellStr, vbHide

MsgBox "You will find the zip file here: " & NameZipFile
End Sub



'With this example you browse to the folder you want and select the files that you want to zip
'Use the Ctrl key to select more then one file or select blocks of files with the shift key pressed.
'With Ctrl a you select all files in the dialog.
'The name of the zip file will be the Date/Time, you can change the NameZipFile string
'If you want to add the word "MyZip" before the date/time use
'NameZipFile = DefPath & "MyZip " & strDate & ".zip"
'The zip file will be saved in: DefPath = Application.DefaultFilePath
'Normal if you have not change it this will be your Documents folder
'You can change this folder to this if you want to use another folder
'DefPath = "C:\Users\Ron\ZipFolder"
'No need to change the code before you test it

Sub C_Zip_File_Or_Files_Browse()
Dim PathZipProgram As String, NameZipFile As String, FolderName As String
Dim ShellStr As String, strDate As String, DefPath As String
Dim NameList As String, sFileNameXls As String
Dim vArr As Variant, FileNameXls As Variant, iCtr As Long

'Path of the Zip program
PathZipProgram = "C:\Program Files (x86)\7-Zip\"
If Right(PathZipProgram, 1) <> "\" Then
PathZipProgram = PathZipProgram & "\"
End If

'Check if this is the path where 7z is installed.
If Dir(PathZipProgram & "7z.exe") = "" Then
MsgBox "Please find your copy of 7z.exe and try again"
Exit Sub
End If

'Create Path and name of the new zip file
'The zip file will be saved in: DefPath = Application.DefaultFilePath
'Normal if you have not change it this will be your Documents folder
'You can change the folder if you want to another folder like this
'DefPath = "C:\Users\Ron\ZipFolder"
DefPath = Application.DefaultFilePath
If Right(DefPath, 1) <> "\" Then
DefPath = DefPath & "\"
End If

'Create date/Time string, also the name of the Zip in this example
strDate = Format(Now, "yyyy-mm-dd h-mm-ss")

'Set NameZipFile to the full path/name of the Zip file
'If you want to add the word "MyZip" before the date/time use
'NameZipFile = DefPath & "MyZip " & strDate & ".zip"
NameZipFile = DefPath & strDate & ".zip"

FileNameXls = Application.GetOpenFilename(filefilter:="Excel Files, *.xl*", _
MultiSelect:=True, Title:="Select the files that you want to add to the new zip file")

If IsArray(FileNameXls) = False Then
'do nothing
Else
NameList = ""
For iCtr = LBound(FileNameXls) To UBound(FileNameXls)
NameList = NameList & " " & Chr(34) & FileNameXls(iCtr) & Chr(34)
vArr = Split(FileNameXls(iCtr), "\")
sFileNameXls = vArr(UBound(vArr))

If bIsBookOpen(sFileNameXls) Then
MsgBox "You can't zip a file that is open!" & vbLf & _
"Please close: " & FileNameXls(iCtr)
Exit Sub
End If
Next iCtr

'Zip every file you have selected with GetOpenFilename
ShellStr = PathZipProgram & "7z.exe a" _
& " " & Chr(34) & NameZipFile & Chr(34) _
& " " & NameList

ShellAndWait ShellStr, vbHide

MsgBox "You will find the zip file here: " & NameZipFile
End If

End Sub



'With this example you browse to the folder you want and select the files that you want to
'add or update to/in a existing zip file, if the zip file not exist it will be created for you.
'Use the Ctrl key to select more then one file or select blocks of files with the shift key pressed.
'With Ctrl a you select all files in the dialog.
'The zip file will be saved in: DefPath = Application.DefaultFilePath
'Normal if you have not change it this will be your Documents folder
'You can change the folder if you want to another folder like this :
'DefPath = "C:\Users\Ron\ZipFolder"
'Change this code line if you want to change the name of the zip file :
'NameZipFile = DefPath & "ron.zip
'There is no need to change the code before you test it

Sub D_Zip_File_Or_Files_Browse_Add_Update()
'Update older files in the archive and add files that are not in the archive
'Change NameZipFile in the code to your zip file before you run the code
Dim PathZipProgram As String, NameZipFile As String, FolderName As String
Dim ShellStr As String, DefPath As String
Dim NameList As String, sFileNameXls As String
Dim vArr As Variant, FileNameXls As Variant, iCtr As Long

'Path of the Zip program
PathZipProgram = "C:\Program Files (x86)\7-Zip\"
If Right(PathZipProgram, 1) <> "\" Then
PathZipProgram = PathZipProgram & "\"
End If

'Check if this is the path where 7z is installed.
If Dir(PathZipProgram & "7z.exe") = "" Then
MsgBox "Please find your copy of 7z.exe and try again"
Exit Sub
End If

'Create Path and name of the existing/new zip file
'If the zip file not exist the code create it for you
'The zip file will be saved in: DefPath = Application.DefaultFilePath
'Normal if you have not change it this will be your Documents folder
'You can change the folder if you want to another folder like this
'DefPath = "C:\Users\Ron\ZipFolder"
DefPath = Application.DefaultFilePath
If Right(DefPath, 1) <> "\" Then
DefPath = DefPath & "\"
End If
'Set NameZipFile to the full path/name of the Zip file
'Change this code line if you want to change the name of the zip file
NameZipFile = DefPath & "ron.zip"

FileNameXls = Application.GetOpenFilename(filefilter:="Excel Files, *.xl*", _
MultiSelect:=True, Title:="Select the files that you want to update or add to the zip file")

If IsArray(FileNameXls) = False Then
'do nothing
Else
NameList = ""
For iCtr = LBound(FileNameXls) To UBound(FileNameXls)
NameList = NameList & " " & Chr(34) & FileNameXls(iCtr) & Chr(34)
vArr = Split(FileNameXls(iCtr), "\")
sFileNameXls = vArr(UBound(vArr))

If bIsBookOpen(sFileNameXls) Then
MsgBox "You can't zip a file that is open!" & vbLf & _
"Please close: " & FileNameXls(iCtr)
Exit Sub
End If
Next iCtr

'Zip every file you have selected with GetOpenFilename
ShellStr = PathZipProgram & "7z.exe u" _
& " " & Chr(34) & NameZipFile & Chr(34) _
& " " & NameList

ShellAndWait ShellStr, vbHide

MsgBox "You will find the zip file here: " & NameZipFile
End If

End Sub



'With this example you zip the ActiveWorkbook
'The name of the zip file will be the name of the workbook + Date/Time
'The zip file will be saved in: DefPath = Application.DefaultFilePath
'Normal if you have not change it this will be your Documents folder
'You can change this folder to this if you want to use another folder
'DefPath = "C:\Users\Ron\ZipFolder"
'There is no need to change the code before you test it

Sub E_Zip_ActiveWorkbook()

Application.DisplayAlerts = False
Application.ScreenUpdating = False
ActiveWorkbook.CheckCompatibility = False


Dim PathZipProgram As String, NameZipFile As String
Dim ShellStr As String, strDate As String, DefPath As String
Dim FileNameXls As String, TempFilePath As String, TempFileName As String
Dim MyWb As Workbook, FileExtStr As String

'Path of the Zip program
PathZipProgram = "C:\Program Files\7-Zip\"
If Right(PathZipProgram, 1) <> "\" Then
PathZipProgram = PathZipProgram & "\"
End If
'Check if this is the path where 7z is installed.
If Dir(PathZipProgram & "7z.exe") = "" Then
MsgBox "Please find your copy of 7z.exe and try again"
Exit Sub
End If

'Build the path and name for the new xls? file
Set MyWb = ActiveWorkbook
If ActiveWorkbook.Path = "" Then Exit Sub

TempFilePath = Environ$("temp") & "\"
FileExtStr = "." & LCase(Right(MyWb.name, _
Len(MyWb.name) - InStrRev(MyWb.name, ".", , 1)))
TempFileName = Left(MyWb.name, Len(MyWb.name) - Len(FileExtStr))

'Use SaveCopyAs to make a copy of the file
FileNameXls = TempFilePath & TempFileName & FileExtStr
MyWb.SaveCopyAs FileNameXls

'Build the path and name for the new zip file
'The name of the zip file will be the name of the workbook + Date/Time
'The zip file will be saved in: DefPath = Application.DefaultFilePath
'Normal if you have not change it this will be your Documents folder.
'You can change this folder to this if you want to use another folder
'DefPath = "C:\Users\Ron\ZipFolder"
DefPath = "\\wpmfs1\WFM_MNL_Real Time Monitoring\Real Time Automation\WCT TPN\Interval"
If Right(DefPath, 1) <> "\" Then
DefPath = DefPath & "\"
End If
strDate = Format(Now, "yyyy-mm-dd h-mm-ss")
NameZipFile = DefPath & TempFileName & ".Sabre" & ".zip"
'Zip FileNameXls (copy of the ActiveWorkbook)
ShellStr = PathZipProgram & "7z.exe a" _
& " " & Chr(34) & NameZipFile & Chr(34) _
& " " & Chr(34) & FileNameXls & Chr(34)
ShellAndWait ShellStr, vbHide

'Delete the file that you saved with SaveCopyAs and add to the zip file
Kill TempFilePath & TempFileName & FileExtStr
ActiveWorkbook.Save
ActiveWindow.Close

Application.DisplayAlerts = True
Application.ScreenUpdating = True
ActiveWorkbook.CheckCompatibility = True

Call Automail1

End Sub


=================================================================
Kiil server


If you are trying to automate Avaya CMS thru Excel VBA and your script is hanging due to a build up of servers instances in Windows Task manager then this might help. It will probably only happen if the Avaya application is running at the same time as running my script.

The possible solution is to terminate all objects using the below code at the end of your script. I tried it few times and it worked. The server instances will terminate properly at the end of the script.


cvsApp.Servers.Remove cvsSrv.ServerKey
cvsCon.Logout
cvsCon.Disconnect
cvsSrv.Connected = False
Set Log = Nothing
Set Rep = Nothing
Set cvsSrv = Nothing
Set cvsConn = Nothing
Set cvsApp = Nothing

Forecast
FCST
The number of calls forecast for a particular program/split/skill. This is based on client information and historical trends.

Forecast Variance
FCST-VAR
The difference between the actual call volume and the forecasted call volume. Represented by % to forecast
FCST-VAR = NCO / Forecast

Number of Calls Offered
NCO
The number of calls offered to the split/skill (post vectoring). It includes ACD Calls, abandoned Calls, dequeued Calls and outflow Calls.
NCO = NCH + NCA

Number of Calls Handled
NCH
This is the total number of inbound calls which were answered by agents after being offered to the split/skill and includes those calls which were transferred internally using the split/skill transfer number. The same as ACD calls.

Number of Calls Abandoned
NCA
The number of calls that have abandoned (or considered lost) after queing to the split/skill (post vectoring, i.e. after call prmpt, IVR and any QA messages that may be played to the caller).
NCA = NCO –NCH

Abandonment Rate
ABN%
This refers to the percent of calls are offered in the split skill that are actually abandoned within a specific timeframe
ABN% = NCA / NCO

Average Speed of Answer
ASA
This is the average length of time that a call waits once queued to a split/skill before being answered by an agent. It includes the time spent in queue and the time ringing an agent.

Average Talk Time
ATT
This is the average amount of time agents spend talking on calls. It is averaged over ACD calls (calls handled).

Average Time of Hold
ATOH
This is the average amount of time that calls are placed on hold. It is averaged over calls handled (ACD calls).

After Call Work
ACW
This is the amount of time spent in both associated and non-associated ACW. Associated ACW occurs when an agent presses the ACW button while on the phone, non-associated ACW occurs when the agent presses the ACW button while in available or auxillary.


Productivity
Prod
This measures how productive an agent which includes their phone time and all billable Auxes
Prod % = Net Productive Hours/ Actual Productive Hours

Average Handle Time
AHT
This is the amount of time spent on average for each ACD call. It includes Talk Time, After Call Work, both associated and non-associated, and Hold Time. Time spent on either inbound or outbound calls while in ACW mode is included as ACW Time.
AHT = ATT + ATOH + ACW

Service Level
SVL
This relates to what percentage of calls are answered or abandoned within the agreed SL time frame. It is critical to identify the point at which measurement is made, VDN or Split Skill.
SVL = NCH in X seconds / NCO in X seconds
SVL = (NCH in X seconds + NCA in X seconds)/NCO

Occupancy %
OCC%
The amount of time agents are working on call related functions. This includes talk time, after call work time & hold time. It is expressed as a percentage of stafftime less Auxes
OCC% = Total Talk Time + Total Hold Time + Total ACW Time / Total Talk Time + Total Hold Time + Total ACW Time + Total Avail Time


In-Chair Occupancy %
InOCC%
In-chair is calculated as the time that the agent is sitting IN CHAIR divided by TOTAL IN OFFICE WORKED less LUNCH (total of 8 hours). The TOTAL IN OFFICE (Worked) is equivalent to the Total Stafftime (8 hours per agent per day).
InOCC% = Total Talk Time + Total Hold Time + Total ACW Time + Total Avail Time/ Paid Hours (8 hours)

Absenteeism %
ABS%
Percentage of unproductive time (absence heads|hours, whether paid or unpaid) of an agent versus the total scheduled hours of work of the agent.
ABS% = (Total Absent/ Total Scheduled FTE) | includes Late Equivalent to compute Abs with lost Hours

Vacation Leave %
VL%
This is the percentage of the daily staffing that are on vacation leave.
VL% = Total VL / Total FTE

Shrinkage %
Shrinkage
Agent time lost on planned or unplanned factors. This is a major consideration when adjusting FTE requirements over simple call workload.
Shrinkage = Planned Abs% (VL+LOA) + Unplanned Abs% (including Tardy Hours) + Total Aux %



Full Time Equivalent
FTE
A Full Time Equivalent is someone who is working for a total of a specified number hours per week based on the prevailing labor laws per site. A part time employee could be equivalent to less than 1 FTE, depending on the number of working hours in a week. A part time employee working 20 hours per week is equivalent to 0.5 FTE in a case of 40 hours is to 1 FTE definition.

Attrition
Attr
Attrition refers to the FTE lost dues to: positive ATTR =promotions/transfers ,negative ATTR= resignations or terminations.

Attrition %
Attr %
This is referred to as the percentage of FTEs lost due to(positive) promotions/transferred and (negative) resignations and terminations.
Attrition Rate = Negative Attrition / Average of (Beginning and Ending Headcount)

PRODUCTIVITY =(AHT*ACD CALLS)/(STAFFED TIME-(OFF CALL+DATA))
=(AX318*Z318)/(M318-(P318+Q318))

LOG IN EFFICIENCY % = TOTAL STAFF TIME/9:00:00
=BC2/$BV$1

TOTAL STAFF TIME = STAFFTIME/60/24/60
=M2/60/24/60

UNPROD DEFAULT = IF(TOTAL EXTENSION HR<DEFAULT,(DEFAULT-TOTAL EXTENSION HR),0)
=IF(BP2<BE2,(BE2-BP2),0)

PROD DEFAULT = IF( TOTAL EXT HR>DEFAULT,DEFAULT,TOTAL EXT HR)
=IF(BP2>BE2,BE2,BP2)

TOTAL EXT HRS = TOTAL EXT OUT/60/24/60
=AY2/60/24/60

AVAIL TIME =AVAIL TIME/60/24/60
=Y2/60/24/60

QUALITY = Quality/60/24/60
=X5/60/24/60


SWAP/SYSTEM =SWAP/SYSTEM/60/24/60


COFEE BREAK =COFEE BREAK/60/24/60


PERSONAL = PERSONAL/60/24/60


TRAINING = TRAINING/60/24/60


MEETING = MEETING/60/24/60


LUNCH = LUNCH/60/24/60


DATA AUX = DATA AUX/60/24/60


OFF CALL =OFF CALL/60/24/60


DEFAULT = DEFAULT/60/24/60


TOTAL AUX TIME = TOTAL AUX TIME60/24/60

ACW TIME = AVERAGE ACW TIME*ACD CALLS
AB5*Z5

HOLD TIME = AVE HOLD TIME*ACD CALLS
AW5*Z5


TOTAL EXT OUT = AVE EXT OUT TIME*EXTN OUT TIME
=AH5*AG5

TALK TIME = AVG ACD TIME*ACD CALLS
=AA5*Z5

Thanks for sharing, you've been working on this for a while

Try attaching the file now, I had xls, xlsx and xlsm disabled. Usually we placed the files in a Zip folder then attached them but I just changed it.