Announcements
Attention for Customers without Multi-Factor Authentication or Single Sign-On - OTP Verification rolls out April 2025. Read all about it here.
KWarrenCA
333 Views, 2 Replies

iLogic to select stock number from excel file

I'm working on an iLogic to look at the sheet metal flat pattern dimensions and select the correct part number. Right now I have it working correctly if the size is in between 2 numbers. The issue I'm running into is if say a 48 x 120 sheet is not in stock or unavailable but a 60 x 120 is in stock to select the larger sheet stock number.

I attached the excel file and the code below.

 

I also only want this to run on a sheet metal part. How would I go about doing that.

''Sheet Metal Stock Number
'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
	
'IF the sheet is larger than 72 X 144 notifys 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)
	oSetLength = 120
Else If (oLength <= 144) AndAlso (oLength > 120.000001)
	oSetLength = 144
End If

'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)
'If no option is available then message box
If (rowNum >0) Then

		If GoExcel.CurrentRowValue("Stocked") = "Yes" Then
			SheetPartnumber = GoExcel.CurrentRowValue("Part Number")
 			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
					Else If question = vbYes Then
				iProperties.Value("Project", "Stock Number") = SheetPartnumber
				Return
			End If
		Else If GoExcel.CurrentRowValue("Stocked") = "No" Then
			SheetPartnumber = GoExcel.CurrentRowValue("Part Number")
			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
					Else If question = vbYes Then
				iProperties.Value("Project", "Stock Number") = SheetPartnumber
				Return
			End If
		End If
Else If (rowNum <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