Public test_dir As String
Public last_col As Variant
Public new_date_format_int As Integer
Public work_week As Variant
Public File_Path As String
Public Mo_Low As Variant
Public Mo_High As Variant
Public Phase_Low As Variant
Public Phase_High As Variant
Sub convert_mft_aku_rev2()
Application.DisplayAlerts = False
'directories
mft_wlt_dir = "T:\Homestead_Test_Data\RTP-WLT\"
Yield_Folder = "T:\New_Structure_TEST\WLT\KGD\KGD_Yield_Folder\"
device_check = UCase(Application.InputBox("Homestead.1.2 or Homestead.1.3?"))
return_jmp:
If device_check = "HOMESTEAD.1.2" Then
project_id = UCase(device_check)
device_type = "DDF360"
ElseIf device_check = "HOMESTEAD.1.3" Then
project_id = UCase(device_check)
device_type = "DDP360"
Else
'ask for entry again since maybe it was misspelled.
device_check = UCase(Application.InputBox("Homestead.1.2 or Homestead.1.3?"))
GoTo return_jmp
End If
'choose which lot
With Application.FileDialog(msoFileDialogFolderPicker)
.InitialFileName = mft_wlt_dir
.Title = "Please Select Folder with Wafer Files"
.Show
test_dir = .SelectedItems(1) & "\"
End With
'filename
mft_wfr = Dir(test_dir & "*.csv", 7)
'where to save converted files to.
save_dir = test_dir & "converted\"
Set fs = CreateObject("Scripting.FileSystemObject")
'if converted folder doesn't exist, create it.
If Not fs.folderexists(save_dir) Then
MkDir (save_dir)
End If
r = 1
'loop through all csv files in the directory.
Do While mft_wfr <> ""
'Application.DisplayAlerts = False
'full path of filename
wfr_path = test_dir & mft_wfr
'add workbook for querytable below.
Workbooks.Add
new_book = ActiveWorkbook.Name
With ActiveSheet.QueryTables.Add(Connection:="TEXT;" & wfr_path, Destination:=Workbooks(new_book).Sheets("Sheet1").Range("A1"))
.Name = "dataset" & r
.FieldNames = True
.RowNumbers = True
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 10000
.TextFilePromptOnRefresh = False
.TextFilePlatform = 850
.TextFileStartRow = 1
.TextFileParseType = xlDelimited
.TextFileTextQualifier = xlTextQualifierDoubleQuote
.TextFileConsecutiveDelimiter = False
.TextFileTabDelimiter = False
.TextFileSemicolonDelimiter = True
.TextFileCommaDelimiter = False
.TextFileSpaceDelimiter = False
.TextFileColumnDataTypes = Array(1)
.TextFileDecimalSeparator = ","
.TextFileThousandsSeparator = "."
.TextFileTrailingMinusNumbers = True
'On Error Resume Next
'error occurs here!!
.Refresh BackgroundQuery:=False
End With
'find last column in the data file
last_col = find_in_rows("", 1, 1) - 1
soft_bin_col = find_in_rows("Soft BIN", 1, 1)
high_limit_row = find_in_col("High Limit->", 1)
low_limit_row = find_in_col("Low Limit->", 1)
'find Mo and Phase limits
setup_col = find_in_rows("Setup ID", 1, 1)
Mo_Low_Col = find_in_rows("Low_Sens1", 1, 1)
Mo_High_col = find_in_rows("High_Sens1", 1, 1)
Phase_lim_col = find_in_rows("Phase 1kHz", 1, 1)
Mo_Low = Cells(low_limit_row, Mo_Low_Col)
Mo_High = Cells(high_limit_row, Mo_High_col)
Phase_Low = Cells(low_limit_row, Phase_lim_col)
Phase_High = Cells(high_limit_row, Phase_lim_col)
'data starts on row 9
first_data_row = 9
setup_data = Cells(9, setup_col)
'find last_data_row
last_data_row = find_in_col_start("", 1, 9) - 1
'calculate number of data points
Number_Die_Tested = last_data_row - first_data_row + 1
'replace extra commas in last column.
Columns(last_col).Select
Selection.Replace What:=",,*", Replacement:="", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
'convert back to standard file format
With Application
.DecimalSeparator = "."
.ThousandsSeparator = ","
.UseSystemSeparators = True
End With
cur_file = ActiveWorkbook.Name
' breaks up file name into project, lot #, wafer #, etc..
underscore = 1
Dim path(4)
For counter = 0 To 3
path(counter) = ExtractElement(mft_wfr, underscore, "__")
underscore = underscore + 1
Next counter
new_lot = "LQ" & Left(path(1), 4)
new_wafer = "W" & Mid(path(1), 6, 2)
'convert date for work week
new_date_format = Left(path(3), 6)
new_month = Left(new_date_format, 2)
new_day = Mid(new_date_format, 3, 2)
new_year = "20" & Right(new_date_format, 2)
new_date_format = new_year & new_month & new_day
' determine Work_Week by opening work week file
Workbooks.Open (Yield_Folder & "Work_Week.xls")
ww = ActiveWorkbook.Name
For counter = 2 To 158
If Val(new_date_format) >= Cells(counter, 1) And Val(new_date_format) <= Cells(counter, 2) Then
work_week = Cells(counter, 3)
End If
Next
'added code for binned work week. 20091216 jcb
For counter = 2 To 158
If Val(Format(Date, "YYYYMMDD")) >= Cells(counter, 1) And Val(Format(Date, "YYYYMMDD")) <= Cells(counter, 2) Then
bin_work_week = Cells(counter, 3)
End If
Next
Workbooks(ww).Close
Workbooks(cur_file).Activate
'arrays for bin counts and percentages.
ReDim bin_array(20)
ReDim bin_array_percentage(20)
For bin_counter = 1 To 20
bin_array(bin_counter) = WorksheetFunction.CountIf(Range(Cells(first_data_row, soft_bin_col), Cells(last_data_row, soft_bin_col)), bin_counter)
bin_array_percentage(bin_counter) = (bin_array(bin_counter) / Number_Die_Tested) * 100
Next bin_counter
'open summary file
Workbooks.Open (Yield_Folder & "KGD_Wafer_Level_Summary_Sheet_Homestead.csv")
yield_file = ActiveWorkbook.Name
'Finds first blank row and uses that row to store the data.
new_Entry_Row = find_in_col("", 1)
'added columns for ClkOff-Idd_avg and std deviation.
Cells(new_Entry_Row, 1).Value = new_date_format
Cells(new_Entry_Row, 2).Value = "RTP"
Cells(new_Entry_Row, 3).Value = "3.30V"
Cells(new_Entry_Row, 4).Value = setup_data
Cells(new_Entry_Row, 5).Value = Spec_ID
Cells(new_Entry_Row, 6).Value = project_id
Cells(new_Entry_Row, 7).Value = new_lot
Cells(new_Entry_Row, 8).Value = "B0"
Cells(new_Entry_Row, 9).Value = new_wafer
Cells(new_Entry_Row, 10).Value = device_type
Cells(new_Entry_Row, 11).Value = Mo_Average
Cells(new_Entry_Row, 12).Value = Idd_Average
Cells(new_Entry_Row, 13).Value = ClkOff_Idd_Average
Cells(new_Entry_Row, 14).Value = Mo_Median
Cells(new_Entry_Row, 15).Value = Idd_median
Cells(new_Entry_Row, 16).Value = Mo_Std_dev
Cells(new_Entry_Row, 17).Value = Idd_Std_dev
Cells(new_Entry_Row, 18).Value = ClkOff_Idd_Std_dev
Cells(new_Entry_Row, 19).Value = Number_Die_Tested
Cells(new_Entry_Row, 20).Value = Bin1_Percentage
Cells(new_Entry_Row, 21).Value = Mo_CpK
Cells(new_Entry_Row, 22).Value = Idd_CpK
Cells(new_Entry_Row, 23).Value = Bin1
Cells(new_Entry_Row, 24).Value = Bin1_Percentage
'Cells(new_Entry_Row, 25).Value = Bin2
'Cells(new_Entry_Row, 26).Value = Bin2_Percentage
'Cells(new_Entry_Row, 27).Value = Bin3
'Cells(new_Entry_Row, 28).Value = Bin3_Percentage
'Cells(new_Entry_Row, 29).Value = Bin4
'Cells(new_Entry_Row, 30).Value = Bin4_Percentage
'Cells(new_Entry_Row, 31).Value = Bin5
'Cells(new_Entry_Row, 32).Value = Bin5_Percentage
'added Bin 6 for 2600Hz failure.
'Cells(new_Entry_Row, 33).Value = Bin6
'Cells(new_Entry_Row, 34).Value = Bin6_Percentage
'added Bin 7 for ClkOff failures
'Cells(new_Entry_Row, 35).Value = Bin7
'Cells(new_Entry_Row, 36).Value = Bin7_Percentage
'added Bin 8 for Phase failures
'Cells(new_Entry_Row, 37).Value = Bin8
'Cells(new_Entry_Row, 38).Value = Bin8_Percentage
'calculates functional die percentage
'updated on 20070508 by jcb
'Cells(new_Entry_Row, 39).Value = Bin1 + Bin4 + Bin5 + Bin6 + Bin7 + Bin8
'Cells(new_Entry_Row, 40).Value = ((Bin1 + Bin4 + Bin5 + Bin6 + Bin7 + Bin8) / Number_Die_Tested) * 100
'added Mo specs to summary sheet.
Cells(new_Entry_Row, 41).Value = Mo_High
Cells(new_Entry_Row, 41).NumberFormat = "##.##"
Cells(new_Entry_Row, 42).Value = Mo_Low
Cells(new_Entry_Row, 42).NumberFormat = "##.##"
'added Phase specs to summary sheet.
Cells(new_Entry_Row, 43).Value = Phase_High
Cells(new_Entry_Row, 43).NumberFormat = "##.##"
Cells(new_Entry_Row, 44).Value = Phase_Low
Cells(new_Entry_Row, 44).NumberFormat = "##.##"
'added clkoffidd limit
Cells(new_Entry_Row, 45).Value = CLKOFFIDD_USL
Cells(new_Entry_Row, 46).Value = comment_par
Cells(new_Entry_Row, 47).Value = work_week
Cells(new_Entry_Row, 48).Value = Format(Date, "YYYYMMDD")
Cells(new_Entry_Row, 49).Value = bin_work_week
'changed so sorts by date now. jcb 20091209
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Sort Key1:=Range("AW2"), Order1:=xlDescending, Key2:=Range("AU2" _
), Order2:=xlAscending, Key3:=Range("G2"), Order3:=xlAscending, HEADER _
:=xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom _
, DataOption1:=xlSortNormal, DataOption2:=xlSortNormal, DataOption3:= _
xlSortNormal
fname1 = Yield_Folder & "KGD_Wafer_Level_Summary_Sheet_Homestead.csv"
fformat = xlCSV
ActiveWorkbook.SaveAs Filename:=fname1, FileFormat:=fformat
ActiveWorkbook.Close SaveChanges:=True
'ActiveWorkbook.Save
Application.DisplayAlerts = False
convert_dir = save_dir & mft_wfr
Workbooks(cur_file).Activate
ActiveWorkbook.SaveAs Filename:=convert_dir, FileFormat:=xlCSV
ActiveWorkbook.Close (True)
'get next file
mft_wfr = Dir()
r = r + 1
Loop
End Sub
Function find_in_rows(search_term, row, start) As Integer
counter = start - 1
Do
counter = counter + 1
Loop While Cells(row, counter) <> search_term
find_in_rows = counter
End Function
Function ExtractElement(Txt, n, Seperator) As String
'Returns the nth element of a text string, where the
'elements are seperated by a specified seperator character.
Dim AllElements As Variant
AllElements = Split(Txt, Seperator)
ExtractElement = AllElements(n - 1)
End Function
Function find_in_col_start(search_term, col, start) As Integer
counter = start - 1
Do
counter = counter + 1
Loop While Cells(counter, col) <> search_term
find_in_col_start = counter
End Function
Function find_in_col(search_term, col) As Integer
counter = 0
Do
counter = counter + 1
Loop While Cells(counter, col) <> search_term
find_in_col = counter
End Function
