User:Jrkenti/Sandbox2

''   'These are Variable available for every macro! Dim dbQuery As String ' SQL query Dim objMyConn As ADODB.Connection ' SQL connection String Dim objMyCmd As ADODB.Command 'SQL query as a command Dim objMyRecordset As ADODB.Recordset 'SQL result Dim NObjects As Integer ' number of rows Dim NParams As Integer ' number of columns Dim ithObj As Integer ' row counter Dim ithParam As Integer ' columm counter Dim Operator As String 'this hold the initials of the user Dim ii As Integer ' increment counter Dim Buttons As Worksheet ' this is the form where all the data is   Dim Data As Worksheet ' this is the form where all the data is    Dim form As Worksheet ' this is the form where all the data is    Dim LotID As String ' the next lot id name/number indicated by SQL Dim ProcessRow As Integer  Sub ShowDataSheet ' control-shift-D

Set Data = ActiveWorkbook.Worksheets("Data") ActiveWorkbook.Unprotect Data.Visible = True ActiveWorkbook.Protect Data.Activate

End Sub

'This macro is for EVERY button on the Process Tab Sub SetFormPage(ByRef ButtonNum As Integer) CloseFormPage ' in case it was not already closed ProcessRow = ButtonNum + 8 Application.ScreenUpdating = False ActiveWorkbook.Unprotect Set Buttons = ActiveWorkbook.Worksheets("Buttons") Set Data = ActiveWorkbook.Worksheets("Data") Set form = ActiveWorkbook.Worksheets("Form") form.Visible = True Data.Visible = False form.Unprotect form.Select form.Activate form.Range("A1").Select form.Range("A6").Select ' SQL stuff ready for when it is needed in the nested macros Set objMyConn = New ADODB.Connection Set objMyCmd = New ADODB.Command Set objMyRecordset = New ADODB.Recordset objMyConn.ConnectionString = _ "Provider=SQLOLEDB;Data Source=CLTHSQ602\CIS;Initial Catalog=SubstrateCharacterization;User ID=dbreader;Password=readonly;" objMyConn.Open form.Range("Z1").Value = Data.Cells(ProcessRow, 3).Value ' the number of parameters form.Range("B3").Value = Data.Cells(ProcessRow, 2).Value ' the name of the page form.Range("A6:S2000").FillDown form.Range("A6:S2000").Locked = False form.Range("A6:S2000").NumberFormat = "General" form.Range("A3").Formula = "=CountA(A6:A2000)" ii = 1 While (ii <= form.Range("Z1").Value) form.Cells(5, ii).Value = Data.Cells(ProcessRow, ii + 3).Value form.Columns(ii).ColumnWidth = 11 If form.Range("Z1").Value < 8 Then form.Columns(ii).ColumnWidth = 33 - 2 * form.Range("Z1").Value ElseIf form.Range("Z1").Value < 15 Then form.Columns(ii).ColumnWidth = 13 End If      ii = ii + 1 Wend form.Columns(1).ColumnWidth = 20 ActiveWindow.ScrollRow = 1 'the row you want to scroll to   ActiveWindow.ScrollColumn = 1 'the column you want to scroll to    Application.ScreenUpdating = True DoEvents ActiveWorkbook.RefreshAll DoEvents ActiveWorkbook.RefreshAll DoEvents ' repitition seems to be necessary for 100% compliance ActiveWorkbook.RefreshAll Application.ScreenUpdating = False

' get stuff for column A   If form.Range("B3").Value Like "NonInstrumentalCharacterization" Then CreateSliceList End If   If form.Range("A5").Value Like "LOT*WaferID" Then GetLotWafers End If   If form.Range("A5").Value Like "1/3 LOT*WaferID" Then GetThirdLotWafers End If   If form.Range("B3").Value Like "Shipping" Then GetShippableWafers End If   ' get stuff for other columns If form.Range("B3").Value Like "Disposition" Then GetDataFromAllGraded End If   If form.Range("B3").Value Like "CalculatePadRemoval" Then CheckMicrosenseRemoval End If   If form.Range("B3").Value Like "AssembleProcessLot" Then GetNextLotID GetSliceData End If   If form.Range("B3").Value Like "AssembleCleanGroup" Then GetNextLotID CreateCleanGroupForm End If   AddDefaultValues AddHeadPositions If form.Range("B3").Value Like "*Gauge" Then VerifyProcessStep End If   form.Range("A5").Select form.Range("A6").Select form.Protect AllowFiltering:=True, AllowSorting:=True ActiveWorkbook.Protect End Sub

'This button is on the Form Tab, it returns the user to the Process Tab Sub CloseFormPage

Application.ScreenUpdating = False ActiveWorkbook.Unprotect Set Buttons = ActiveWorkbook.Worksheets("Buttons") Set Data = ActiveWorkbook.Worksheets("Data") Set form = ActiveWorkbook.Worksheets("Form") form.Unprotect form.Rows(1).RowHeight = 100 form.Rows(2).RowHeight = 0 form.Range("Z1").Value = 0 Dim colnum As Integer colnum = 2 While colnum < 5 form.Columns(colnum).ColumnWidth = 30 colnum = colnum + 1 Wend While colnum <= 18 form.Columns(colnum).ColumnWidth = 0 colnum = colnum + 1 Wend form.Range("B3").Value = "Empty" form.Range("A5:AZ99999").ClearContents form.Rows("7:99999").Delete ' this causes the file size when saving (for unipoint) to be much smaller. form.Range("A6:R99999").Validation.Delete If form.AutoFilterMode Then form.AutoFilterMode = False form.Visible = False Application.ScreenUpdating = True Buttons.Range("A1").Select ActiveWorkbook.Protect

End Sub

' This is the Proceeed Button ' it can be interrupted, and it can perform steps before and/or after the upload Sub UploadSubFabData Dim toLoad As Workbook Dim toLoadS As Worksheet ActiveWorkbook.Unprotect Set form = ActiveWorkbook.ActiveSheet form.Unprotect Application.ScreenUpdating = False ' SQL stuff ready for when it is needed Set objMyConn = New ADODB.Connection Set objMyCmd = New ADODB.Command Set objMyRecordset = New ADODB.Recordset objMyConn.ConnectionString = _ "Provider=SQLOLEDB;Data Source=CLTHSQ602\CIS;Initial Catalog=SubstrateCharacterization;User ID=dbreader;Password=readonly;" objMyConn.Open NObjects = form.Range("A3").Value NParams = form.Range("Z1").Value If NObjects > 0 Then If form.Range("B3").Value Like "*_Gauge" Then If Not form.Range("B3").Value Like "NFace_Mount_Gauge" And Not form.Range("B3").Value Like "SF2_AlMount_Gauge" Then CheckGaugeRemoval End If           BuildOfflineTable End If       If form.Range("B3").Value Like "CalculatePadRemoval" Then MsgBox ("There is no need to upload this data, it is all already in the system") End End If       If form.Range("B3").Value Like "AssembleProcessLot" Or _ form.Range("B3").Value Like "Shipping" Or _ form.Range("B3").Value Like "DispositionSubstrates" Or _ form.Range("B3").Value Like "AssembleCleanGroup" Then DeleteExtraData If NObjects < 1 Then MsgBox "No Objects to Load!" End End If           If vbNo = MsgBox("Does this look right?", vbYesNo) Then End End If       If form.Range("B3").Value Like "AssembleProcessLot" Then DeleteFromSliceData Operator = UCase(InputBox("What are your initials?", "Scan or Type your Initials", "Operator")) form.Rows(1).RowHeight = 0: form.Rows(2).RowHeight = 100 Set toLoad = Workbooks.Add Set toLoadS = toLoad.ActiveSheet toLoadS.Range("A1").Value = "Date" toLoadS.Range("B1").Value = "Process" toLoadS.Range("C1").Value = "Object" toLoadS.Range("D1").Value = "parameter" toLoadS.Range("E1").Value = "value" ithObj = 0 While ithObj < NObjects ithParam = 1 toLoadS.Cells(2 + ithObj * NParams, 1).Value = Now toLoadS.Cells(2 + ithObj * NParams, 2).Value = form.Cells(3, 2) toLoadS.Cells(2 + ithObj * NParams, 3).Value = form.Cells(ithObj + 6, 1) toLoadS.Cells(2 + ithObj * NParams, 4).Value = "Operator" toLoadS.Cells(2 + ithObj * NParams, 5).Value = Operator While ithParam < NParams toLoadS.Cells(2 + ithObj * NParams + ithParam, 1).Value = Now toLoadS.Cells(2 + ithObj * NParams + ithParam, 2).Value = form.Cells(3, 2) toLoadS.Cells(2 + ithObj * NParams + ithParam, 3).Value = form.Cells(ithObj + 6, 1) toLoadS.Cells(2 + ithObj * NParams + ithParam, 4).Value = form.Cells(4, ithParam + 1) toLoadS.Cells(2 + ithObj * NParams + ithParam, 5).Value = form.Cells(ithObj + 6, ithParam + 1) ithParam = ithParam + 1 Wend ithObj = ithObj + 1 Wend Path = "Z:\Operations\Characterization\2 inch wafer grading\ManualData\SubFabProcess." Path = Path & form.Range("A1").Value & "." & form.Range("B3").Value & "." & Operator & ".csv" toLoad.SaveAs Path, xlCSV:  toLoad.Close Application.ScreenUpdating = True form.Range("A2").Select form.Protect ActiveWorkbook.Protect If form.Range("B3").Value = "AssembleProcessLot" Then BuildPaperLotTraveler If form.Range("B3").Value = "ReceiveFromCG" Then BuildPaperBouleTraveler If form.Range("B3").Value = "AssembleCleanGroup" Then BuildPuckStickers If form.Range("B3").Value = "Shipping" Then BuildShippingForm Else MsgBox "No Objects to Load!" End If End Sub

Sub ExitNoSave If Workbooks.Count = 1 Then Application.DisplayAlerts = False Application.Quit Else ActiveWorkbook.Close SaveChanges:=False End If End Sub

Sub CreateSliceList Dim bouleID As String Dim sliceNumString As String Dim sliceNum As Integer Dim topNum As Integer Dim botNum As Integer Dim pass As Boolean pass = False ' get the bouleID While pass = False bouleID = InputBox("Please SCAN in the BouleID. " & vbNewLine & " (adhere to the indicated format)", "What is the BouleID?", "H11-0000") If bouleID = "" Then Exit Sub pass = bouleID Like "[HG][1234567890ABC][1-8]-[0-9][0-9][0-9][0-9]" If bouleID Like "H11-0000" Then pass = False Wend pass = False While pass = False ' get the number of slices sliceNumString = InputBox("How many slices are in " & bouleID, "What is the number of slices?", "0") If sliceNumString = "" Then Exit Sub If IsNumeric(sliceNumString) Then pass = True If pass Then pass = 0 < CInt(sliceNumString) And CInt(sliceNumString) < 40 Wend sliceNum = CInt(sliceNumString) ii = 1: While ii <= sliceNum: form.Cells(5 + ii, 1).Value = bouleID & "-" & ii: ii = ii + 1: Wend

pass = False While pass = False ' get the position of the TOP slice sliceNumString = InputBox("What is the slice number of the top-most 2-inch slice?", "Which?", "0") If sliceNumString = "" Then Exit Sub If IsNumeric(sliceNumString) Then pass = True If pass Then pass = 0 < CInt(sliceNumString) And CInt(sliceNumString) < 40 Wend topNum = CInt(sliceNumString) ii = 1: While ii < topNum: form.Cells(5 + ii, 2).Value = "Small": ii = ii + 1: Wend form.Cells(5 + ii, 2).Value = "Top" pass = False While pass = False ' get the position of the bottom slice sliceNumString = InputBox("What is the slice number of the bottom-most 2-inch slice?", "Witch!", "0") If sliceNumString = "" Then Exit Sub If IsNumeric(sliceNumString) Then pass = True If pass Then pass = 0 < CInt(sliceNumString) And CInt(sliceNumString) < 40 Wend botNum = CInt(sliceNumString) ii = ii + 1: While ii < botNum: form.Cells(5 + ii, 2).Value = "2inch": ii = ii + 1: Wend form.Cells(5 + ii, 2).Value = "Bottom" ii = ii + 1: While ii <= sliceNum: form.Cells(5 + ii, 2).Value = "Small": ii = ii + 1: Wend

End Sub

Sub GetLotWafers Dim pass As Boolean pass = False While pass = False LotID = InputBox("Please SCAN in the LotID. Please follow one of the suggested formats", "What is the LotID?", "3840   or    Clean-1234") If LotID = "" Then End pass = LotID Like "[1-39][0-9][0-9][0-9]" Or LotID Like "Clean-[0-9][0-9][0-9][0-9]" Wend Set objMyCmd.ActiveConnection = objMyConn dbQuery = "SELECT ObjectID FROM [SubstrateCharacterization].[dbo].[ManualData] " & _ "WHERE ObjectID in (SELECT ObjectID FROM [SubstrateCharacterization].[dbo].[ManualData] " & _           "WHERE process like 'Assemble%' and ((parameter like 'LotID' and ObjectID not in " & _            "(Select ObjectID from [SubstrateCharacterization].[dbo].[ManualData] " & _ "where parameter like 'Disposition')) or parameter like 'CleanGroup')and value like '" & _           LotID & "') and process like 'Assemble%' and Parameter like 'Position' order by value" objMyCmd.CommandText = dbQuery objMyCmd.CommandType = adCmdText Set objMyRecordset.Source = objMyCmd objMyRecordset.Open form.Cells(6, 1).CopyFromRecordset objMyRecordset objMyRecordset.Close form.Range("B6").Select

End Sub

Sub GetThirdLotWafers ' this function is very similar to the previous, but it gets only a third of a lot, and it does it twice. Dim pass As Boolean Dim blockNo As String pass = False While pass = False LotID = InputBox("Please SCAN the lot of one of the blocks being processed") If LotID = "" Then End pass = LotID Like "[1-3][0-9][0-9][0-9]" Or LotID Like "Clean-[0-9][0-9][0-9][0-9]" Wend blockNo = InputBox("Which block from " & LotID & " is this?") blockNo = Right(blockNo, 1) Set objMyCmd.ActiveConnection = objMyConn dbQuery = "SELECT ObjectID FROM [SubstrateCharacterization].[dbo].[ManualData] " & _ "WHERE ObjectID in (SELECT ObjectID FROM [SubstrateCharacterization].[dbo].[ManualData] " & _               "WHERE process like 'Assemble%' and (parameter like 'LotID' or parameter like 'CleanGroup') " & _                "and value like '" & LotID & "') and process like 'Assemble%' " & _ "and Parameter like 'Position' and value like 'Block" & blockNo & ".P_' order by value " objMyCmd.CommandText = dbQuery objMyCmd.CommandType = adCmdText Set objMyRecordset.Source = objMyCmd objMyRecordset.Open form.Cells(6, 1).CopyFromRecordset objMyRecordset objMyRecordset.Close LotID = InputBox("Please SCAN the lot of the other block being processed (or cancel)") If LotID = "" Then LotID = "ErrorError" If LotID <> "ErrorError" Then blockNo = InputBox("Which block from " & LotID & " is this?") blockNo = Right(blockNo, 1) Set objMyCmd.ActiveConnection = objMyConn dbQuery = "SELECT ObjectID FROM [SubstrateCharacterization].[dbo].[ManualData] " & _ "WHERE ObjectID in (SELECT ObjectID FROM [SubstrateCharacterization].[dbo].[ManualData] " & _               "WHERE process like 'Assemble%' and (parameter like 'LotID' or parameter like 'CleanGroup') " & _                "and value like '" & LotID & "') and process like 'Assemble%' " & _ "and Parameter like 'Position' and value like 'Block" & blockNo & ".P_' order by value " objMyCmd.CommandText = dbQuery objMyCmd.CommandType = adCmdText Set objMyRecordset.Source = objMyCmd objMyRecordset.Open form.Cells(9, 1).CopyFromRecordset objMyRecordset objMyRecordset.Close form.Range("B6").Select

End Sub

Sub GetNextLotID Set objMyCmd.ActiveConnection = objMyConn dbQuery = "SELECT top 1 value FROM [SubstrateCharacterization].[dbo].[ManualData]" & _ " where process = 'AssembleProcessLot' and parameter like 'LotID' and value like '[3-9][0-9][0-9][0-9]'" & _ " Order by value desc" If form.Range("B3").Value Like "*Clean*" Then dbQuery = "SELECT top 1 value FROM [SubstrateCharacterization].[dbo].[ManualData]" & _ " where process = 'AssembleCleanGroup' and parameter like 'CleanGroup' and value like 'Clean-[0-9][0-9][0-9][0-9]'" & _ " Order by value desc" End If   objMyCmd.CommandText = dbQuery objMyCmd.CommandType = adCmdText Set objMyRecordset.Source = objMyCmd objMyRecordset.Open form.Cells(6, 1).CopyFromRecordset objMyRecordset objMyRecordset.Close If form.Range("B3").Value Like "*Clean*" Then LotID = "Clean-" & (Right(form.Cells(6, 1).Value, 4) + 1) Else LotID = 1 + form.Cells(6, 1).Value End If   form.Cells(6, 1).Value = "" End Sub

Sub CheckGaugeRemoval

Dim GaugeFile As Workbook Set objMyCmd.ActiveConnection = objMyConn dbQuery = "SELECT top 9 ObjectID, value from [SubstrateCharacterization].[dbo].[ManualData] Where EventDate in " & _ "(Select Top 1 EventDate from [SubstrateCharacterization].[dbo].[ManualData] " & _                       "Where process like '%Gauge' and process not like '" & _                        form.Cells(3, 3).Value & "' and ObjectID like '" & form.Cells(6, 1).Value & _                        "' order by EventDate desc) and parameter like '%Thickness' order by EventDate desc" objMyCmd.CommandText = dbQuery objMyCmd.CommandType = adCmdText Set objMyRecordset.Source = objMyCmd objMyRecordset.Open form.Cells(6, 10).CopyFromRecordset objMyRecordset objMyRecordset.Close

form.Range("K6:K14").NumberFormat = "General" form.Range("K6:K14").Value = form.Range("K6:K14").Value

form.Cells(6, 12).Formula = "=OFFSET(K$5,MATCH(A6,J$6:J$14,0),0)" form.Range("L6:L14").FillDown form.Cells(6, 13).Formula = "=Min(B6:B8)":       form.Cells(6, 14).Formula = "=Max(B6:B8)" ' Block 1 min and max form.Cells(9, 13).Formula = "=Min(B9:B11)":      form.Cells(9, 14).Formula = "=Max(B9:B11)" 'Block 2 min and max form.Cells(12, 13).Formula = "=Min(B12:B14)":    form.Cells(12, 14).Formula = "=Max(B12:B14)" 'Block 3 min and max form.Cells(7, 13).Formula = "=Min(L6:L8)":       form.Cells(7, 14).Formula = "=Max(L6:L8)" ' Block 1 min and max previous step form.Cells(10, 13).Formula = "=Min(L9:L11)":     form.Cells(10, 14).Formula = "=Max(L9:L11)" 'Block 2 min and max previous step form.Cells(13, 13).Formula = "=Min(L12:L14)":    form.Cells(13, 14).Formula = "=Max(L12:L14)" 'Block 3 min and max previous step form.Cells(8, 13).Formula = "=((N7-N6) + (M7-M6))/2":    form.Cells(8, 14).Formula = "=N6-M6" 'Block 1 removal & TTV form.Cells(11, 13).Formula = "=((N10-N9) + (M10-M9))/2": form.Cells(11, 14).Formula = "=N9-M9" 'Block 2 removal & TTV form.Cells(14, 13).Formula = "=((N13-N12) + (M13-M12))/2": form.Cells(14, 14).Formula = "=N12-M12" 'Block 3 removal & TTV answer = MsgBox( _           "Removal for the first Block = " & form.Range("M8").Value & vbNewLine & _            "TTV for the first Block = " & form.Range("N8").Value & vbNewLine & vbNewLine & _            "Removal for the second Block = " & form.Range("M11").Value & vbNewLine & _            "TTV for the second Block = " & form.Range("N11").Value & vbNewLine & vbNewLine & _            "Removal for the third Block = " & form.Range("M14").Value & vbNewLine & _            "TTV for the third Block = " & form.Range("N14").Value & vbNewLine & vbNewLine & _            "Use the above data to determine if you need to perform REWORK" & vbNewLine & vbNewLine & _            "Did you make a typo in the numbers?", vbYesNo, "Evaluate the Removal & Check For Typos")

If answer = vbYes Then MsgBox "Sorry to hear about the typo." & vbNewLine & "Please fix the number and resubmit.", vbExclamation End End If

End Sub

Sub BuildOfflineTable ' in case the network is down Application.ScreenUpdating = False Set GaugeFile = Workbooks.Add Set objMyCmd.ActiveConnection = objMyConn dbQuery = "SELECT * from [SubstrateCharacterization].[dbo].[ManualData]" & _ " where EventDate > getDate - 30 and Process like '%Gauge' " & _ " order by EventDate desc, ObjectID" objMyCmd.CommandText = dbQuery objMyCmd.CommandType = adCmdText Set objMyRecordset.Source = objMyCmd objMyRecordset.Open GaugeFile.Worksheets(1).Cells(2, 1).CopyFromRecordset objMyRecordset objMyRecordset.Close GaugeFile.ActiveSheet.Range("A1").Value = "date" GaugeFile.ActiveSheet.Range("B1").Value = "Process" GaugeFile.ActiveSheet.Range("C1").Value = "ObjectID" GaugeFile.ActiveSheet.Range("D1").Value = "parameter" GaugeFile.ActiveSheet.Range("E1").Value = "value" Application.DisplayAlerts = False If GaugeFile.Worksheets(1).Range("A5").Value > 10 Then If vbNullString <> Dir("C:\Users\Jmann\", vbDirectory) Then GaugeFile.SaveAs "C:\Users\Jmann\GaugeFile.xlsx" ElseIf vbNullString <> Dir("C:\Users\CIS_Subfab06\", vbDirectory) Then GaugeFile.SaveAs "C:\Users\CIS_Subfab06\GaugeFile.xlsx" End If       End If        GaugeFile.Close Application.DisplayAlerts = True Application.ScreenUpdating = True End Sub

Sub GetDataFromAllGraded ' this is for disposition a substrate

Dim AllGraded As Workbook Dim AllGradedS As Worksheet Dim SliceData As Workbook Dim SliceDataS As Worksheet Dim WaferID As String Dim rng As Range Dim rowID As Variant Dim initials As Variant Workbooks.Open Filename:="Z:\Operations\Characterization\2 inch wafer grading\GradedWafersByWeek\SQLAllWaferData.xls" Set AllGraded = ActiveWorkbook Set AllGradedS = AllGraded.ActiveSheet ithObj = 6 NObjects = form.Range("A3").Value While ithObj <= NObjects + 5 WaferID = form.Cells(ithObj, 1).Value Set rng = AllGradedS.Range("B:B").Find(WaferID) If Not rng Is Nothing Then rowID = Application.WorksheetFunction.Match(WaferID, AllGraded.Worksheets(1).Range("B:B"), 0) form.Cells(ithObj, 2).Value = AllGradedS.Cells(rowID, 76).Value form.Cells(ithObj, 3).Value = AllGradedS.Cells(rowID, 77).Value form.Cells(ithObj, 4).Value = AllGradedS.Cells(rowID, 78).Value form.Cells(ithObj, 5).Value = AllGradedS.Cells(rowID, 79).Value form.Cells(ithObj, 6).Value = AllGradedS.Cells(rowID, 80).Value form.Cells(ithObj, 7).Value = AllGradedS.Cells(rowID, 81).Value form.Cells(ithObj, 8).Value = AllGradedS.Cells(rowID, 82).Value 'product grade form.Cells(ithObj, 9).Value = AllGradedS.Cells(rowID, 83).Value form.Cells(ithObj, 10).Value = AllGradedS.Cells(rowID, 84).Value form.Cells(ithObj, 11).Value = AllGradedS.Cells(rowID, 85).Value form.Cells(ithObj, 12).Value = AllGradedS.Cells(rowID, 86).Value form.Cells(ithObj, 13).Value = AllGradedS.Cells(rowID, 87).Value form.Cells(ithObj, 14).Value = AllGradedS.Cells(rowID, 88).Value 'seed grade Else MsgBox WaferID & " is not in the system. There is no data for this wafer.", vbInformation End If       ithObj = ithObj + 1 Wend

Set objMyCmd.ActiveConnection = objMyConn dbQuery = "Select Type, SliceGrade FROM ( " & _           "Select ObjectID, value as SliceGrade from [SubstrateCharacterization].[dbo].[ManualData] " & _            "Where Process like 'AssembleProcessLot' and parameter like 'SliceGrade' " & _            ") as R1                                Full Join ( " & _            "Select ObjectID, value as Type from [SubstrateCharacterization].[dbo].[ManualData] " & _            "Where Process like 'ReceiveFromCG' and parameter like 'ProcessType' " & _            ") as R2 on left(R1.ObjectID,8) = R2.ObjectID     Full Join ( " & _            "Select ObjectID, value as Position from [SubstrateCharacterization].[dbo].[ManualData] " & _            "Where Process like 'AssembleProcessLot' and parameter like 'Position' " & _            ") as R3 on R1.ObjectID = R3.ObjectID     Full Join ( " & _            "Select ObjectID, value as LotID from [SubstrateCharacterization].[dbo].[ManualData] " & _ "Where Process like 'AssembleProcessLot' and parameter like 'LotID' " & _ ") as R4 on R1.ObjectID = R4.ObjectID    Full Join ( " & _ "Select ObjectID, value as Disposition from [SubstrateCharacterization].[dbo].[ManualData] " & _ "Where Process like 'Disposition' and parameter like 'Disposition' " & _ ") as R5 on R1.ObjectID = R5.ObjectID  Where Disposition is null and lotid = '" & LotID & "' order by Position "    objMyCmd.CommandText = dbQuery    objMyCmd.CommandType = adCmdText    Set objMyRecordset.Source = objMyCmd    objMyRecordset.Open    form.Cells(6, 16).CopyFromRecordset objMyRecordset    objMyRecordset.Close    DoEvents    ActiveWorkbook.RefreshAll    DoEvents    ActiveWorkbook.RefreshAll    AllGraded.Close    Application.ScreenUpdating = True    form.Range("O6").Select    MsgBox "Alert! Do NOT upload data for a wafer unless all characterization data is available. " & _            "Please, don't disposition a wafer that has missing data, wait until it is resolved.  " & _            "Wafers without dispositions will not be loaded - they will need to be REDISPOSITIONED LATER.", vbCritical End Sub

Sub GetShippableWafers

'   '' This step is now performed by scanning the barcodes on the puck stickers '' The list of possible wafers is not needed to perform this step. '' The code below is defunct. '

'Set objMyCmd.ActiveConnection = objMyConn 'dbQuery = "Select ObjectID, , , DispositionP_Grade + '/' + " & _ '   "CASE WHEN DispositionS_Grade is null then 'NULL' ELSE DispositionS_Grade END as Grade FROM " & _ '   "(Select ObjectID, Process + Parameter as PP, Value from [SubstrateCharacterization].[dbo].[ManualData]) as sourcetable " & _ '   "PIVOT ( max(value) for PP in (InspectionResult, ShippingDestination,DispositionP_Grade, DispositionS_Grade)) as pivottable " & _ '   "where InspectionResult = 'Pass' and ShippingDestination is null and DispositionP_Grade is not null" 'objMyCmd.CommandText = dbQuery 'objMyCmd.CommandType = adCmdText 'Set objMyRecordset.Source = objMyCmd 'objMyRecordset.Open 'Form.Cells(6, 1).CopyFromRecordset objMyRecordset 'objMyRecordset.Close End Sub

Sub BuildPaperLotTraveler Dim BlockSet As String Dim PaperTraveler As Workbook Workbooks.Open Filename:="Z:\Operations\Characterization\2 inch wafer grading\ManualData\SF-F-056.xlsm", ReadOnly:=True Set PaperTraveler = ActiveWorkbook Dim PaperTravelerS As Worksheet Set PaperTravelerS = PaperTraveler.Worksheets("SF1.0") Dim PaperTravelerT As Worksheet Set PaperTravelerT = PaperTraveler.Worksheets("SF2.0") BlockSet = Left(form.Range("D6"), 2)

PaperTravelerS.Cells(2, 3).Value = BlockSet PaperTravelerS.Cells(4, 1).Value = form.Cells(6, 3) ii = 6 While ii < 15 PaperTravelerS.Cells(1, ii + 1).Value = form.Cells(ii, 12) PaperTravelerS.Cells(2, ii + 1).Value = form.Cells(ii, 1) PaperTravelerT.Cells(1, ii + 1).Value = form.Cells(ii, 13) ii = ii + 1 Wend PaperTraveler.SaveAs Filename:="Z:\Operations\Characterization\2 inch wafer grading\ManualData\SQLArchive\BouleTraveler_" & PaperTravelerS.Cells(4, 1).Value & ".xlsm"

End Sub

Sub BuildPaperBouleTraveler Dim PaperTraveler As Workbook Dim PaperTravelerS As Worksheet ii = 0 While ii < form.Range("A3") Workbooks.Open Filename:="Z:\Operations\Characterization\2 inch wafer grading\ManualData\SF-F-003.xlsm", ReadOnly:=True Set PaperTraveler = ActiveWorkbook Set PaperTravelerS = PaperTraveler.Worksheets("NoBarCodes") PaperTravelerS.Activate PaperTravelerS.Cells(4, 1).Value = form.Cells(6 + ii, 1) PaperTravelerS.Cells(1, 1).Value = form.Cells(6 + ii, 2) PaperTravelerS.Cells(1, 4).Value = form.Cells(6 + ii, 3) PaperTraveler.SaveAs Filename:="Z:\Operations\Characterization\2 inch wafer grading\ManualData\SQLArchive\LotTraveler_" & PaperTravelerS.Cells(4, 1).Value & ".xlsm" ii = ii + 1 Wend End Sub

Sub BuildPuckStickers

Set form = ActiveWorkbook.ActiveSheet Dim PuckBook As Workbook Dim PuckSheet As Worksheet Workbooks.Open Filename:="Z:\Operations\Characterization\2 inch wafer grading\ManualData\PuckLabelsMacro_withBarcodes.xlsm", ReadOnly:=True Set PuckBook = ActiveWorkbook Set PuckSheet = PuckBook.Worksheets("PrintHere") PuckSheet.Activate ' This is for use with the older version, that did not have barcodes 'With PuckSheet '  .Range(.Cells(1, 1), .Cells(4, Form.Range("A3"))).FillRight 'End With ii = 1 While ii <= form.Range("A3") PuckSheet.Cells(2, ii).Value = "   " & form.Cells(5 + ii, 1) PuckSheet.Cells(3, ii).Value = "   " & form.Cells(5 + ii, 3) ii = ii + 1 Wend If form.Range("A3") < 48 Then MsgBox ("Remember to print only the first " & form.Range("A3") & " pages.") ElseIf form.Range("A3") > 48 Then MsgBox ("There are more wafers here than there are formatted pages. " & _               "Consider breaking this into multiple groups, or fixing the formatting manually.") End If

End Sub

Sub BuildShippingForm Set form = ActiveWorkbook.ActiveSheet Dim ShipBook As Workbook Dim ShipSheet As Worksheet Workbooks.Open Filename:="Z:\Operations\Characterization\2 inch wafer grading\ManualData\OPS-F-006.xlsm", ReadOnly:=True Set ShipBook = ActiveWorkbook Set ShipSheet = ShipBook.Worksheets("Fuji") ShipSheet.Activate ii = 1 While ii <= form.Range("A3") ShipSheet.Cells(ii + 7, 3).Value = form.Cells(5 + ii, 1) ii = ii + 1 Wend Application.Run "'OPS-F-006.xlsm'!GetDataForShippingForm" ii = 1 While ii <= form.Range("A3") ShipSheet.Cells(ii + 7, 3).Value = _ Left(form.Cells(5 + ii, 1), 8) & ".1-" & Split(form.Cells(5 + ii, 1), "-")(2) & "-0-0" ' the CIS reclaim number is NOT USED, the second '-0' is the FUJI reclaim number ii = ii + 1 Wend End Sub

Sub GetSliceData ' this is for assisting lot creation

Dim AllGraded As Workbook Dim AllGradedS As Worksheet Dim WaferID As String Dim rng As Range Dim rowID As Variant Dim initials As Variant Dim BlockSet As String form.Columns(6).ColumnWidth = 0 ' users dont need to see this form.Columns(7).ColumnWidth = 0 ' users dont need to see this form.Columns(8).ColumnWidth = 0 ' users dont need to see this BlockSet = UCase(InputBox("What BlockSet are you using?" & vbNewLine & vbNewLine & _ " Type 'CC' if you are using 'CC1 & CC2 & CC3'", "Type in the BlockSet", "CC")) Workbooks.Open Filename:="Z:\Operations\Characterization\2 inch wafer grading\GradedWafersByWeek\SQLSliceData.xls" Set AllGraded = ActiveWorkbook Set AllGradedS = AllGraded.ActiveSheet AllGradedS.Range("A2:A10000").Copy:  form.Range("A6").PasteSpecial Paste:=xlPasteValues ' wafers AllGradedS.Range("AF2:AF10000").Copy: form.Range("E6").PasteSpecial Paste:=xlPasteValues ' grade AllGradedS.Range("AC2:AC10000").Copy: form.Range("F6").PasteSpecial Paste:=xlPasteValues ' miscut grade AllGradedS.Range("Z2:Z10000").Copy:  form.Range("G6").PasteSpecial Paste:=xlPasteValues ' microsense grade AllGradedS.Range("AD2:AD10000").Copy: form.Range("H6").PasteSpecial Paste:=xlPasteValues ' defect grade AllGradedS.Range("E2:E10000").Copy:  form.Range("I6").PasteSpecial Paste:=xlPasteValues ' priority AllGradedS.Range("C2:C10000").Copy:  form.Range("J6").PasteSpecial Paste:=xlPasteValues ' type AllGradedS.Range("D2:D10000").Copy:  form.Range("K6").PasteSpecial Paste:=xlPasteValues ' date form.Range("K6:K9900").NumberFormat = "mmm dd, yyyy" AllGradedS.Range("F2:F10000").Copy:  form.Range("L6").PasteSpecial Paste:=xlPasteValues ' designation AllGradedS.Range("X2:X10000").Copy:  form.Range("M6").PasteSpecial Paste:=xlPasteValues ' warp form.Range("C6").Formula = "=if(isblank(B6),"""",""" & LotID & """)" form.Range("D6").Formula = "=if(isblank(B6),"""",concatenate(""" & BlockSet & """,mid(B6,6,1)))" form.Range("C6:D10000").FillDown AllGraded.Close Application.ScreenUpdating = True form.Range("A5:M10000").AutoFilter form.Range("$A$5:$M$10000").AutoFilter Field:=5, Criteria1:=Array("Epi/Seed", "Epi", "ShowAsEpi"), Operator:=xlFilterValues form.Range("$A$5:$M$10000").AutoFilter Field:=10, Criteria1:="Gen 2.5"

End Sub

Sub DeleteFromSliceData ' this is only important when multiple lots are made on the same day

Dim AllGraded As Workbook Dim AllGradedS As Worksheet Dim WaferID As String Dim rng As Range Dim rowID As Variant Dim initials As Variant Workbooks.Open Filename:="Z:\Operations\Characterization\2 inch wafer grading\GradedWafersByWeek\SQLSliceData.xls" Set AllGraded = ActiveWorkbook Set AllGradedS = AllGraded.ActiveSheet ii = 6 While ii < 15 WaferID = form.Cells(ii, 1).Value If Len(WaferID) > 1 Then Set rng = AllGradedS.Range("A:A").Find(WaferID) If Not rng Is Nothing Then rowID = Application.WorksheetFunction.Match(WaferID, AllGradedS.Range("A:A"), 0) AllGradedS.Rows(rowID).Delete Else MsgBox WaferID & " is not in the system anymore. " & _ "It was used for a different lot already. " & _ "Please try again, sorry!!", vbCritical CloseFormPage End End If       End If        ii = ii + 1 Wend

Application.DisplayAlerts = False AllGraded.SaveAs Filename:="Z:\Operations\Characterization\2 inch wafer grading\GradedWafersByWeek\SQLSliceData.xls" AllGraded.Close Application.DisplayAlerts = True

End Sub

Sub DeleteExtraData

Dim lrow As Long Dim position As Integer Dim col As Integer Dim rng As String Dim Pages Dim NumBadColumns Dim SortRanges If form.AutoFilterMode Then form.AutoFilterMode = False 'only SOME pages get columns deleted, and only SOME pages get rows deleted Pages = [{"AssembleProcessLot","Disposition","AssembleCleanGroup","Shipping"}] NumBadColumns = [{5, 2, 4, 1}] SortRanges = [{"B6","O6","B6","C6"}] For ii = 1 To UBound(Pages) If Pages(ii) = form.Range("B3").Value Then col = NumBadColumns(ii) rng = SortRanges(ii) form.Range("Z1").Value = form.Range("Z1").Value - col ' the last few parameter do not need to be loaded back into SQL Range("A6", Range("R6").End(xlDown)).Sort Key1:=Range(rng), Order1:=xlAscending, Header:=xlNo lrow = Cells(Rows.Count, 2).End(xlUp).Row + 1 form.Range(Cells(lrow, 1), Cells(10060, 13)).ClearContents NObjects = form.Range("A3").Value End If   Next ii    Application.ScreenUpdating = True Application.Goto Reference:=Range("A6"), Scroll:=True DoEvents ActiveWorkbook.RefreshAll DoEvents ActiveWorkbook.RefreshAll Application.ScreenUpdating = False NObjects = form.Range("A3").Value NParams = form.Range("Z1").Value form.Range("A3").Formula = "=CountA(A6:A2000)"

End Sub

Sub CreateCleanGroupForm

Dim Recipe As String Set objMyCmd.ActiveConnection = objMyConn dbQuery = "Select ObjectID, , , '', AssembleProcessLotLotID, DispositionDisposition, DispositionP_Grade, DispositionS_Grade from " & _ "(Select ObjectID, Process+Parameter as PP, Value from SubstrateCharacterization.dbo.ManualData) as source " & _ "Pivot (Max(value) for PP in (AssembleProcessLotLotID, DispositionDisposition, DispositionP_Grade, " & _ "DispositionS_Grade, AssembleCleanGroupCleanGroup, CleaningRecipe)) as piv " & _ "where CleaningRecipe is null and DispositionP_Grade is not null and DispositionS_Grade is not null " & _ "order by AssembleProcessLotLotID" objMyCmd.CommandText = dbQuery objMyCmd.CommandType = adCmdText Set objMyRecordset.Source = objMyCmd objMyRecordset.Open form.Cells(6, 1).CopyFromRecordset objMyRecordset objMyRecordset.Close Recipe = InputBox("What Recipe should be used?", "SCAN in the recipe", "water rinse")

form.Range("C6").Formula = "=if(isblank(B6),"""",""" & LotID & """)" form.Range("D6").Formula = "=if(isblank(B6),"""",""" & Recipe & """)" form.Range("C6:D1001").FillDown

End Sub

Sub CheckMicrosenseRemoval

Dim Wafer As String ii = 6 While ii < 6 + form.Range("A3") Wafer = form.Cells(ii, 1).Value

Set objMyCmd.ActiveConnection = objMyConn dbQuery = " Declare @recipe nvarchar(100) " & _ " Set @recipe = (Select top 1 recipe" & _                 "        FROM [SubstrateCharacterization].[dbo].[MicroSenseFormatted]" & _                  "        where id = '" & Wafer & "' order by msdatetime desc) " & _ " Select PreGrind, PostGrind, PreGrind-PostGrind as Removal from" & _ "   (Select top 1 id, thickness as Pregrind" & _                  "        FROM [SubstrateCharacterization].[dbo].[MicroSenseFormatted]" & _                  "        where id = '" & Wafer & "' and recipe not like @recipe" & _                  "        and recipe not like '%Post Slice' " & _                  "        order by msdatetime desc) as PreT" & _ " Full Join" & _ "   (Select top 1 id, thickness as PostGrind" & _                  "        FROM [SubstrateCharacterization].[dbo].[MicroSenseFormatted]" & _                  "        where id = '" & Wafer & "' and recipe  like @recipe" & _                  "        order by msdatetime desc) PostT" & _ "  on PreT.ID = PostT.ID" objMyCmd.CommandText = dbQuery objMyCmd.CommandType = adCmdText Set objMyRecordset.Source = objMyCmd objMyRecordset.Open form.Cells(ii, 2).CopyFromRecordset objMyRecordset objMyRecordset.Close ii = ii + 1

Wend End Sub

Sub AddDefaultValues ii = 1 While ii < 19 dfault = Data.Cells(ProcessRow, 26 + ii).Value firstdfault = dfault If Len(dfault) > 1 And (Left(dfault, 1) = "$" Or Left(dfault, 1) = "&") Then firstdfault = Split(Mid(dfault, 2, 50), ",")(0) ElseIf Left(dfault, 1) = "$" Or Left(dfault, 1) = "&" Then firstdfault = Mid(dfault, 2, 50) End If       If form.Cells(5, 1 + ii).Value Like "DEFAULT*" Then form.Cells(6, ii + 1).Formula = "=if(isblank(A6),"""",""" & firstdfault & """)" If dfault Like "$*" Then dfault = InputBox("Please SCAN or TYPE the appropriate " & form.Cells(4, ii + 1).Value, "Scan Value", firstdfault) form.Cells(6, ii + 1).Formula = "=if(isblank(A6),"""",""" & dfault & """)" ElseIf dfault Like "&*" Then form.Cells(6, ii + 1).Validation.Add Type:=xlValidateList, AlertStyle:=xlValidAlertInformation, Formula1:=(Mid(dfault, 2, 500)) ElseIf dfault Like "**" Then form.Cells(6, ii + 1).Formula = "=if(isblank(A6),""""," & dfault & ")" End If           form.Range(Cells(6, ii + 1), Cells(2000, ii + 1)).FillDown End If       ii = ii + 1 Wend End Sub

Sub AddHeadPositions

If form.Range("D5").Value Like "MACRO*HeadNum" Then HeadNo = InputBox("What head position held the first group of three wafers?") form.Range("D6").Value = HeadNo form.Range("D7").Value = HeadNo form.Range("D8").Value = HeadNo If form.Range("A9").Value <> "" Then HeadNo = InputBox("What head position held the second group of three wafers?") form.Range("D9").Value = HeadNo form.Range("D10").Value = HeadNo form.Range("D11").Value = HeadNo End If       If form.Range("A12").Value <> "" Then HeadNo = InputBox("What head position held the third group of three wafers?") form.Range("D12").Value = HeadNo form.Range("D13").Value = HeadNo form.Range("D14").Value = HeadNo End If   End If

End Sub

Sub VerifyProcessStep

Dim filepath As String Dim GaugeFile As Workbook Dim rng As Range Dim searchStr As String If form.Range("B3").Value Like "NFace_Mount_Gauge" Or _ form.Range("B3").Value Like "SF2_Al_Mount_Gauge" Then Exit Sub End If   Application.ScreenUpdating = True DoEvents ActiveWorkbook.RefreshAll DoEvents ActiveWorkbook.RefreshAll ' This block of comment is how to get the data from the SQL database to verify '   Set objMyCmd.ActiveConnection = objMyConn '   dbQuery = "SELECT top 9 ObjectID, value from [SubstrateCharacterization].[dbo].[ManualData] Where EventDate in " & _ '                   "(Select Top 1 EventDate from [SubstrateCharacterization].[dbo].[ManualData] " & _ '                    "Where process like '%Gauge' and parameter like 'ProcessStep' and value not like '" & _ '                    Form.Cells(6, 2).Value & "' and ObjectID like '" & Form.Cells(6, 1).Value & _ '                    "' order by EventDate desc) and parameter like 'ProcessStep' order by EventDate desc" ' '   objMyCmd.CommandText = dbQuery '   objMyCmd.CommandType = adCmdText '   Set objMyRecordset.Source = objMyCmd '   objMyRecordset.Open '   Form.Cells(6, 10).CopyFromRecordset objMyRecordset '   objMyRecordset.Close

'This block of text is how to get the data from the OFFLINE TABLE

If vbNullString <> Dir("C:\Users\Jmann\", vbDirectory) Then filepath = "C:\Users\Jmann\GaugeFile.xlsx" ElseIf vbNullString <> Dir("C:\Users\CIS_Subfab06\", vbDirectory) Then filepath = "C:\Users\CIS_Subfab06\GaugeFile.xlsx" End If

Workbooks.Open Filename:=filepath Set GaugeFile = ActiveWorkbook Set GaugeFileS = GaugeFile.ActiveSheet GaugeFileS.Range("F2").Formula = "=Concatenate(C2,B2)" GaugeFileS.Range("F2:F2000").FillDown GaugeFileS.Range("F2:F2000").Copy GaugeFileS.Range("F2").PasteSpecial Paste:=xlPasteValues searchStr = form.Range("A6").Value & Data.Cells(ProcessRow, 27).Value Set rng = GaugeFileS.Range("F:F").Find(searchStr) Application.DisplayAlerts = False GaugeFile.Close Application.DisplayAlerts = False If rng Is Nothing Then MsgBox ("There is no gauge data from " & Data.Cells(ProcessRow, 27).Value & _       "." & vbNewLine & vbNewLine & "No " & form.Range("B3").Value & _        " calculations can be made for these wafers " & _        "until " & Data.Cells(ProcessRow, 27).Value & " data is entered.") CloseFormPage End End If   Application.ScreenUpdating = False

End Sub