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