Sub SetupPerfSummary()
'
' SetupPerfSummary Macro
'
' Keyboard Shortcut: Ctrl+s
RemoveNmonFiles
Application.ScreenUpdating = False
'Use the Status Bar to inform user of the macro's progress
'change the cursor to hourglass
Application.Cursor = xlWait
' makes sure that the statusbar is visible
Application.DisplayStatusBar = True
'add your message to status bar
Application.StatusBar = "Creating PerfSummary Tab..."
Dim SummaryFile As String
SummaryFile = SaveSummary
Application.DisplayAlerts = False
If Application.Version = "12.0" Then
ActiveWorkbook.SaveAs FileName:=SummaryFile, FileFormat:=xlExcel8
Else
ActiveWorkbook.SaveAs FileName:=SummaryFile
End If
Application.DisplayAlerts = True
Sheets.Add.Name = "Perf_Summary"
Application.StatusBar = "Generating PerfSummary Header..."
Range("A1:D1").Select
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = True
End With
Range("E1").Value = "CPU"
Range("K1").Value = "Memory"
Range("B2").Value = "Allocation"
Range("E2").Value = "PhysicalCPU"
Range("G2").Value = "%Virtual CPU"
Range("I2").Value = "RunQueue"
Range("K2").Value = "pgsin"
Range("M2").Value = "pgsout"
Range("O2").Value = "Comp. Memory"
' line 3
Range("A3").Value = "Server"
Range("B3").Value = "EC"
Range("C3").Value = "VP"
Range("D3").Value = "Mem"
Range("E3,G3,I3,K3,M3").Value = "Avg"
Range("F3,H3,J3,L3,N3").Value = "Max"
Range("O3").Value = "Avg"
Range("P3").Value = "Max"
Range("A1:P3").Select
Range("A1:P1").Font.Bold = True
Range("A2:P2").Font.Bold = True
Range("A3:P3").Font.Bold = True
Selection.Borders(<wbr>xlDiagonalDown).LineStyle = xlNone
Selection.Borders(<wbr>xlDiagonalUp).LineStyle = xlNone
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.ColorIndex = 0
.Weight = xlMedium
End With
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.ColorIndex = 0
.Weight = xlMedium
End With
With Selection.Borders(<wbr>xlEdgeBottom)
.LineStyle = xlContinuous
.ColorIndex = 0
.Weight = xlMedium
End With
With Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.ColorIndex = 0
.Weight = xlMedium
End With
With Selection.Borders(<wbr>xlInsideVertical)
.LineStyle = xlContinuous
.ColorIndex = 0
.Weight = xlThin
End With
With Selection.Borders(<wbr>xlInsideHorizontal)
.LineStyle = xlContinuous
.ColorIndex = 0
.Weight = xlThin
End With
Range("E1:J1").Select
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = True
End With
Range("K1:P1").Select
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = True
End With
Range("B2:D2").Select
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = True
End With
Range("E2:F2").Select
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = True
End With
Range("G2:H2").Select
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = True
End With
Range("I2:J2").Select
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = True
End With
Range("K2:L2").Select
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = True
End With
Range("M2:N2").Select
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = True
End With
Range("O2:P2").Select
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = True
End With
Range("B3:P3").Select
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
End With
Sheets("Sheet1").Select
Range("A2").Select
ReorderSummary
''''''''''''''''''
' macro_LOOP
''''''''''''''''''
Dim message As String
Dim imOutput As String
Dim recipients As Variant
Dim FullFilePath As String
Dim FullPath As String
Dim FName As String
Dim SFName As String
Dim ans As Integer
ans = 2
Range("D2").Select
Do
Range("D" & ans).Select
FullFilePath = Range("D" & ans).Value
FName = Mid(FullFilePath, InStrRev(FullFilePath, "\") + 1, 999)
Application.StatusBar = "Processing " & FName & "..."
Workbooks.Open (FullFilePath)
Workbooks(FName).Activate
Application.Run "'macro_for_nmon.XLS'!nmon_<wbr>postwork"
Workbooks(FName).Close SaveChanges:=True
ans = ans + 1
'ActiveCell.Offset(1, 0).Select
Loop Until IsEmpty(ActiveCell.Offset(1, 0))
SummaryFile = SaveSummary
Application.StatusBar = "Saving " & SummaryFile & "..."
Application.Cursor = xlNormal
' makes sure that the statusbar is visible
Application.DisplayStatusBar = False
SFName = Mid(FullFilePath, InStrRev(FullFilePath, "\") + 1, 999)
Application.DisplayAlerts = False
If Application.Version = "12.0" Then
ActiveWorkbook.SaveAs FileName:=SummaryFile, FileFormat:=xlExcel8
Else
ActiveWorkbook.SaveAs FileName:=SummaryFile
End If
ActiveWorkbook.Close
message = "A New NMON Report is ready. The Summary file is at " & vbCrLf & SummaryFile
'
' Set IM recipients (TTM UNIX members)
'
'recipients = Array ("anemailaddress.com")
imOutput = sendIM(message, recipients)
End Sub
