Sub Run_AnalySheets()
'*****设置参数***************
Dim FileList As Variant
Dim SheetList(1 To 10) As Variant
Dim FileName As String
Dim NumFiles As Integer
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 NumSettingRows As Integer
Dim Sum As Double
Dim MaxVal As Double
Dim TmpVal As Double
Dim N As Integer
Dim i As Integer
Dim j As Integer
Dim k As Integer
Dim l As Integer
Dim Num As Integer
Dim CPUSheetName As String
Dim xlApp As Excel.Application
Dim xlBook As Excel.Workbook
Dim xlSheet As Excel.Worksheet
Dim CalRow As Integer
Dim Wrow As Integer
Dim Wcol As Integer
Dim NumDataRows As Integer
Dim WRcol As Integer
Dim SFlag As Boolean
Dim MFlag As Boolean
'********Get Set SheetName*****************************
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
'*****建立选择文件列表*******
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
'MsgBox NumFiles
'*****依次打开文件***********
For i = 1 To NumFiles
'********Split select file name********************
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)
'**************Set Write Col Number*****************
Select Case FileNamePart(1)
Case "misapp1"
Wcol = 0
Case "misapp2"
Wcol = 1
Case "misdb1"
Wcol = 2
Case "misdb2"
Wcol = 3
End Select
Select Case FileNamePart(3)
Case "180100"
Wrow = 5
Case "080100"
Wrow = 4
End Select
'*****************Write Subject************************
Sheets(2).Select
For k = 1 To 7
Sheets(2).Cells(Wcol * 7 + k + 1, 1) = FileNamePart(2)
Sheets(2).Cells(Wcol * 7 + k + 1, 2) = FileNamePart(1)
Next
'Me.Save
CPUSheetName = ""
Set xlApp = New Excel.Application
Set xlBook = Nothing
Set xlSheet = Nothing
xlApp.Workbooks.Open FileName:=FileName
Set xlBook = xlApp.Workbooks(1) '一个工作簿
'**********依次计算表中数据*********************************
For j = 1 To NumSettingRows
CPUSheetName = SheetList(j)
If CPUSheetName = "" Then
Exit For
End If
Set xlSheet = xlBook.Worksheets(CPUSheetName) '第一个表
xlSheet.Activate
NumDataRows = xlSheet.UsedRange.Rows.Count
Sum = 0
Num = 0
CalRow = 1
MaxVal = 0
TmpVal = 0
SFlag = True
MFlag = True
Select Case CPUSheetName
Case "CPU_ALL"
CalRow = 2
WRcol = 2
Case "MEM"
CalRow = 4
WRcol = 4
MFlag = False
Case "PAGE"
CalRow = 4
WRcol = 8
Case "IOADAPT"
CalRow = 2
WRcol = 6
SFlag = False
End Select
For N = 2 To NumDataRows - 1
If SFlag Then
'****************计算平均数**************************
If Len(Trim(xlSheet.Cells(N, CalRow))) <> 0 Then
TmpVal = xlSheet.Cells(N, CalRow)
Sum = Sum + TmpVal
Num = Num + 1
End If
'****************计算最大值***************************
If TmpVal > MaxVal Then
MaxVal = TmpVal
End If
Else
'***************计算IO********************************
If Len(Trim(xlSheet.Cells(N, CalRow))) <> 0 Then
Sum = Sum + xlSheet.Cells(N, CalRow)
Sum1 = Sum1 + xlSheet.Cells(N, CalRow + 1)
End If
End If
Next
'**********************写入结果********************************
If SFlag Then
If MFlag Then
ThisWorkbook.Worksheets("Sheet2").Cells(Wcol * 7 + WRcol, Wrow) = Sum / Num * 0.01
ThisWorkbook.Worksheets("Sheet2").Cells(Wcol * 7 + WRcol + 1, Wrow) = MaxVal * 0.01
Else
ThisWorkbook.Worksheets("Sheet2").Cells(Wcol * 7 + WRcol, Wrow) = Sum * 0.01 / (Num * 32768)
ThisWorkbook.Worksheets("Sheet2").Cells(Wcol * 7 + WRcol + 1, Wrow) = MaxVal * 0.01 / 32768
End If
Else
ThisWorkbook.Worksheets("Sheet2").Cells(Wcol * 7 + WRcol, Wrow) = Sum
ThisWorkbook.Worksheets("Sheet2").Cells(Wcol * 7 + WRcol + 1, Wrow) = Sum1
End If
Next
xlBook.Close
Next
MsgBox "OK"
End Sub