iLogic Code to emulate VLOOKUP & MATCH

[Anonymous]

Searched everywhere here for the right solution & found similar answers but not quite what I need.

I have created an iLogic .IPT for a Wall Mounted HVAC.  The .IPT is linked to an Excel spreadsheet for the parameters (HVAC_W, HVAC_D, etc... as shown below) & I have created a table of values in Excel based on each HVAC model number.  I already have created an iLogic Rule that determines the correct HVAC Model Number based on the selection of 3 criteria from my iLogic Form.  So far, I think I have that covered....

Here's a portion of my table of dimensions from my spreadsheet:

What I'm trying to achieve is to write the correct iLogic Rule that would emulate the Excel Functions of VLOOKUP & MATCH.   The code would LOOKUP the HVAC Model Number column ("HVAC_Model_No" parameter in my .IPT) from the spreadsheet then MATCH the corresponding values of the parameters (HVAC_W, HVAC_D, etc...) then apply these values to my .IPT parameters & change the physical dimensions of my part.

I've already tested this (with success) as an iLogic assembly with iParts for each HVAC model number, then, using iLogic to suppress components, however, the code gets lengthy & the assembly gets heavy.  I'd prefer to have a smaller part file to use, as our steel building models get large enough already.  That's why I'm trying to go the .IPT direction using iLogic & a spreadsheet to control one part, rather than a large assembly & suppressing components.

I'm sure there's an easy solution to this for those of you more verse in iLogic than I.

What iLogic snippets or code would steer me in the right direction?

I've attached my .IPT & Spreadsheet, as well, so you can see where I'm going with this.

Thanks in advance for your help!

Cheers,

Bruce

[ADSKDJW4]

Hello @Anonymous,

For the ILogic questions we have the Inventor Customization forum for these. The ILogic is considered custom programming and the support for this is handled in that forum. I will move the post there in hope that someone can assist further.

Best Regards,

Don

[Jon.Balgley · Accepted solution]

Hi –

So close!!!

A few changes and I think you’ll get what you want:

1. You should use the iLogic “GoExcel.FindRow” and “GoExcel.CurrentRowValue” functions. But to do that, you’ll have to transpose the rows and columns of your data file.   To transpose rows and columns in Excel, select the whole range, use Ctrl-C to copy, find a clean space to paste it into (new sheet, for example), and use the “Paste” menu in the ribbon to select Paste/Transpose.

2. If you only have these two dozen or so parameters, you can just have two dozen or so calls to GoExcel.CurrentRowValue in your “Read_Dims_From_Table” rule. Alternatively, you can figure out a loop to get them all from the column-headers.

3. It sounds like you're trying to automate or optimize a phase in your business process. Maybe it’s a pre-sales process, i.e., automatically generate correct models/drawings when customers or distributors call in with their requirements.  Perhaps you should investigate Configurator 360?   (aka "C360" ... see configurator360.autodesk.com).  But C360 doesn’t support “GoExcel” functions, you’ll have to use some substitute functions instead (we can provide those), and save your data in a simple TXT file.  I can help you with that, if you’re interested.  I will be on vacation Thursday and Friday, so contact the C360 team at configurator360.support@autodesk.com or on the C360 forum, or I can help you next week.  Of course, feel free to ask more questions about the iLogic stuff, even if you're not interested in C360.

4. Watch out for typo in cells A3:A4. “HAVC”  

[Anonymous · Accepted solution]

As said previously, to use GoExcel you will need to move your model numbers into the column and inventor parameters into row 1.

Once that's done try out this piece of code. I use it regularly and it works a treat.

GoExcel.TitleRow = 1 'Row number
i = GoExcel.FindRow("enter your own filepath\HVAC Dims Table 01.xlsx", "Sheet1", "MODEL #", "=", HVAC_Model_No)
'If model number match is found, if no match is found i = -1
If i <> -1 Then
HVAC_W =  GoExcel.CurrentRowValue("HVAC_W")
'Repeat this line with all your other inventor parameters

End If

[tristanmallinson]

Hi there

This code works for me:

First locate your row number:

GoExcel.FindRow("HVAC Dims Table 01.xls" ,"Sheet1","MODEL #","HVAC_C")

This will return the row in your spreadsheet that matches your search parameter ie 7.

Note - if your Excel sheet is not in your current Project folder then prefix file name with the full directory path.

Tip - before you continue insert a MessageBox line to check this part is working!

Collect the info you need with the function and set a parameter

MyParameter = GoExcel.CurrentRowValue("W24LA-A")

Here is a sample from my working code that may help to show it in action...

If Handing = "Right" Then
x1 ="Hinge Post"
x2 = "Lock Post"
Else
x2 ="Hinge Post"
x1 = "Lock Post"
End If

Parameter("Right Frame", "Handing") = Handing
Parameter("Left Frame", "Handing") = Handing
Parameter("Outer Leaf", "Handing") = Handing

GoExcel.FindRow("Astute Live Configurator V5.11.xlsm", "Single Doors", "Frame size", "=", Width)

Parameter("Right Frame", "Right_Post_Width") = GoExcel.CurrentRowValue(x1)
Parameter("Left Frame", "Left_Post_Width") = GoExcel.CurrentRowValue(x2)
Parameter("Master Frame Skeleton", "Right_Offset") = GoExcel.CurrentRowValue(x1)
Parameter("Master Frame Skeleton", "Left_Offset") = GoExcel.CurrentRowValue(x2)

 I'm new to this too - hope this is of some use!

[Anonymous]

Works great!  Thank you for the code guidance.

Here's what I had to do to make it work:

1.  I had to SAVEAS my original to a new .IPT, as well, SAVEAS a new copy of my spreadheet.  For simplicty, I saved them as "02" at the end of the filenames.

2.  Open the "02" versions of both the .IPT & Spreadsheet.

3.  In the .IPT, I deleted the 3rd Party reference Excel link & deleted the Fx folder containing the now broken link reference.  This retained my parameters (ie: HVAC_W, etc...) & changed them to User Parameters.

4.  In the spreadsheet, I deleted the original table of values & deleted the original "Inventor parameters" I had linked to my .IPT & only kept the transposed table of values.

5.  I then added your sample code you provided & edited "i" for "W" for parameter "HVAC_W", then repeated & edited similar code for each of the other parameters ("D" for "HVAC_D" If Then statements, etc...)

Here's a portion of the final iLogic for my "Read_Dims_From_Table" Rule:

GoExcel.TitleRow = 1 'Row number
W = GoExcel.FindRow("C:\VaultWork\Vault\UEE\Templates\Modular Buildings\HVAC, Wall Mounted\HVAC Dims Table 02.xlsx", "Sheet1", "MODEL #", "=", HVAC_Model_No)
If W <> -1 Then
HVAC_W =  GoExcel.CurrentRowValue("HVAC_W")
End If

GoExcel.TitleRow = 1 'Row number
D = GoExcel.FindRow("C:\VaultWork\Vault\UEE\Templates\Modular Buildings\HVAC, Wall Mounted\HVAC Dims Table 02.xlsx", "Sheet1", "MODEL #", "=", HVAC_Model_No)
If D <> -1 Then
HVAC_D =  GoExcel.CurrentRowValue("HVAC_D")
End If

GoExcel.TitleRow = 1 'Row number
H = GoExcel.FindRow("C:\VaultWork\Vault\UEE\Templates\Modular Buildings\HVAC, Wall Mounted\HVAC Dims Table 02.xlsx", "Sheet1", "MODEL #", "=", HVAC_Model_No)
If H <> -1 Then
HVAC_H =  GoExcel.CurrentRowValue("HVAC_H")
End If

Here's two screenshots of the iLogic form while making selections & then the .IPT updating it's dimensions instantly:

The final working versions of both the .IPT with iLogic form & spreadsheet are attahced below as well.  Hopefully this may help someone else in the future.

Brilliant!  Thank you all for your help!

Cheers,

Bruce

[Anonymous]

Thank you, Jon!  You certainly helped me.  And thank you for picking up on my spelling error in "HAVC" vs "HVAC".  You know, I've looked at these parameters for a few weeks now & didn't even notice that at all.  Great catch!  Thank you.

Cheers,

Bruce