topic Re: iLogic and grabbing a value from excel matrix in Inventor Programming Forum

iLogic and grabbing a value from excel matrix

steveh3 — Tue, 11 Apr 2017 15:36:54 GMT

Gang...

I created a rule which takes values from Inventor model and looks up a matrix in a spreadsheet and it works.

But, if it can't find a row...it keeps the values that were already there.

Is there a way to have it default to a value ("Please contact Engineering for a Material Number") if it can't find a row to match?

I'm thinking I need some type of If Then statement.

Here's my rule and the spreadsheet....

SyntaxEditor Code Snippet

If Shape = "Rectangular Tube" Then
        i = GoExcel.FindRow("C:\ENGINEERING\ilogic\Shape_Generator_Raw_Material_Specs.xlsx", "Rectangular_tube", "Matl", "=", Rect_Tube_Matl, "Shape_Size", "=", Shape_Size)

        Matl_Description = GoExcel.CurrentRowValue("Matl_Description")
        M_Number = GoExcel.CurrentRowValue("M_Number")

        iProperties.Value("Custom", "(Mat'l Description)") = Matl_Description
        iProperties.Value("Custom", "(Reference M#)") = M_Number

        InventorVb.DocumentUpdate()

    Else If Shape = "Square Tube" Then
        i = GoExcel.FindRow("C:\ENGINEERING\ilogic\Shape_Generator_Raw_Material_Specs.xlsx", "Square_tube", "Matl", "=", Rect_Tube_Matl, "Shape_Size", "=", Shape_Size)

        Matl_Description = GoExcel.CurrentRowValue("Matl_Description")
        M_Number = GoExcel.CurrentRowValue("M_Number")

        iProperties.Value("Custom", "(Mat'l Description)") = Matl_Description
        iProperties.Value("Custom", "(Reference M#)") = M_Number

        InventorVb.DocumentUpdate()



End If

Any help would be appreciated.

Thanks in advance,

Steve H.

Re: iLogic and grabbing a value from excel matrix

Owner2229 — Tue, 11 Apr 2017 16:05:14 GMT

Hey, are you familiar with Subs And Functions?

Basically, if you have any other code above or below the one you've posted, move it along with this update to the "Sub Main() ... End Sub":

Sub Main()
If Shape = "Rectangular Tube" Then
	Dim R As String = Update_Matl("Rectangular_tube")
	If R <> "OK" Then MsgBox(R)
ElseIf Shape = "Square Tube" Then
	Dim R As String = Update_Matl("Square_tube")
	If R <> "OK" Then MsgBox(R)
End If
End Sub

Function Update_Matl(SheetName As String) As String
	Dim er_Message As String = "Please contact Engineering for a Material Number."
	Dim i As Integer = GoExcel.FindRow("C:\ENGINEERING\ilogic\Shape_Generator_Raw_Material_Specs.xlsx", SheetName, "Matl", "=", Rect_Tube_Matl, "Shape_Size", "=", Shape_Size)
	If i = 0 Then Return er_Message

	Dim Matl_Description As String = GoExcel.CurrentRowValue("Matl_Description")
	Dim M_Number As String = GoExcel.CurrentRowValue("M_Number")
	If Matl_Description = vbNullString Then Return er_Message
	If M_Number = vbNullString Then Return er_Message

	iProperties.Value("Custom", "(Mat'l Description)") = Matl_Description
	iProperties.Value("Custom", "(Reference M#)") = M_Number
	InventorVb.DocumentUpdate()

	Return "OK"
End Function

Re: iLogic and grabbing a value from excel matrix

steveh3 — Tue, 11 Apr 2017 16:09:09 GMT

To answer your question simply...NO.

But, trying to learn.

I'll attempt to decipher and give it a whirl...

Thanks for the guidance/expertise!!!!!

Steve H.

Re: iLogic and grabbing a value from excel matrix

Owner2229 — Tue, 11 Apr 2017 16:20:09 GMT

You might also need to pass some other parameters to the function (like I did with the "SheetName").

It depends on where the variables come from.

Namely:

Rect_Tube_Matl
Shape_Size

If they're parameters, then it should be alright, but if they are variables from the rule, they'll need to be passed.

Let me know if you need furter assistence with this.

Re: iLogic and grabbing a value from excel matrix

Anonymous — Tue, 11 Apr 2017 16:40:49 GMT

Would a Try/Catch Block be helpful here? Just a thought.

Re: iLogic and grabbing a value from excel matrix

Owner2229 — Tue, 11 Apr 2017 16:57:12 GMT

The function that is looking for the row won't throw an error, it'll in fact return 0, so there's no error to catch.

You need to look at the returned value and discontinue the rule if it is 0, therefore:

If i = 0 Then Return er_Message

Re: iLogic and grabbing a value from excel matrix

Anonymous — Tue, 11 Apr 2017 17:01:44 GMT

Thank you for clarifying that. I am still somewhat new to programming outside of basic VBA stuff and I haven't quite grasped the concept of a Try/Catch block yet!

Re: iLogic and grabbing a value from excel matrix

steveh3 — Tue, 11 Apr 2017 19:04:10 GMT

Again...Thanks for the help so far....

Kinda following your logic...not sure on exactly what the syntax is doing (not a code writer, so really cloudy), but making headway.....

The "Rect_Tube_Matl" and "Shape_Size" are parameters as you suggested.

I added your code to my rule (it's the only code in it).

When the code runs and can't find a row that matches the two dimensions of the matrix, the pop up note does show up. And, when a row is found, the message does not. Which makes sense.

The only thing is that the "Matl_Description" and "M_Number" do not populate if the values are found like it does in my code.

I tried to add in an If / Then statement too but no luck.

If a value is found....populate the properties from the spreadsheet. If they aren't, I would like them both to say "Contact Engineering for Material".

Here's your code with my edits in bold.

SyntaxEditor Code Snippet

Sub Main()
If Shape = "Rectangular Tube" Then
    Dim R As String = Update_Matl("Rectangular_tube")
    If R <> "OK" Then MsgBox(R)
ElseIf Shape = "Square Tube" Then
    Dim R As String = Update_Matl("Square_tube")
    If R <> "OK" Then MsgBox(R)
End If
End Sub

Function Update_Matl(SheetName As String) As String
    Dim er_Message As String = "Please contact Engineering for a Material Number."
    Dim i As Integer = GoExcel.FindRow("C:\ENGINEERING\ilogic\Shape_Generator_Raw_Material_Specs.xlsx", SheetName, "Matl", "=", Rect_Tube_Matl, "Shape_Size", "=", Shape_Size)
    If i = 0 Then Return er_Message

    Dim Matl_Description As String = GoExcel.CurrentRowValue("Matl_Description")
    Dim M_Number As String = GoExcel.CurrentRowValue("M_Number")
    If Matl_Description = vbNullString Then
        Matl_Description = "Contact Engineering for Material"
        End If
    If M_Number = vbNullString Then
        M_Number = "Contact Engineering for Material"
        End If
    iProperties.Value("Custom", "(Mat'l Description)") = Matl_Description
    iProperties.Value("Custom", "(Reference M#)") = M_Number
    InventorVb.DocumentUpdate()

    Return "OK"
End Function

Thanks again,

Steve H.

Re: iLogic and grabbing a value from excel matrix

Owner2229 — Tue, 11 Apr 2017 19:48:48 GMT

Alright, try it now. You can comment the red highlighted parts out by using aposthrophe if you don't want the Messages to pop-up.

Sub Main()
If Shape = "Rectangular Tube" Then
    Dim R As String = Update_Matl("Rectangular_tube")
    If R <> "OK" Then MsgBox(R)
ElseIf Shape = "Square Tube" Then
    Dim R As String = Update_Matl("Square_tube")
    If R <> "OK" Then MsgBox(R)
End If
End Sub

Function Update_Matl(SheetName As String) As String
    Dim er_Message As String = "Please contact Engineering for a Material Number."
    Dim i As Integer = GoExcel.FindRow("C:\ENGINEERING\ilogic\Shape_Generator_Raw_Material_Specs.xlsx", SheetName, "Matl", "=", Rect_Tube_Matl, "Shape_Size", "=", Shape_Size)
    Dim oDescription As String = GoExcel.CurrentRowValue("Matl_Description")
    Dim oNumber As String = GoExcel.CurrentRowValue("M_Number")
    If oDescription = vbNullString Then oDescription = "Contact Engineering for Material"
    If oNumber = vbNullString Then oNumber = "Contact Engineering for Material"
      
    iProperties.Value("Custom", "(Mat'l Description)") = oDescription
    iProperties.Value("Custom", "(Reference M#)") = oNumber
    InventorVb.DocumentUpdate()
    If i = 0 Then Return er_Message
    Return "OK"
End Function

Re: iLogic and grabbing a value from excel matrix

richard.joseph.pollak — Thu, 13 Apr 2017 19:44:58 GMT

I wrote something similar that stores people's initials in an excel file to auto-populate iProperties that fill out our title blocks. I'll insert my solution into your iLogic. Hopefully it can work for you. I also recommend you try to write your code more succinctly. There's no point in repeating yourself when you don't have to. Take a look at how I was able to shorten your If/Else If conditional.

If Shape = "Rectangular Tube" Then
	i = GoExcel.FindRow("C:\ENGINEERING\ilogic\Shape_Generator_Raw_Material_Specs.xlsx", "Rectangular_tube", "Matl", "=", Rect_Tube_Matl, "Shape_Size", "=", Shape_Size)
Else If Shape = "Square Tube" Then
	i = GoExcel.FindRow("C:\ENGINEERING\ilogic\Shape_Generator_Raw_Material_Specs.xlsx", "Square_tube", "Matl", "=", Rect_Tube_Matl, "Shape_Size", "=", Shape_Size)
End If

Try
    Matl_Description = GoExcel.CellValue("C" & i) 'If the row does not exist in your excel file, this should fail and skip to the Catch portion
    M_Number = GoExcel.CellValue("D" & i)

    iProperties.Value("Custom", "(Mat'l Description)") = Matl_Description
    iProperties.Value("Custom", "(Reference M#)") = M_Number
Catch
    iProperties.Value("Custom", "(Mat'l Description)") = "Please contact Engineering for a Material Number"
    iProperties.Value("Custom", "(Reference M#)") = ""
End Try

InventorVb.DocumentUpdate()

Re: iLogic and grabbing a value from excel matrix

steveh3 — Thu, 13 Apr 2017 19:46:57 GMT

Thanks for everyone's help...I could never get Mike's code to work, but I played around with my original code and did get the results I was looking for. May not be the cleanest, but it works for me.

I think it was a combination of the code and the spreadsheet not grabbing values properly. From what I read, spreadsheets with empty rows don't play nicely...I ended up putting the default values in the top row. If a row could not be found, it defaults to the first row after the headers.

Anyway, here's what I ended up with ....spreadsheet is at end.

SyntaxEditor Code Snippet

If Shape = "Rectangular Tube" Then
        i = GoExcel.FindRow("C:\ENGINEERING\ilogic\Shape_Generator_Raw_Material_Specs.xlsx", "Rectangular_tube", "Matl", "=", Material, "Shape_Size", "=", Shape_Size)

        Matl_Description = GoExcel.CurrentRowValue("Matl_Description")
        M_Number = GoExcel.CurrentRowValue("M_Number")

'        If Matl_Description = vbNullString Then Matl_Description = "Contact Engineering for Material"
'        If M_Number = vbNullString Then M_Number = "Contact Engineering for Material"        

        iProperties.Value("Custom", "(Mat'l Description)") = Matl_Description
        iProperties.Value("Custom", "(Reference M#)") = M_Number
        
        InventorVb.DocumentUpdate()






    Else If Shape = "Square Tube" Then
        i = GoExcel.FindRow("C:\ENGINEERING\ilogic\Shape_Generator_Raw_Material_Specs.xlsx", "Square_tube", "Matl", "=", Material, "Shape_Size", "=", Shape_Size)

        Matl_Description = GoExcel.CurrentRowValue("Matl_Description")
        M_Number = GoExcel.CurrentRowValue("M_Number")

'        If Matl_Description = vbNullString Then Matl_Description = "Contact Engineering for Material"
'        If M_Number = vbNullString Then M_Number = "Contact Engineering for Material"

        iProperties.Value("Custom", "(Mat'l Description)") = Matl_Description
        iProperties.Value("Custom", "(Reference M#)") = M_Number

        InventorVb.DocumentUpdate()







    Else If Shape = "Plates" Then
        i = GoExcel.FindRow("C:\ENGINEERING\ilogic\Shape_Generator_Raw_Material_Specs.xlsx", "Plates", "Matl", "=", Material, "Shape_Size", "=", Shape_Size)

        Matl_Description = GoExcel.CurrentRowValue("Matl_Description")
        M_Number = GoExcel.CurrentRowValue("M_Number")

'        If Matl_Description = vbNullString Then Matl_Description = "Contact Engineering for Material"
'        If M_Number = vbNullString Then M_Number = "Contact Engineering for Material"

        iProperties.Value("Custom", "(Mat'l Description)") = Matl_Description
        iProperties.Value("Custom", "(Reference M#)") = M_Number

        InventorVb.DocumentUpdate()





    Else If Shape = "Flat Bar" Then
        i = GoExcel.FindRow("C:\ENGINEERING\ilogic\Shape_Generator_Raw_Material_Specs.xlsx", "Flat Bar", "Matl", "=", Material, "Shape_Size", "=", Shape_Size)

        Matl_Description = GoExcel.CurrentRowValue("Matl_Description")
        M_Number = GoExcel.CurrentRowValue("M_Number")

'        If Matl_Description = vbNullString Then Matl_Description = "Contact Engineering for Material"
'        If M_Number = vbNullString Then M_Number = "Contact Engineering for Material"

        iProperties.Value("Custom", "(Mat'l Description)") = Matl_Description
        iProperties.Value("Custom", "(Reference M#)") = M_Number

        InventorVb.DocumentUpdate()
        
        
        
        
        
    Else If Shape = "Mechanical Tube" Then
        i = GoExcel.FindRow("C:\ENGINEERING\ilogic\Shape_Generator_Raw_Material_Specs.xlsx", "Mechanical_tube", "Matl", "=", Material, "Shape_Size", "=", Shape_Size)

        Matl_Description = GoExcel.CurrentRowValue("Matl_Description")
        M_Number = GoExcel.CurrentRowValue("M_Number")

'        If Matl_Description = vbNullString Then Matl_Description = "Contact Engineering for Material"
'        If M_Number = vbNullString Then M_Number = "Contact Engineering for Material"

        iProperties.Value("Custom", "(Mat'l Description)") = Matl_Description
        iProperties.Value("Custom", "(Reference M#)") = M_Number

        InventorVb.DocumentUpdate()    

        

        
Else If Shape = "Round Bar" Then
        i = GoExcel.FindRow("C:\ENGINEERING\ilogic\Shape_Generator_Raw_Material_Specs.xlsx", "Round_bar", "Matl", "=", Material, "Shape_Size", "=", Shape_Size)

        Matl_Description = GoExcel.CurrentRowValue("Matl_Description")
        M_Number = GoExcel.CurrentRowValue("M_Number")

'        If Matl_Description = vbNullString Then Matl_Description = "Contact Engineering for Material"
'        If M_Number = vbNullString Then M_Number = "Contact Engineering for Material"

        iProperties.Value("Custom", "(Mat'l Description)") = Matl_Description
        iProperties.Value("Custom", "(Reference M#)") = M_Number

        InventorVb.DocumentUpdate()    
        
        
        
        
        
Else If Shape = "Hex" Then
        i = GoExcel.FindRow("C:\ENGINEERING\ilogic\Shape_Generator_Raw_Material_Specs.xlsx", "Hex_bar", "Matl", "=", Material, "Shape_Size", "=", Shape_Size)

        Matl_Description = GoExcel.CurrentRowValue("Matl_Description")
        M_Number = GoExcel.CurrentRowValue("M_Number")

'        If Matl_Description = vbNullString Then Matl_Description = "Contact Engineering for Material"
'        If M_Number = vbNullString Then M_Number = "Contact Engineering for Material"

        iProperties.Value("Custom", "(Mat'l Description)") = Matl_Description
        iProperties.Value("Custom", "(Reference M#)") = M_Number

        InventorVb.DocumentUpdate()        
                
        



Else If Shape = "Channel" Then
        i = GoExcel.FindRow("C:\ENGINEERING\ilogic\Shape_Generator_Raw_Material_Specs.xlsx", "Channel", "Matl", "=", Material, "Shape_Size", "=", Shape_Size)

        Matl_Description = GoExcel.CurrentRowValue("Matl_Description")
        M_Number = GoExcel.CurrentRowValue("M_Number")

'        If Matl_Description = vbNullString Then Matl_Description = "Contact Engineering for Material"
'        If M_Number = vbNullString Then M_Number = "Contact Engineering for Material"

        iProperties.Value("Custom", "(Mat'l Description)") = Matl_Description
        iProperties.Value("Custom", "(Reference M#)") = M_Number

        InventorVb.DocumentUpdate()    
        
        
        

Else If Shape = "Angle" Then
        i = GoExcel.FindRow("C:\ENGINEERING\ilogic\Shape_Generator_Raw_Material_Specs.xlsx", "Angle", "Matl", "=", Material, "Shape_Size", "=", Shape_Size)

        Matl_Description = GoExcel.CurrentRowValue("Matl_Description")
        M_Number = GoExcel.CurrentRowValue("M_Number")

'        If Matl_Description = vbNullString Then Matl_Description = "Contact Engineering for Material"
'        If M_Number = vbNullString Then M_Number = "Contact Engineering for Material"

        iProperties.Value("Custom", "(Mat'l Description)") = Matl_Description
        iProperties.Value("Custom", "(Reference M#)") = M_Number

        InventorVb.DocumentUpdate()    
        




End If

Re: iLogic and grabbing a value from excel matrix

steveh3 — Thu, 13 Apr 2017 19:49:46 GMT

Thanks Richard...was putting up my conclusion right when your reply popped up. I will take a look and see if I can't clean up my code. Appreciate the insight.

Re: iLogic and grabbing a value from excel matrix

Dopey1993 — Tue, 29 Aug 2017 14:01:32 GMT

Can I have a Inventor and excel sample file of this?

Thanks in advance

Re: iLogic and grabbing a value from excel matrix

MechMachineMan — Tue, 29 Aug 2017 14:15:32 GMT

Dopey, the code is shown and the excel file is attached, what more do you need?

Re: iLogic and grabbing a value from excel matrix

Dopey1993 — Tue, 29 Aug 2017 14:21:48 GMT

I drew up a generic part and downloaded the excel file and copied the rule, replaced the file path with mine and it didn't do anything.

I am some what of a newbie so it might be a problem on my end.

Re: iLogic and grabbing a value from excel matrix

MechMachineMan — Tue, 29 Aug 2017 15:44:23 GMT

In your generic part, did you put a parameter called Shape, which is what the rule requires to make it's selection.

Much like the debugging outlined in the link in my signature, you can use messageboxes to figure out which lines are and aren't executing and then tackle the issue from there.

Re: iLogic and grabbing a value from excel matrix

Dopey1993 — Tue, 29 Aug 2017 16:23:55 GMT

Here is what I have.

Re: iLogic and grabbing a value from excel matrix

MechMachineMan — Tue, 29 Aug 2017 18:09:41 GMT

Dopey, you are missing parameters in your part file (Material & Shape_Size)

There are definitely better ways of doing this though; as you can have the parameters used to model the part also used to look up the part size in the spreadsheet, but that's another issue.

Also, I cleaned up the code to make it much, much simpler and easier to read, maintain, and expand upon.

Sub Main()
	Dim oSourceFile As String
	oSourceFile = "C:\Users\JRK\Desktop\Shape_Generator_Raw_Material_Specs.xlsx"
	
	Select Case Shape
		Case "Rectangular Tube"
			oSheetName = "Rectangular_tube"
		Case "Square Tube"
			oSheetName = "Square_tube"
		Case "Mechanical Tube"
			oSheetName = "Mechanical_tube"
		Case "Round Bar"
			oSheetName = "Round_bar"
		Case "Hex"
			oSheetName = "Hex_bar"
		Case Else
			oSheetName = oShape
	End Select
	
	i = GoExcel.FindRow(oSourceFile, oSheetName, "Matl", "=", Material, "Shape_Size", "=", Shape_Size)
			
	Matl_Description = GoExcel.CurrentRowValue("Matl_Description")
	M_Number = GoExcel.CurrentRowValue("M_Number")
	
	'If Matl_Description = vbNullString Then Matl_Description = "Contact Engineering for Material"     
	
	iProperties.Value("Custom", "(Mat'l Description)") = Matl_Description
	iProperties.Value("Custom", "(Reference M#)") = M_Number
			
	InventorVb.DocumentUpdate()

End Sub