NMON第2分析工具

最新推荐文章于 2024-09-12 14:20:23 发布

book_gldmz

最新推荐文章于 2024-09-12 14:20:23 发布

阅读量509

点赞数 1

文章标签：工具 file extension integer excel build

本文链接：https://blog.csdn.net/book_gldmz/article/details/4011947

版权

Sub Run_sheets()

Dim FileBackupExt As String
Dim NumSettingRows As Integer
Dim SheetList(100) As String
Dim CPUSheetName As String
Dim PeriodYear As String
Dim PeriodMonth As String
Dim PeriodTime As String
Dim NumSettingRows2 As String
Dim ServerName(20) As String
Dim ServerIndex As Integer
Dim NumFiles As Integer
Dim NumSheets As Integer
Dim NumCPUSheetRows As Integer
Dim FileName As String
Dim FileNamePart() As String
Dim FileNamePartServer As String
Dim FileNamePartYear As String
Dim FileNamePartMonth As String
Dim FileNamePartDay As String
Dim FileNamePartTime As String
Dim CheckCPUFigureFlag As Boolean
Dim CPUBusyLevel As String
Dim CPUSamplingIntval As Integer
Dim CPUBusyDurationMin As Integer
Dim Fname As String
Dim BkFname As String
Dim Fpath As String
Dim sTemp As String
Dim FileList As Variant
Dim DeleteFlag As Boolean
Dim NumDeleteSheet As Integer
Dim DeleteSheetList(100) As String
Dim i, m, n, x As Integer

'=== Definition section ===
PeriodTime = "08"
CPUSheetName = "CPU_ALL"
CPUBusyLevel = ">=80"
CPUSamplingIntval = 5

'=== Define the file added extension ===
FileBackupExt = "org"

'=== Get the setting information ===
Sheets("Setting1").Select
NumSettingRows = ActiveSheet.UsedRange.Rows.Count - 1
Range("A1").Select
For i = 1 To NumSettingRows
SheetList(i) = ActiveCell.Offset(i, 0).Value
Next

Sheets("Setting2").Select
PeriodYear = Range("B1").Value
PeriodMonth = Range("B2").Value
NumSettingRows2 = ActiveSheet.UsedRange.Rows.Count - 4
Range("A4").Select
For i = 1 To NumSettingRows2
ServerName(i) = LCase(ActiveCell.Offset(i, 0).Value)
Next

'=== Build the file list ===
NumFiles = 0
If FileName = "" Or Dir(FileName) = "" Then
FileList = Application.GetOpenFilename("NMON Files(*.xls),*.xls", 1, "Select NMON Excel file(s) to be processed", , True)
If VarType(FileList) <> vbBoolean Then NumFiles = UBound(FileList)
End If

'=== Configure the "CPU_Busy_Duration" sheet ===
Sheets("CPU_Busy_Duration").Select
Cells.Select
Selection.ClearContents
Range("A1").Select
For i = 1 To NumSettingRows2
ActiveCell.Offset(0, i).Value = ServerName(i)
Next
For i = 1 To 31
ActiveCell.Offset(i, 0).Value = i
Next

'=== Process each file one by one ===
For i = 1 To NumFiles
    FileName = FileList(i)
    CheckCPUFigureFlag = False
    FileNamePart = Split(FileName, ".")
    FileNamePartServer = LCase(FileNamePart(1))
    FileNamePartYear = Left(FileNamePart(2), 4)
    FileNamePartMonth = Mid(FileNamePart(2), 5, 2)
    FileNamePartDay = Right(FileNamePart(2), 2)
    FileNamePartTime = Left(FileNamePart(3), 2)

    '=== Validate whether it's needed to check for CPU busy duration figure for this file ===
    For x = 1 To NumSettingRows2
        If FileNamePartServer = ServerName(x) Then
            If FileNamePartYear = PeriodYear And FileNamePartMonth = PeriodMonth And FileNamePartTime = PeriodTime Then
                CheckCPUFigureFlag = True
                ServerIndex = x
                Exit For
            End If
        End If
    Next

    Workbooks.Open FileName:=FileName
    '=== Check for CPU busy duration figure ===
    If CheckCPUFigureFlag = True Then
        Sheets(CPUSheetName).Select
        NumCPUSheetRows = ActiveSheet.UsedRange.Rows.Count - 2
        CPUBusyDurationMin = WorksheetFunction.CountIf(Range("F1", "F" & NumCPUSheetRows), CPUBusyLevel) * CPUSamplingIntval
    End If

    '=== Prepare for removing non-necessary sheets ===
    NumSheets = Sheets.Count
    NumDeleteSheet = 0
    For m = 1 To NumSheets
        DeleteFlag = True
        For n = 1 To NumSettingRows
            If Sheets(m).Name = SheetList(n) Then
                DeleteFlag = False
                Exit For
            End If
        Next
        If DeleteFlag = True Then
            NumDeleteSheet = NumDeleteSheet + 1
            DeleteSheetList(NumDeleteSheet) = Sheets(m).Name
        End If
    Next

    If NumDeleteSheet < NumSheets Then
        Application.DisplayAlerts = False

        '=== Backup the original file ===
        If NumDeleteSheet >= 1 Then
            sTemp = Dir(FileName)
            Fname = Left(sTemp, InStrRev(sTemp, "."))
            BkFname = Fname & FileBackupExt & ".xls"
            Fname = Fname & "xls"
            Fpath = Left(FileName, InStrRev(FileName, "/"))
            ActiveWorkbook.SaveAs FileName:=Fpath & BkFname, FileFormat:=xlNormal, _
                Password:="", WriteResPassword:="", ReadOnlyRecommended:=False, _
                CreateBackup:=False
        End If

        '=== Start deleting non-required worksheets ===
        For m = 1 To NumDeleteSheet
            Sheets(DeleteSheetList(m)).Delete
        Next

        '=== Save the file ===
        If NumDeleteSheet >= 1 Then
            ActiveWorkbook.SaveAs FileName:=Fpath & Fname, FileFormat:=xlNormal, _
                Password:="", WriteResPassword:="", ReadOnlyRecommended:=False, _
                CreateBackup:=False
        End If

        Application.DisplayAlerts = True
    End If

    '=== Close the workbook ===
    ActiveWorkbook.Close

    '=== Store the CPU busy duration figure ===
    If CheckCPUFigureFlag = True Then
        Sheets("CPU_Busy_Duration").Select
        Range("A1").Select
        ActiveCell.Offset(Int(FileNamePartDay), ServerIndex).Value = CPUBusyDurationMin
    End If
Next

'=== Completed the tasks ===
If NumFiles >= 1 Then
    Sheets("CPU_Busy_Duration").Select
    Range("A1").Select
    For x = 1 To NumSettingRows2
        ActiveCell.Offset(32, x).Value = WorksheetFunction.Sum(Range(ActiveCell.Offset(1, x), ActiveCell.Offset(31, x)))
    Next

    MsgBox "Tasks completed.", vbInformation
End If

End Sub