10-24-2022
06:46 AM
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report
10-24-2022
06:46 AM
I was able to rework this code to get it to work the way I wanted it to by looking for stocked items first if no stocked items were found it would search again for non stocked items.
''Sheet Metal Stock Number
'Checks to see if the file is sheet metal. If not it will return.
If oSubType = "Sheet Metal" Then
'Pulls flat pattern extents
Dim oLength As Double
Dim oWidth As Double
ex_Length = SheetMetal.FlatExtentsLength
ex_Width = SheetMetal.FlatExtentsWidth
'Checks flat pattern to see what length is longer and defines the longer side to be length
If ex_Length > ex_Width Then
oLength = ex_Length
oWidth = ex_Width
Else
oWidth = ex_Length
oLength = ex_Width
End If
'Define Excel File to Lookup
ExcelFile = "C:\CA Workspace\CAD Standards\CA Library A\iLogic Automation\CA - Sheetmetal Part Number Selection - Table.xlsx"
'Ignores any excel alerts
GoExcel.DisplayAlerts = False
'Message box warning for non stocked items
NonStockedNote = "This Item is not Stocked. Check with Purchasing on Availablity!"
Dim SheetMetalStyle As String
SheetMetalStyle = SheetMetal.GetActiveStyle
SheetMaterial = iProperties.Material
'Checks the sheet if it is larger than 72 X 144 notifies user sheet is custom order
If (oWidth >72) AndAlso (oLength >144) Then
i = MessageBox.Show("Sheet is too large for Stocked material. Check with Purchasing if we can order custom sheet sizes", "Too Large of Sheet", MessageBoxButtons.OK, MessageBoxIcon.Hand, MessageBoxDefaultButton.Button1)
SheetPartnumber = "Check With Purchasing if we can order a sheet this size."
End If
''Setting Width depending on sheet size
If (oWidth <= 48) AndAlso (oWidth >0) Then
oSetWidth = 48
Else If (oWidth <= 60) AndAlso (oWidth > 48.000001)
oSetWidth = 60
Else If (oWidth <=72) AndAlso (oWidth > 60.000001) Then
oSetWidth = 72
End If
''Setting Length depending on sheet size
If (oLength <= 120) AndAlso (oLength > 0) Then
oSetLength = 120
Else If (oLength <= 144) AndAlso (oLength > 120.000001)Then
oSetLength = 144
End If
'Used to verify it works on sheet size
'i = MessageBox.Show(oSetWidth & OSetLength, "Sheet Size", MessageBoxButtons.OK, MessageBoxIcon.None, MessageBoxDefaultButton.Button1)
Dim rowNum As Integer = GoExcel.FindRow(ExcelFile, "Stocked", "Sheet Metal Rule", "=", SheetMetalStyle, "Material", "=", iProperties.Material, "Sheet Width (Short Side)", ">=", oSetWidth, "Sheet Length (Long Side)", ">=", oSetLength, "Stocked", "=", "Yes")
'Checks if no option is available then displays message box to user
If (rowNum >0) Then
SheetPartnumber = GoExcel.CurrentRowValue("Part Number")
CostCode = GoExcel.CurrentRowValue("Cost Code")
question = MessageBox.Show("The Stock Number will be replaced with the "& SheetPartnumber &" are you sure you want to procceed?", "Warning Stock Number Change", MessageBoxButtons.YesNo, MessageBoxIcon.Question, MessageBoxDefaultButton.Button1)
If question = vbNo Then
Return
Else If question = vbYes Then
iProperties.Value("Project", "Stock Number") = SheetPartnumber
iProperties.Value("Project", "Cost Center") = CostCode
Return
End If
Else If (rowNum <0) Then
End If
Dim rowNum2 As Integer = GoExcel.FindRow(ExcelFile, "Stocked", "Sheet Metal Rule", "=", SheetMetalStyle, "Material", "=", iProperties.Material, "Sheet Width (Short Side)", ">=", oSetWidth, "Sheet Length (Long Side)", ">=", oSetLength, "Stocked", "=", "No")
If (rowNum2 >0)Then
SheetPartnumber = GoExcel.CurrentRowValue("Part Number")
CostCode = GoExcel.CurrentRowValue("Cost Code")
question = MessageBox.Show(NonStockedNote & "The Stock Number will be replaced with the "& SheetPartnumber &" are you sure you want to procceed?", "Non Stocked Sheet", MessageBoxButtons.YesNo, MessageBoxIcon.Question, MessageBoxDefaultButton.Button1)
If question = vbNo Then
Return
Else If question = vbYes Then
iProperties.Value("Project", "Stock Number") = SheetPartnumber
iProperties.Value("Project", "Cost Center") = CostCode
Return
End If
Else If (rowNum2 <0) Then
i = MessageBox.Show("No Sheet Size Available. Check Purchasing for Part Number.", "No Sheet Size Available", MessageBoxButtons.OK, MessageBoxIcon.Warning, MessageBoxDefaultButton.Button1)
End If
Else
Return
End If