Community
Inventor Programming - iLogic, Macros, AddIns & Apprentice
Inventor iLogic, Macros, AddIns & Apprentice Forum. Share your knowledge, ask questions, and explore popular Inventor topics related to programming, creating add-ins, macros, working with the API or creating iLogic tools.
cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 

Part , Description,excel and ilogic

17 REPLIES 17
SOLVED
Reply
Message 1 of 18
GosponZ
4116 Views, 17 Replies

Part , Description,excel and ilogic

Thanks to Curtis who guide me to this Discussion Groups over my question. We make assemblies from solid bodies. Every part must have material and material description. We created custom iproperties for those two,and we would like to have ilogic rule with drop list of our library(samle excel library file att) which has over 3000 material codes and is growing every day. On the end this information should be filled in inventor bom becouse we exporting bom csv file for use in other program . If this can be done with ilogic it will save a tone of time. I'm typing info for each part in iproperties and it is taking a long  to complete job.

I 'm apreciate help

17 REPLIES 17
Message 2 of 18
Curtis_Waguespack
in reply to: GosponZ

 

Hi MisterZS,

 

I'm not aware of a way to add a list to the BOM editor, but you might be able to do it using a custom form. I won't have time to look at this any time soon, but here are some snippets that might help someone else if they get a chance to help.

 

These snippets get the list of materials from Excel, reading from row 2 to the last used row (assuming the list isn't longer than 10000 rows).

 

I hope this helps.
Best of luck to you in all of your Inventor pursuits,
Curtis
http://inventortrenches.blogspot.com

 

'get list from XL using an arraylist

'define the XLS to look at
myXLS = "U:\iLogic examples\XLS lookup\Mat Library.xls"
GoExcel.Open(myXLS)

'index row 2 through 10000
For rowNumber = 2 To 10000
'find first empty cell in column A
 If (GoExcel.CellValue("A" & rowNumber) = "") Then
'  If (GoExcel.CellValue(myXLS,"Sheet1", "A" & rowNumber) = "") Then
'create a variable for the row that is one row less than the first empty row found
'this assumes the first empty row is the end of the list
lastUsedRow = rowNumber - 1
Exit For
End If
Next

'set the list to be read from the XLS file
Dim myArrayList As New ArrayList
i = 2

'add the values from the Excel file to an array list
Do until i = lastUsedRow +1 
myArrayList.add(GoExcel.CellValue("B" & i)) 
i = i + 1
Loop

'present the list to the user
myMatl= InputListBox("Select from the list", myArrayList, myArrayList.Item(0), "iLogic", "Available Materials")

'set the iProperty
iProperties.Value("Project", "Description") = myMatl

'present the result
MessageBox.Show("The Description is: " & iProperties.Value("Project", "Description"), "iLogic")

 

OR

 

'get list from XL using an existing multi value parameter list

'define the XLS to look at myXLS = "U:\iLogic examples\XLS lookup\Mat Library.xls" GoExcel.Open(myXLS) 'index row 2 through 10000 For rowNumber = 2 To 10000 'find first empty cell in column A If (GoExcel.CellValue("A" & rowNumber) = "") Then 'create a variable for the row that is one row less than the first empty row found 'this assumes the first empty row is the end of the list lastUsedRow = rowNumber - 1 Exit For End If Next 'add the values from the Excel file to the list MultiValue.List("Material_List") = GoExcel.CellValues(myXLS, "Sheet1", "B2", "B" & lastUsedRow) 'present the list to the user myMatl= InputListBox("Select from the list", MultiValue.List("Material_List"), MultiValue.List("Material_List").Item(0), "iLogic", "Available Materials") 'set the iProperty iProperties.Value("Project", "Description") = myMatl 'present the result MessageBox.Show("The Description is: " & iProperties.Value("Project", "Description"), "iLogic")

 

 

Message 3 of 18

Hi,

 

As I know, there is no API for "BOM column customization" like UI does. Curtis’s solution is nice to me, i.e. customize with Excel table. You can also customize the Excel table directly, e.g. add a column and read the relevant information from the PropertySet of the corresponding BOM componenet.

 

In addition, another thread may be of a bit help:

 

http://forums.autodesk.com/t5/Autodesk-Inventor-Customization/Add-custom-iProperty-to-BOM-using-API/...

Message 4 of 18
GosponZ
in reply to: GosponZ

Thank you for your help. Link you gave doesn't work. Code from Curtis is nice and is working but i'm afraid that is not what i'm looking for. After i make components my parts need to receive saome text informaton. That text need to be in iproperties which i map to Bom in Inventor. That is becouse i'm saving bom file as csv(comma delimited) for use in another program.. With text parameter with multi value option would work perfect, but how to do that.  I made bom columns in way how we need and bom is done, except i need  to fill those 2 columns with text which is most time every time different. Any idea how to export  text parameter or something similar .

Message 5 of 18
GosponZ
in reply to: GosponZ

I realy need help in this. If you look first code written by  Curtis. It is working very well for me, but i need upgrade.

This code read column A. What i need is that whatever i choose from column A to fill out what is in column b in same row. For  example if i choose A 55 i need to fill iproperties what is in B55.

A column is code and B column is description.

Thank you for help and support

Message 6 of 18
xiaodong_liang
in reply to: GosponZ

oops, the link is truncted. It should be

 

http://forums.autodesk.com/t5/Autodesk-Inventor-Customization/Add-custom-iProperty-to-BOM-using-API/...

 

Hope it helps a bit.

Message 7 of 18
GosponZ
in reply to: GosponZ

Here is code that i was worked on it folowing code from Curtis. I do have 2 parameters in iproperties material code and material description. Runing this rule it fillout my 2iproperties.

I would like  just to pick code and description to be filled automaticly. Any idea?

In Excel ther is only two columns. Code and material description.

Thanks for any idea comments sugestions

 

'get list from XL using an arraylist

'define the XLS to look at
myXLS="K:\ExcelMaterial.xls"
GoExcel.Open(myXLS)

'index row 2 through 10000
ForrowNumber=2To10000
'find first empty cell in column A
If(GoExcel.CellValue("A"&rowNumber)=(""))Then
' If (GoExcel.CellValue(myXLS,"Sheet1", "A" & rowNumber) = "") Then
'
create a variable for the row that is one row less than the first empty row found
'
this assumes the first empty row is the end of the list
lastUsedRow=rowNumber-1



EndIf

If(GoExcel.CellValue("B"&rowNumber)=(""))Then
' If (GoExcel.CellValue(myXLS,"Sheet1", "B" & rowNumber) = "") Then
'
create a variable for the row that is one row less than the first empty row found
'
this assumes the first empty row is the end of the list
lastUsedRow=rowNumber-1

ExitFor
EndIf
Next

'set the list to be read from the XLS file
DimmyArrayListAsNewArrayList
i=2

'add the values from the Excel file to an array list
DoUntili=lastUsedRow+1
myArrayList.add(GoExcel.CellValue("A"&i))
myArrayList.add(GoExcel.CellValue("B"&i))
i=i+1
Loop



'present the list to the user
myMatl=InputListBox("Select from the list", myArrayList, myArrayList.Item(0), "iLogic", "Available Codes")
myMat2=InputListBox("Select from the list", myArrayList, myArrayList.Item(0), "iLogic", "Available Materials")
'set the iProperty
iProperties.Value("Custom", "PartMat")=myMatl
iProperties.Value("Custom", "MATERIAL DESCRIPTION")=myMat2
'present the result
MessageBox.Show("The Code is: "&iProperties.Value("Custom", "PartMat"), "iLogic")
MessageBox.Show("The description is: "&iProperties.Value("Custom", "MATERIAL DESCRIPTION"), "iLogic")
Message 8 of 18
GosponZ
in reply to: GosponZ

Any idea?

Message 9 of 18
xiaodong_liang
in reply to: GosponZ

Hi,

 

it looks to me the code has achieved what you need. Its workflow is:

 

1) get the values of each column of the Excel (part material and material description)

2) fill in the dropdown box of the dilaogs with the values of  each column

3) ask the user to select what he wants to use

4) set the corresponding iProperties with the values the user selects.

 

So, It is not clear to me what is your problem right now. Could you elaborate?

 

 

Message 10 of 18
GosponZ
in reply to: GosponZ

Well,

thanks for the comment. Here is what i would like. If i pick part in for example in A1 to fill automaticly what is in B1

or what is in A10 to be auto fill in B10. List is long and it take time to find what i need but code like this is working just fine. But my question is can we work to find even better?

Thanks to all for comments, ideas sugestions.

Message 11 of 18
xiaodong_liang
in reply to: GosponZ

so, do you mean you pick a cell (A1) in Excell, the code gets the information of the part in cell B1, and fill A1 with the info? if yes, this is pure a question of Excel. Or I am misunderstanding?

Message 12 of 18
GosponZ
in reply to: xiaodong_liang

On my click to run rule i got from iLogic to choose one of many available codes. On Ok it guide me to available materials and i choose my material.Code is alfa numeric and it is in column A in Excel, and material is just text description in Column B. Every row got own code and description. So now when i click Ok iLogic is giving me message The code is ..,and one more time Ckick Ok and i got msg The description is....That is working like i said last time just fine. No errors .But what if i click on desired code to get code in iproperties for code and   description for that code to be filled without extra looking for material on very huge list. So with one shot to kill two flies. 

Message 13 of 18
xiaodong_liang
in reply to: GosponZ

Well, if I understand correctly, you have a list of code and a list of description. Each code corresponds to each description. They are in the same row of Excel. You firstly get the list of code, input them in the InputListBox (let us say ListBox1), next get the list of description, input them in another InputListBox (let us say ListBox2)


Now, you select one item of the ListBox1, next, you have to find the corresponding description in ListBox2. While the list of material is large, it is not easy to find the correct item.

 

If this is your problem, I would suggest you add a numeric prefix for each item in the two ListBox2. The number is the row sequence e.g.

 

ListBox1:
01- code1
02- code2
03- code3


ListBox2:
01 – description1
02 – description2
03 – description3

 

By this mean, if the user selects an item in ListBox1, he can locate the item in ListBox2 easier.

 

 

Message 14 of 18

Hello Curtis,

 

The code what you have posted is amazing, it saved a lot of time for me.

My requirement was to select part number from excel sheet, I had modified this code to read only Column A and mapping the same to  Part Number.

Thanks a lot.

 

Mahesh
Message 15 of 18
rossano_praderi
in reply to: GosponZ

Hi MisteZS,

the code written by Curtis is a very good solution, but I would like to post my code as alternative solution.

To work properly the last cell value of the column "A" within your excel file have to be "LAST" (see attached file).

 

 

'get list from XL using an arraylist
'define the XLS to look at
myXLS = ThisDoc.WorkspacePath() & "\Mat Library.xls"
LastUsedRow = GoExcel.FindRow(myXLS, "Sheet1", "PartMat", "=", "LAST") - 1

myArrayList = GoExcel.CellValues(myXLS, "Sheet1", "B2", "B" & LastUsedRow)

'present the list to the user
myMatDescription = InputListBox("Select from the list", myArrayList, myArrayList.Item(0), "iLogic", "Available Materials")

'set the iProperty
MatCodeRow = GoExcel.FindRow(myXLS, "Sheet1", "Material Description", "=", myMatDescription)

If MatCodeRow < 0 Then MessageBox.Show("Material code not found!" & vbCr & "Iproperties not updated!", "iLogic ERROR!")

iProperties.Value("Custom", "PartMat") = GoExcel.CellValue(myXLS, "Sheet1", "A" & MatCodeRow)
iProperties.Value("Custom", "MATERIAL DESCRIPTION") = myMatDescription
''present the result
result = "The Code is: " & iProperties.Value("Custom", "PartMat") & vbCr & _
		"The description is: " & iProperties.Value("Custom", "MATERIAL DESCRIPTION")
MessageBox.Show(Result, "iLogic")

 

Let me know if this is can be your solution.

 

Bregs

Rossano Praderi



--------------------------------------
If my post answers your question, please click the "Accept as Solution"
button. This helps everyone find answers more quickly!
---------------
Message 16 of 18

This is a correction of my code, you can't have empty value on the column "PartMat" before the "LAST" row.

 

'get list from XL using an arraylist
'define the XLS to look at

myXLS = ThisDoc.WorkspacePath() & "\Mat Library.xls"
LastUsedRow = GoExcel.FindRow(myXLS, "Sheet1", "PartMat", "=", "LAST") - 1
If LastUsedRow < 0 Then
	MessageBox.Show("The column PartMat can't have empty cells before the LAST row!", "iLogic ERROR!")
	Goto fine
End If
myArrayList = GoExcel.CellValues(myXLS, "Sheet1", "B2", "B" & LastUsedRow)

'present the list to the user
myMatDescription = InputListBox("Select from the list", myArrayList, myArrayList.Item(0), "iLogic", "Available Materials")

'set the iProperty
MatCodeRow = GoExcel.FindRow(myXLS, "Sheet1", "Material Description", "=", myMatDescription)
MatCode = GoExcel.CellValue(myXLS, "Sheet1", "A" & MatCodeRow)

If MatCode = "" Then 
	MessageBox.Show("Material code not found!" & vbCr & "Iproperties not updated!", "iLogic ERROR!")
	Goto fine
End If

iProperties.Value("Custom", "PartMat") = MatCode
iProperties.Value("Custom", "MATERIAL DESCRIPTION") = myMatDescription
''present the result
result = "The Code is: " & iProperties.Value("Custom", "PartMat") & vbCr & _
		"The description is: " & iProperties.Value("Custom", "MATERIAL DESCRIPTION")
MessageBox.Show(Result, "iLogic")
fine:

 

Bregs

Rossano Praderi



--------------------------------------
If my post answers your question, please click the "Accept as Solution"
button. This helps everyone find answers more quickly!
---------------
Message 17 of 18
GosponZ
in reply to: rossano_praderi

Hi Rossano,

thanks for review old post. I like your code. I did test working just fine. It is working for me.

Message 18 of 18
mikmosTZG95
in reply to: GosponZ

I have been playing with these scripts but have an issue.

I got the excel list to show in the array (Description)but I want to populate two iproperties of a part file,

Part Number & Description. I want the description to be shown in the selection form and when selected the part number on that row automatically gets added to the iproperties.

I deeply appreciate any feedback as I am trying to justify an iLogic course to my employers.

 

Regards,

 

Can't find what you're looking for? Ask the community or share your knowledge.

Post to forums  

Autodesk Design & Make Report