Read Excel File - Find value between numbers and apply part number

[brad.jackson]

I am hoping that someone can tell me if there is an operator I can use when reading an Excel file (attached) to apply the appropriate Stock number to the iProperties of a part.

I would be looking at three columns in the attached Excel file {Color, Width, and Max Length}.

Color, Width, and Length would be set parameters within the part.

Max Length in the Excel file would be compared to the length parameter in the part.

The problem I am having is that when I search the Max Length I would want a value of 1543 returned for

{White, 4, 180}.  I can see where I would probably get that value if I used the <= 207.75 operator.  However, I could also see how that operator could possibly return any Stock number value for any other length that is less than 207.75.

I would like to make sure that the max length found is between 159.8125 and 207.75.  Is there an operator that tells iLogic to look between those two numbers.

[tyler.warner]

You can use the 'And' keyword. What are you searching with & what are you trying to find? You can do something like the examples below depending on whether you are using parameters, iProperties or Excel table data.

Here is some info for the Excel commands (link1 & link2).

'oExcelFileName = "\temp\Slider Parts Data.xlsx"
'oExcelApp = CreateObject("Excel.Application")
'oExcelWorkbook = oExcelApp.Workbooks.Open(oExcelFileName)
'oExcelSheet = oExcelWorkbook.Worksheets(1).Activate

If MaxLength > 159.75 And MaxLength <= 207.75
MessageBox.Show("Do Something")
'iProperties.Value("Project", "Stock Number") = oExcelApp.Range("B20").Value
End If

[WCrihfield · Accepted solution]

Hi @brad.jackson.  I do not know what your current code may look like, or how you may have it structured, but since you mentioned iLogic, and are somewhat unfamiliar with some of the basic operators, I assume you are working on an iLogic rule, and are attempting to use the GoExcel functionality that iLogic has available.  So, just to add to what Tyler suggested (which I agree with), here is a full working example of an iLogic rule, using GoExcel functionality, to search for the row matching your specifications, and returns the data from that row in a message, as well as leaving you with each piece of data in that row stored to variables that you could use however you may want to, instead of simply showing a message.  I tested this code on your Excel document, after downloading it to my C:\Temp\ folder, and it worked just as planned.

oFile = "C:\Temp\Slider Parts Data.xlsx"
oSheet = "HDPE Data"
GoExcel.Open(oFile, oSheet)
GoExcel.DisplayAlerts = False
Dim oRow As Integer = 2
Dim oVendorCol As String = "A"
Dim oItemNumCol As String = "B"
Dim oProdNameCol As String = "C"
Dim oColorCol As String = "D"
Dim oWidthCol As String = "E"
Dim oMaxLengthCol As String = "F"
Dim oVendor, oItemNumber, oProductName, oColor As String
Dim oWidth, oMaxLength As Double
For oRow = 2 To 35
	oColor = GoExcel.CellValue(oColorCol & oRow)
	oWidth = GoExcel.CellValue(oWidthCol & oRow)
	oMaxLength = GoExcel.CellValue(oMaxLengthCol & oRow)
	If oColor = "White" And oWidth = 4 And oMaxLength > 159.75 And oMaxLength <= 207.75 Then
		oVendor = GoExcel.CellValue(oVendorCol & oRow)
		oItemNumber = GoExcel.CellValue(oItemNumCol & oRow)
		oProductName = GoExcel.CellValue(oProdNameCol & oRow)
		Exit For
	End If
Next
If oVendor = "" Then 'a matching row was never found
	MsgBox("A row in the Excel sheet meeting input value requirements was not found.", vbCritical, "")
Else
	MsgBox("A match was found:  " & _
	vbCrLf & "Vendor = " & oVendor & _
	vbCrLf & "Item Number = " & oItemNumber & _
	vbCrLf & "Product Name = " & oProductName & _
	vbCrLf & "Color = " & oColor & _
	vbCrLf & "Width = " & oWidth & _
	vbCrLf & "Max Length = " & oMaxLength, vbInformation, "")
End If
'GoExcel.Close
'GoExcel.QuitApplication

If this solved your problem, or answered your question, please click ACCEPT SOLUTION .
Or, if this helped you, please click (LIKE or KUDOS) 👍.

[brad.jackson]

@WCrihfield 

Thanks for the example.  I believe this will help in the directions I was hoping to take.

[brad.jackson]

@WCrihfield 

Question:  Can the same syntax (And arguments) be used with a Case statement?

	If oColor = "White" And oWidth = 4 And oMaxLength > 159.75 And oMaxLength <= 207.75 Then

[WCrihfield]

Hi @brad.jackson.  I do not think that would be a good fit in this exact scenario, because the opening line of a Case statement is 'Select Case ?????', where the ????? part needs to be a single argument that each Case is a different value for.  Maybe if we were collecting a whole row of data at once, and storing it in something like a Object() (an Array of Object) or List(Of Object) (I'm using Object, because some values are String, and some are Double), then we might be able to have that Array or List as the single 'argument' that we check different values of in each Case.  You actually get much better performance (speed) when collecting all or most data from Excel in one or fewer read calls, verses collecting smaller bits of data in many read calls, so this code could definitely be improved upon, as far as performance is concerned, if that was an issue.  However, when using the GoExcel functionality, opening the Excel document once at the beginning, then using the 'current' versions of those snippets (the version where you do not include file name & sheet name in each line/call) is better performing than the alternative/equivalent 'not current' lines.  Also, within a Case block of code, on each 'Case' line, you can simply use commas (,) between many possible values, instead of having to use the 'And' operator, which is really nice in some situations.