Private Sub CmdAnalyze_Click()
On Error Resume Next
'变量定义
Dim i As Long
Dim j As Long
Dim k As Long
Dim DesDataSht As Worksheet
Dim ParaSht As Worksheet
Dim RawDataSht As Worksheet
Dim WaferDataSht As Worksheet
Set DesDataSht = ThisWorkbook.Sheets("格式")
Set ParaSht = ThisWorkbook.Sheets("参数设定")
Set WaferDataSht = ThisWorkbook.Sheets("数据导入")
Dim ItemRange As Range
Dim ItemName As String '项目名称
Dim ItemCol As Long
Dim ReportPath As String '报告存放路径
Dim DefectRow As Long '原始报告中项目名称所在行
Dim LotCount As Long
Dim WaferDataCol As Long
LotCount = 1
WaferDataCol = 1
Dim MaxLotCount As Long
MaxLotCount = CLng(ParaSht.Cells(2, 2))
'参数读取
ReportPath = ParaSht.Cells(1, 2)
'读取所有LotID
Dim LotId() As String
GetSubFolder ReportPath, LotId()
Application.DisplayAlerts = False
'查询LotId,导入数据
Dim LotIdRow As Long
Dim LotIdIndex As Long
For LotIdIndex = 0 To UBound(LotId())
If Not LotId(LotIdIndex) = "" Then
LotIdRow = DesDataSht.Range("C65536").End(xlUp).Row + 1
DesDataSht.Cells(LotIdRow, 3) = LotId(LotIdIndex)
If Not Dir(ReportPath & "\" & LotId(LotIdIndex) & "\Detail Result.csv") = "" Then
Workbooks.Open Filename:=ReportPath & "\" & LotId(LotIdIndex) & "\Detail Result.csv"
Sheets().Move After:=ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count)
Set RawDataSht = ThisWorkbook.Sheets("Detail Result")
DesDataSht.Activate
'查找项目名称列
For i = 1 To 20
If RawDataSht.Cells(i, 1) = "Defect Name" Then
DefectRow = i
Exit For
End If
Next
'提取检测项目的列号
For i = 1 To Application.WorksheetFunction.CountA(RawDataSht.Range(CStr(DefectRow + 2) & ":" & CStr(DefectRow + 2)))
If "Thickness" = RawDataSht.Cells(DefectRow, i) Then
ItemCol = i
Exit For
End If
If "TTV(Line)" = RawDataSht.Cells(DefectRow, i) Then
ItemCol = i
Exit For
End If
Next
'区分良品
Set ItemRange = RawDataSht.Range(RawDataSht.Cells(DefectRow + 2, ItemCol).Address, RawDataSht.Cells(RawDataSht.Range("A65536").End(xlUp).Row, ItemCol).Address).SpecialCells(xlCellTypeVisible)
ItemRange.Copy
WaferDataSht.Cells(WaferDataSht.Range(Cells(65535, WaferDataCol).Address).End(xlUp).Row + 1, WaferDataCol) = LotId(LotIdIndex)
If ItemRange.Rows.Count = 1 Then
DesDataSht.Cells(LotIdRow, 2) = RawDataSht.Cells(DefectRow + 2, ItemCol)
WaferDataSht.Cells(WaferDataSht.Range(Cells(65535, WaferDataCol).Address).End(xlUp).Row + 1, WaferDataCol) = RawDataSht.Cells(DefectRow + 2, ItemCol)
Else
DesDataSht.Cells(LotIdRow, 2) = Application.WorksheetFunction.Average(ItemRange)
WaferDataSht.Range(WaferDataSht.Cells(WaferDataSht.Range(Cells(65535, WaferDataCol).Address).End(xlUp).Row + 1, WaferDataCol).Address).PasteSpecial xlPasteValues
End If
LotCount = LotCount + 1
If LotCount > MaxLotCount Then
LotCount = 1
WaferDataCol = WaferDataCol + 1
End If
Set TemRange = Nothing
RawDataSht.Delete
End If
End If
Next
Application.DisplayAlerts = True
End Sub
On Error Resume Next
'变量定义
Dim i As Long
Dim j As Long
Dim k As Long
Dim DesDataSht As Worksheet
Dim ParaSht As Worksheet
Dim RawDataSht As Worksheet
Dim WaferDataSht As Worksheet
Set DesDataSht = ThisWorkbook.Sheets("格式")
Set ParaSht = ThisWorkbook.Sheets("参数设定")
Set WaferDataSht = ThisWorkbook.Sheets("数据导入")
Dim ItemRange As Range
Dim ItemName As String '项目名称
Dim ItemCol As Long
Dim ReportPath As String '报告存放路径
Dim DefectRow As Long '原始报告中项目名称所在行
Dim LotCount As Long
Dim WaferDataCol As Long
LotCount = 1
WaferDataCol = 1
Dim MaxLotCount As Long
MaxLotCount = CLng(ParaSht.Cells(2, 2))
'参数读取
ReportPath = ParaSht.Cells(1, 2)
'读取所有LotID
Dim LotId() As String
GetSubFolder ReportPath, LotId()
Application.DisplayAlerts = False
'查询LotId,导入数据
Dim LotIdRow As Long
Dim LotIdIndex As Long
For LotIdIndex = 0 To UBound(LotId())
If Not LotId(LotIdIndex) = "" Then
LotIdRow = DesDataSht.Range("C65536").End(xlUp).Row + 1
DesDataSht.Cells(LotIdRow, 3) = LotId(LotIdIndex)
If Not Dir(ReportPath & "\" & LotId(LotIdIndex) & "\Detail Result.csv") = "" Then
Workbooks.Open Filename:=ReportPath & "\" & LotId(LotIdIndex) & "\Detail Result.csv"
Sheets().Move After:=ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count)
Set RawDataSht = ThisWorkbook.Sheets("Detail Result")
DesDataSht.Activate
'查找项目名称列
For i = 1 To 20
If RawDataSht.Cells(i, 1) = "Defect Name" Then
DefectRow = i
Exit For
End If
Next
'提取检测项目的列号
For i = 1 To Application.WorksheetFunction.CountA(RawDataSht.Range(CStr(DefectRow + 2) & ":" & CStr(DefectRow + 2)))
If "Thickness" = RawDataSht.Cells(DefectRow, i) Then
ItemCol = i
Exit For
End If
If "TTV(Line)" = RawDataSht.Cells(DefectRow, i) Then
ItemCol = i
Exit For
End If
Next
'区分良品
Set ItemRange = RawDataSht.Range(RawDataSht.Cells(DefectRow + 2, ItemCol).Address, RawDataSht.Cells(RawDataSht.Range("A65536").End(xlUp).Row, ItemCol).Address).SpecialCells(xlCellTypeVisible)
ItemRange.Copy
WaferDataSht.Cells(WaferDataSht.Range(Cells(65535, WaferDataCol).Address).End(xlUp).Row + 1, WaferDataCol) = LotId(LotIdIndex)
If ItemRange.Rows.Count = 1 Then
DesDataSht.Cells(LotIdRow, 2) = RawDataSht.Cells(DefectRow + 2, ItemCol)
WaferDataSht.Cells(WaferDataSht.Range(Cells(65535, WaferDataCol).Address).End(xlUp).Row + 1, WaferDataCol) = RawDataSht.Cells(DefectRow + 2, ItemCol)
Else
DesDataSht.Cells(LotIdRow, 2) = Application.WorksheetFunction.Average(ItemRange)
WaferDataSht.Range(WaferDataSht.Cells(WaferDataSht.Range(Cells(65535, WaferDataCol).Address).End(xlUp).Row + 1, WaferDataCol).Address).PasteSpecial xlPasteValues
End If
LotCount = LotCount + 1
If LotCount > MaxLotCount Then
LotCount = 1
WaferDataCol = WaferDataCol + 1
End If
Set TemRange = Nothing
RawDataSht.Delete
End If
End If
Next
Application.DisplayAlerts = True
End Sub
