how can i get iproperty values filled in by reading an excel file

[andrew.nao]

I have a few part files that i keep is a library. most times the iproperties of these part files change in some fashion. I would like to have the iproperties of these parts be filled in by reading an excel file so I only have to change the excel file to update these properties instead of going into each part file and update them. Can anyone point me in the right direction on how to achieve this? thanks in advanced

[kennyj]

Hello @andrew.nao,

You make your excel sheet, then in Parameters, link the Excel file.  They will show up as Parameters.  Then in the far right column is a check box for "export".  When you check this box, it will add the parameter to the Custom iProperties.

Hope this helps,

Kenny

[andrew.nao]

thanks but im looking to read from an excel and fill in the properties on the existing tabs, like the summary and projects tabs. I guess i should have been a bit more clear on what I was after. thanks for the reply

[MechMachineMan]

iLogic or an addin.

[johnsonshiue]

Hi Andrew,

Are you familiar with iLogic? I think you can use iLogic rule to read in an Excel spreadsheet and then populate the value to individual iProperty. Please try it and let us know if more information is needed.

Many thanks!

[andrew.nao]

yes I did some digging around and found a post someone shared with ilogic and I got it working. but now i need to find out how to make it update automatically. right now i have to manually remove the iproperties and rerun the ilogic rule in order to populate it. know of any links with tutorials that i can learn more?

[jtylerbc · Accepted solution]

---

@andrew.nao wrote:
yes I did some digging around and found a post someone shared with ilogic and I got it working. but now i need to find out how to make it update automatically. right now i have to manually remove the iproperties and rerun the ilogic rule in order to populate it. know of any links with tutorials that i can learn more?

---

There are triggers you can set so an iLogic rule will run in response to a certain event (before or after Save, when a parameter changes, etc.).  If you don't have any triggers set for your rule, you might want to start there.

If you did set triggers, then it may be something in the code.

[andrew.nao]

yes, i found those too but its not updating the iproperties unless i delete whats there and let it re-populate. I will have to rethink my ilogic code thanks

[johnsonshiue]

Hi Andrew,

The behavior you describe here does not seem to match what I am seeing on my machine. I don't have to delete the existing iProperties value. I believe something is not working right with the rule. If you have not figured it out, feel free to post the rule here and some experts can help take a look.

Many thanks!

[andrew.nao]

attached is my dummy test file along with my test excel file. here is my ilogic code. if i manually delete the info in the part number and description fields in the iproperties. and save the file or run the rule. it updates. if there is info already existing in the part number or description filed and i change the info in the excel file and i save the file or rerun the rule. the information is not automatically updated. am I missing something or am i not doing this correctly? '---------start of iLogic code--------- StartRule: 'read excel file GoExcel.Open("C:\Users\amilorey\OneDrive - Airline Hydraulics Corporation\Backup\Documents\INVENTOR BLOCKS\test.xls", "Sheet1") 'index row 2 through 10000 For rowPN = 2 To 10000 'find first empty cell in column A If (GoExcel.CellValue("A" & rowPN) = "") Then 'create a variable for the cell value that is one row less than 'the empty cell row lastPN = GoExcel.CellValue("A" & rowPN - 1) lastDesc = GoExcel.CellValue("B" & rowPN- 1) Exit For End If Next 'check to see if Part Number is the same as the file name or blank If iProperties.Value("Project", "Part Number") = ThisDoc.FileName(False) _ Or iProperties.Value("Project", "Part Number") = "" Then 'set iProperty to value read in from excel iProperties.Value("Project", "Part Number") = lastPN 'set iProperty to value read in from excel iProperties.Value("Project", "Description") = lastDesc Else End If 'show results and ask user to confirm results question = MessageBox.Show("PN: " & lastPN & vbLf _ &"Description: " & lastDesc & vbLf _ & "Is this correct?", "iLogic from Excel", MessageBoxButtons.YesNo ) 'if answer is no If question = vbNo Then 'clear these iProperties iProperties.Value("Project", "Part Number") = "" iProperties.Value("Project", "Description") = "" 'run rule again Goto StartRule 'if answer is yes exit rule Else End If ThisDoc.Document.Rebuild() InventorVb.DocumentUpdate()

[johnsonshiue]

Hi Andrew,

I am not a developer. Please bear with me if I misunderstood the issue. I took a look at the iLogic rule. I think there is a logical fault. Toward the end of the rule, the code ask the user if PN and Desc are correct. The way the question is frame is confusing. You are asking the user if the PN and Desc from the Excel table are correct. You did not present the current values from the part. As a result, the existing values are never overwritten. I modified your code a bit and it seems to work now.

CurrentPN=iProperties.Value("Project", "Part Number")
CurrentDesc=iProperties.Value("Project", "Description")
'show results and ask user to confirm results
question = MessageBox.Show("PN: " & CurrentPN & vbLf _
&"Description: " & CurrentDesc & vbLf _
& "Is this correct?", "iLogic from Excel", MessageBoxButtons.YesNo )

Basically, after checking if PN is the same as Filename or PN is empty, I get the current PN and current Desc and then present the value to the users. If they are correct, the rule stops. If they are incorrect, run the rule again.

Does it make sense?

Many thanks!

[andrew.nao]

the pop up asking if the info is correct is just an alert to me to see if its working. that will eventually be removed after it works how im trying to get it to work. i want the rule to read the excel cells and fill in the appropriate fields when the files is saved. if for whatever reason the cell info changed, i want the properties to update to whatever is in the excel cells when the file is saved. i will test out your adjustment over the weekend. i appreciate your help

[mslosar]

This should be in the customization group, but.... 

Code should be along the line of: 

SyntaxEditor Code Snippet

GoExcel.DisplayAlerts = False

DESC = GoExcel.CellValue("filename.xls", "Sheet1", "A2")

iProperties.Value("Project", "Description") = DESC

InventorVb.DocumentUpdate()

GoExcel.Close

Ilogic goes to the excel file, grabs the field you are after then assigns it to the desired iproperty value.

Only remaining this is to set triggers.

Personally, i tend to set them to After Open, Before Close, and Parameter Changes. 

Note, with this, if you do this multiple times, check your task manager for orphaned excel.exe's from time to time. Even if all statements have the close excel line, they still tend to orphan. This is the only way to get anything with text to Inventor - at least through 2017. Only numerical values can be sent to Inventor from Excel, but from within excel you can grab anything from an excel file. I really wish they'd allow the exporting for Text fields from Excel to Inventor....

[ankurGCK9S]

---

@jtylerbc wrote:

---

@andrew.nao wrote:
yes I did some digging around and found a post someone shared with ilogic and I got it working. but now i need to find out how to make it update automatically. right now i have to manually remove the iproperties and rerun the ilogic rule in order to populate it. know of any links with tutorials that i can learn more?

---

There are triggers you can set so an iLogic rule will run in response to a certain event (before or after Save, when a parameter changes, etc.).  If you don't have any triggers set for your rule, you might want to start there.

If you did set triggers, then it may be something in the code.

---

Hi ,

Did you able to find the final solution for what you have describe ? Is it possible for you to share the codes . We are using ERP data base and it will be great tool to import/export  data between inventor and ERP system.

[Anonymous]

I searched the interwebs for this as I'm using forms and an excel sheet to update costs and prices. The company is moving into using an ERP system so a combination of creating forms to collect data and report data and using excel sheets as a data source made sense.

I was able to link the Estimated Cost (iproperties) on a Form that I created; in this case "Estimated ($)":

The only issue remaining is to update the iproperty value Estimated Cost with an excel sheet.

To link a variable from my excel sheet and return a dollar value under the iproperty value Estimated Cost I use the following ilogic code:

iProperties.Value("Project", "Estimated Cost") = GoExcel.CellValue("\\SRV-DOC\location of an excel file on the server\name_of_excel_file.xlsx", "sheet name", "cell containing price")
iLogicVb.UpdateWhenDone = True

The above example takes a dollar value (variable) from my excel sheet and updates the iproperties "Estimated Cost" returning it with a dollar value.

The dollar value under the iproperties "Estimated Cost" then updates the form Estimated ($) price and any report that my Form generates.

THANK YOU interwebs!