Ilogic Excel Look up

[AMN3161]

Hello,

I am looking a code to look at a excel table and find a value like a tag, then pull the information next to that tag in the adjustment cell. I also working to learn ilogic so some explanation on what the code is doing would be appreciated. So if the column has PSV-101 in A2 i want the information in A3 to get pulled into a preset iproperty. 

thank you in advance

[Anonymous · Accepted solution]

Hi @AMN3161,

To read a value in excel file and assign it to parameters, you can start the code below and adjust it according to your needs.

ExcelFile = "C:\folder\ExcelfileName" & iProperties.Value("Custom", "iLogicRulename") & ".xlsx"

d0 = GoExcel.CellValue(ExcelFile, "Sheet1", "A1")
d1 = GoExcel.CellValue("A2")
d2 = GoExcel.CellValue("A3")

[AMN3161]

This is great thank you so much!

[AMN3161]

so i was a little premature with my thanks yous.

I should of explained this a little better but i want it to search for a value in a row, the pull the adjacent value in the column next to that value.

[dleesuk]

Hi Adam,

I'm not sure this will help, but I created an iLogic routine some time ago which searched an Excel spreadsheet to populate the parameters to build an hydraulic cylinder.  The parameters were used to modify 2 existing parts to the sizes required and also drove parameters in the assembly.

The data I used is from the APH Cylinders data sheet. The data runs in columns as opposed to rows as you ask.

I have attached a snippet of my data set for you to compare with the code.

Hopefully, by going through the code and comparing it to the data the code will make some sense and allow you to build your own routine.

Dim ExcelFile = "THIS WOULD BE THE LOCATION OF YOUR DATA SET\*****.xlsx"

i = GoExcel.FindRow(ExcelFile, "Sheet1", "Series", "=", Parameter("CylinderSeries"))

'Gather APH CYLINDER parameters from Excel spreadsheet and assign them to local variables
Rod  = GoExcel.CurrentRowValue("Rod")
D3  = GoExcel.CurrentRowValue("D3")
D4  = GoExcel.CurrentRowValue("D4")
D6  = GoExcel.CurrentRowValue("D6")
D7  = GoExcel.CurrentRowValue("D7")
L1  = GoExcel.CurrentRowValue("L1")
L2  = GoExcel.CurrentRowValue("L2")
L3  = GoExcel.CurrentRowValue("L3")
L4  = GoExcel.CurrentRowValue("L4")
L5  = GoExcel.CurrentRowValue("L5")
L6  = GoExcel.CurrentRowValue("L6")
L7  = GoExcel.CurrentRowValue("L7")
L8  = GoExcel.CurrentRowValue("L8")
L9  = GoExcel.CurrentRowValue("L9")
L10 = GoExcel.CurrentRowValue("L10")
L11 = GoExcel.CurrentRowValue("L11")
L12 = GoExcel.CurrentRowValue("L12")
L13 = GoExcel.CurrentRowValue("L13")
L14 = GoExcel.CurrentRowValue("L14")
Stroke = Parameter("Stroke") 'Local parameter

' Cylinder Body Details
Parameter("Body.ipt", "CylBodyDia") 		= D3
Parameter("Body.ipt", "EyeWidth") 			= L11
Parameter("Body.ipt", "EyeThickness") 		= L9
Parameter("Body.ipt", "SwivelEyePos") 		= L12
Parameter("Body.ipt", "SwivelEyeDiameter") 	= D7
Parameter("Body.ipt", "OverBall") 			= L10
Parameter("Body.ipt", "SwivelHoleDia")	 	= D4
Parameter("Body.ipt", "PortPos1") 			= L7
Parameter("Body.ipt", "PortPitch") 			= L3
Parameter("Body.ipt", "PortHeight") 		= L8
Parameter("Body.ipt", "CylStroke") 			= Stroke

Parameter("Body.ipt", "BodyLength") = ((L14 + Stroke) - L6 - (2 * L12))

If Parameter("CylinderSeries") < 48
	Feature.IsActive("Body.ipt", "Port Pattern") = False
Else
	Feature.IsActive("Body.ipt", "Port Pattern") = True
End If

Parameter("Body.ipt", "BodyColour") = Parameter("CylColour")

' Cylinder Rod Details
Parameter("Rod.ipt", "RodDia") = Rod
Parameter("Rod.ipt", "RodLength") = Stroke + 50
Parameter("Rod.ipt", "EyeWidth") = L11
Parameter("Rod.ipt", "EyeThickness") = L9
Parameter("Rod.ipt", "SwivelEyePos") = L12
Parameter("Rod.ipt", "SwivelEyeDiameter") = D7
Parameter("Rod.ipt", "OverBall") = L10
Parameter("Rod.ipt", "SwivelHoleDia") = D4

Parameter("MinCentre") = Stroke + L14
Parameter("MaxCentre") = (Stroke*2) + L14

Parameter("Rod.ipt", "RodDia") = Rod

Parameter("Rod.ipt", "RodColour") = Parameter("CylColour")

InventorVb.DocumentUpdate()
iLogicVb.UpdateWhenDone = True

GoExcel.Close

[AMN3161]

I just test this its exactly what I need, thank you!!!!

[dleesuk]

Hi Adam,

Could you please accept this as a solution.

Thank you