i = GoExcel.FindRow

AMN3161 · ‎10-26-2020

I know this probably isn't the cleanest way to do this but i am very novice to ilogic and i at least understand what's going on

Dim partNumber As String = iProperties.Value("Project", "Part Number")
partNumber = partNumber.Trim().ToUpper()
i = GoExcel.FindRow("P:\_MASTER_PART_LIST\MASTER_PART_LIST.xls", "MASTER_PART_LIST", "Part No.", "=", partNumber)

If i <> -1

	iProperties.Value("Project", "Description") = GoExcel.CurrentRowValue("Description")
	iProperties.Value("Project", "Vendor") = GoExcel.CurrentRowValue("Manufacturer")
	
	Dim partDescription As String = iProperties.Value("Project", "Description")
	partDescription = partDescription.ToUpper()
	iProperties.Value("Project", "Description")= partDescription

	Dim partVendor As String = iProperties.Value("Project", "Vendor")
	partVendor = partVendor.ToUpper()
	iProperties.Value("Project", "Vendor")= partVendor
	
Else

iProperties.Value("Project", "Description") = "The entered Part Number does not exist in the Master Parts List"
iProperties.Value("Project", "Vendor") = "The entered Part Number does not exist in the Master Parts List"

End If

I am going to be adding more If statements for different scenarios, right now the else is there if the part number is not found in the excel look up. what is this function called " i <> -1"? didn't find much trying to google it. I am looking to hopefully use the "i" instead of a else statement because i am going to have a few if statements that would include if the look up cant find the part number.

This is my work in progress

  Dim oDoc As Document
oDoc = ThisDoc.Document
'define list of custom properties to delete 
Dim MyArrayList As New ArrayList 
MyArrayList.Add("Part_Number_1") 
MyArrayList.Add("Part_Number_2") 
MyArrayList.Add("Part_Number_3") 
MyArrayList.Add("Part_Number_4") 
MyArrayList.Add("Part_Number_5") 

'define custom property collection 
oCustomPropertySet = oDoc.PropertySets.Item("Inventor User Defined Properties") 


'[ Adds custom properties
For Each oItem In MyArrayList
	oFlag = False 'set flag to default value
	'look at each property in the collection 
	For Each oCustProp In oCustomPropertySet
		'check property name against the list item
		If oItem = oCustProp.Name Then 
			oFlag = True 'trip the flag
		End If 
	Next
	
	If oFlag = False Then
		'Create new iProperty
		iProperties.Value(oDoc, "Custom", oItem ) = ""
	End If
Next

']

'[ Erases custom properties

For Each oCustProp In oCustomPropertySet
	oFlag = False 'set flag to default value
	For Each oItem In MyArrayList
		'check property name against the list item
		If oCustProp.Name = oItem Then 
			oFlag = True 'trip the flag
		End If 
	Next
	
	If oFlag = False Then
		'delete property
		oCustProp.Delete
	End If
Next

']



'[ If 1-5 are Blank and a Part Number is Found
If iProperties.Value("Custom", "Part_Number_1") = "" And iProperties.Value("Custom", "Part_Number_2") = "" And iProperties.Value("Custom", "Part_Number_3") = "" And iProperties.Value("Custom", "Part_Number_4") = "" And iProperties.Value("Custom", "Part_Number_5") = "" And i <> -1
	
	Dim partNumber As String = iProperties.Value("Project", "Part Number")
	partNumber = partNumber.Trim().ToUpper()
	i = GoExcel.FindRow("P:\_MASTER_PART_LIST\MASTER_PART_LIST.xls", "MASTER_PART_LIST", "Part No.", "=", partNumber)

	iProperties.Value("Project", "Description") = GoExcel.CurrentRowValue("Description")
	iProperties.Value("Project", "Vendor") = GoExcel.CurrentRowValue("Manufacturer")
	
	Dim partDescription As String = iProperties.Value("Project", "Description")
	partDescription = partDescription.ToUpper()
	iProperties.Value("Project", "Description")= partDescription

	Dim partVendor As String = iProperties.Value("Project", "Vendor")
	partVendor = partVendor.ToUpper()
	iProperties.Value("Project", "Vendor") = partVendor
']


'[ If 2-5 are Blank and a Part Number is Found
Else If iProperties.Value("Custom", "Part_Number_2") = "" And iProperties.Value("Custom", "Part_Number_3") = "" And iProperties.Value("Custom", "Part_Number_4") = "" And iProperties.Value("Custom", "Part_Number_5") = "" And i <> -1
	
	Dim partNumber1 As String = iProperties.Value("Custom", "Part_Number_1")
	partNumber1 = partNumber1.Trim().ToUpper()
	i = GoExcel.FindRow("P:\_MASTER_PART_LIST\MASTER_PART_LIST.xls", "MASTER_PART_LIST", "Part No.", "=", partNumber1)
	
	iProperties.Value("Project", "Description") = GoExcel.CurrentRowValue("Description")
	iProperties.Value("Project", "Vendor") = GoExcel.CurrentRowValue("Manufacturer")
	
	Dim partDescription1 As String = iProperties.Value("Project", "Description")
	partDescription1 = partDescription1.ToUpper()
	iProperties.Value("Project", "Description")= partDescription1

	Dim partVendor1 As String = iProperties.Value("Project", "Vendor")
	partVendor1 = partVendor1.ToUpper()
	iProperties.Value("Project", "Vendor") = partVendor1
	
	iProperties.Value("Project", "Part Number") = partNumber1
']

I would want that if Part Number 3-5 are blank them take Part number 1 and 2 then combine them into one line to replace the Part Number in the properties. Then take those two part numbers and combine the descriptions and manufacturers into their own lines respectively. So I don't think I can just use a else statement if the look up cant find the part number in the excel but what if it cant find Part Number 1 or Part Number 2? It would require two else statements which I am not able to do.

I understand how to combine the two the text into a line, its just the different scenarios where a part number is not found

Like what if I have two if statements where Part number 1 is found but Part Number 2 isn't and vice versa, so a else statement would work because 2 of those would be separate If statements and a all encompassing Else statement wouldn't work

Any recommendations of what I can do here, and I hope it explained it properly.

WCrihfield · ‎10-26-2020

- One thing you could do is create multiple simple boolean type variables before the search, with each one named specifically for one of the things you are going to be searching for. During search, if item is found, set its boolean to True. After search, check values of those boolean variables, to determine which of many actions you want to take as a result.

- Also the Try...Catch...End Try block of code can become more dynamic than the simple examples you usually see. It can have multiple Catch statements, and can also have a Finally statement. Perhaps you could make use of this behavior.

- Or just create more levels to your If...Then statements, to include more checks. For example, if it finds one of multiple target items, you could use a Then, then on the next line, start another If...Then layer that searches for the second target item (could even use another loop if needed) below that If...Then, to dig deeper.

Wesley Crihfield

(Not an Autodesk Employee)

AMN3161 · ‎10-26-2020

cant delete a reply so i just edited, let me try that

AMN3161 · ‎10-26-2020

How would i do the boolean portion of your reply?

I saw how to implment the try portion of your reply which works and does its job but i am struggling with the catch

Dim partNumber As String = iProperties.Value("Project", "Part Number")
partNumber = partNumber.Trim().ToUpper()
i = GoExcel.FindRow("P:\_MASTER_PART_LIST\MASTER_PART_LIST.xls", "MASTER_PART_LIST", "Part No.", "=", partNumber)

If i <> -1
	
	Try
	
	iProperties.Value("Project", "Description") = GoExcel.CurrentRowValue("Description")
	iProperties.Value("Project", "Vendor") = GoExcel.CurrentRowValue("Manufacturer")
	
	Dim partDescription As String = iProperties.Value("Project", "Description")
	partDescription = partDescription.ToUpper()
	iProperties.Value("Project", "Description")= partDescription

	Dim partVendor As String = iProperties.Value("Project", "Vendor")
	partVendor = partVendor.ToUpper()
	iProperties.Value("Project", "Vendor") = partVendor
	
	Exit Try
	
Catch 
	
	iProperties.Value("Project", "Description") = "The entered Part Number does not exist in the Master Parts List"
	iProperties.Value("Project", "Vendor") = "The entered Part Number does not exist in the Master Parts List"

End Try

End If

WCrihfield · ‎10-26-2020

I may have misunderstood which code you were talking about when I suggested the multiple booleans strategy. I was thinking about alternatives to the much longer code within your first post.

Here's the Try...Catch version of that last (shorter) code you posted.

Dim partNumber As String = iProperties.Value("Project", "Part Number")
partNumber = partNumber.Trim().ToUpper()
i = GoExcel.FindRow("P:\_MASTER_PART_LIST\MASTER_PART_LIST.xls", "MASTER_PART_LIST", "Part No.", "=", partNumber)
If i <> -1
	'So now we know it found the value of "partNumber" in the Excel file,
	'but we (I) technically still don't know if the cells within the "Description" and/or "Manufacturer" columns of the found row contain anything
	'and this is why we're (I'm) still cautious
	'If both cells will always be filled in, you can delete the Try...Catch blocks and delete the alternative actions (just leave the code used in the Try portion) 
	Dim oDesc As String
	Try
		'if it does find a value, we've just defined it as a String
		oDesc = GoExcel.CurrentRowValue("Description")
		'If that worked, this should also work without any errors
		iProperties.Value("Project", "Description") = oDesc.ToUpper
	Catch 'what to do if the Try portion fails (doesn't find a value in that cell)
		iProperties.Value("Project", "Description") = "The entered Part Number does not exist in the Master Parts List"
	End Try
	
	Dim oVendor As String
	Try
		oVendor = GoExcel.CurrentRowValue("Manufacturer")
		iProperties.Value("Project", "Vendor") = oVendor.ToUpper
	Catch
		iProperties.Value("Project", "Vendor") = "The entered Part Number does not exist in the Master Parts List"
	End Try
Else
End If

And here is the purely If...Then...ElseIf...Else...End If version of that code.

Dim partNumber As String
If iProperties.Value("Project", "Part Number") <> vbNullString Then
	partNumber = iProperties.Value("Project", "Part Number")
	partNumber = partNumber.Trim().ToUpper()
Else
	MsgBox("The 'Part Number' property is blank. Exiting.", vbOKOnly, " ")
	Exit Sub
End If

i = GoExcel.FindRow("P:\_MASTER_PART_LIST\MASTER_PART_LIST.xls", "MASTER_PART_LIST", "Part No.", "=", partNumber)

If i = -1 Then
	MsgBox("That Part Number wasn't found within the Excel file. Exiting.", vbOKOnly, " ")
	Exit Sub
Else
	Dim oDesc As String
	If GoExcel.CurrentRowValue("Description") IsNot Nothing Then
		oDesc = GoExcel.CurrentRowValue("Description")
		iProperties.Value("Project", "Description") = oDesc.ToUpper
	Else 'it was Nothing (no value found in that cell)
		iProperties.Value("Project", "Description") = "The entered Part Number does not exist in the Master Parts List"
	End If
	
	Dim oVendor As String
	If GoExcel.CurrentRowValue("Manufacturer") IsNot Nothing Then
		oVendor = GoExcel.CurrentRowValue("Manufacturer")
		iProperties.Value("Project", "Vendor") = oVendor.ToUpper
	Else
		iProperties.Value("Project", "Vendor") = "The entered Part Number does not exist in the Master Parts List"
	End If
End If

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

Wesley Crihfield

(Not an Autodesk Employee)

AMN3161 · ‎10-26-2020

this is extremely helpful thank you!

AMN3161 · ‎10-26-2020

I had to tweak it because some poor communication on my part but this is how it looks

Dim oDoc As Document
oDoc = ThisDoc.Document

Dim MyArrayList As New ArrayList 
MyArrayList.Add("Part_Number_1") 
MyArrayList.Add("Part_Number_2") 
MyArrayList.Add("Part_Number_3") 
MyArrayList.Add("Part_Number_4") 
MyArrayList.Add("Part_Number_5") 

oCustomPropertySet = oDoc.PropertySets.Item("Inventor User Defined Properties") 


'[ Adds custom properties
For Each oItem In MyArrayList
	oFlag = False 'set flag to default value
	'look at each property in the collection 
	For Each oCustProp In oCustomPropertySet
		'check property name against the list item
		If oItem = oCustProp.Name Then 
			oFlag = True 'trip the flag
		End If 
	Next
	
	If oFlag = False Then
		'Create new iProperty
		iProperties.Value(oDoc, "Custom", oItem ) = ""
	End If
Next

']

'[ Erases custom properties

For Each oCustProp In oCustomPropertySet
	oFlag = False 'set flag to default value
	For Each oItem In MyArrayList
		'check property name against the list item
		If oCustProp.Name = oItem Then 
			oFlag = True 'trip the flag
		End If 
	Next
	
	If oFlag = False Then
		'delete property
		oCustProp.Delete
	End If
Next

']

'[ Part Number and 1-5 Blank
Dim partNumber As String
	
	partNumber = iProperties.Value("Project", "Part Number")
	partNumber = partNumber.Trim().ToUpper()

i = GoExcel.FindRow("P:\_MASTER_PART_LIST\MASTER_PART_LIST.xls", "MASTER_PART_LIST", "Part No.", "=", partNumber)

If i = -1 And iProperties.Value("Custom", "Part_Number_1") = "" And iProperties.Value("Custom", "Part_Number_2") = "" And iProperties.Value("Custom", "Part_Number_3") = "" And iProperties.Value("Custom", "Part_Number_4") = "" And iProperties.Value("Custom", "Part_Number_5") = ""  Then
	iProperties.Value("Project", "Vendor") = "The entered Part Number does not exist in the Master Parts List"
	iProperties.Value("Project", "Description") = "The entered Part Number does not exist in the Master Parts List"
	Exit Sub
Else
	Dim oDesc As String
	If GoExcel.CurrentRowValue("Description") IsNot Nothing Then
		oDesc = GoExcel.CurrentRowValue("Description")
		iProperties.Value("Project", "Description") = oDesc.ToUpper
	End If
	
	Dim oVendor As String
	If GoExcel.CurrentRowValue("Manufacturer") IsNot Nothing Then
		oVendor = GoExcel.CurrentRowValue("Manufacturer")
		iProperties.Value("Project", "Vendor") = oVendor.ToUpper
	End If
End If
']


'[ 2-5 Blank
Dim partNumber1 As String
	
	partNumber1 = iProperties.Value("Custom", "Part_Number_1")
	partNumber1 = partNumber1.Trim().ToUpper()

i = GoExcel.FindRow("P:\_MASTER_PART_LIST\MASTER_PART_LIST.xls", "MASTER_PART_LIST", "Part No.", "=", partNumber1)

If i = -1 And iProperties.Value("Custom", "Part_Number_2") = "" And iProperties.Value("Custom", "Part_Number_3") = "" And iProperties.Value("Custom", "Part_Number_4") = "" And iProperties.Value("Custom", "Part_Number_5") = ""  Then
	iProperties.Value("Project", "Vendor") = "The entered Part Number does not exist in the Master Parts List"
	iProperties.Value("Project", "Description") = "The entered Part Number does not exist in the Master Parts List"
	Exit Sub
Else
	Dim oDesc1 As String
	If GoExcel.CurrentRowValue("Description") IsNot Nothing Then
		oDesc1 = GoExcel.CurrentRowValue("Description")
		iProperties.Value("Project", "Description") = oDesc1.ToUpper
		iProperties.Value("Project", "Part Number") = iProperties.Value("Custom", "Part_Number_1")
	End If
	
	Dim oVendor1 As String
	If GoExcel.CurrentRowValue("Manufacturer") IsNot Nothing Then
		oVendor1 = GoExcel.CurrentRowValue("Manufacturer")
		iProperties.Value("Project", "Vendor") = oVendor1.ToUpper
	End If
End If

']

But your code gave me the idea of what i wanted done, thank you!

Community

i = GoExcel.FindRow

i = GoExcel.FindRow

i = GoExcel.FindRow