How to read the iProperties of a part file and write them to an excel spreadsheet

[joe_hancockRLPXP]

I was wondering if it's possible to write VBA code to read the iProperties of a part file and write them to a new excel spreadsheet. Furthermore if it would also be possible to edit a cell on that same spreadsheet and have it edit the iProperties of the part file.

Thanks in advance

[WCrihfield · Accepted solution]

Hi @joe_hancockRLPXP.  The short answer to your first question is yes...to a certain degree.  Are you talking about every iProperty in every iProperty set, or just specific ones?  Some iProperties have values that can not be written to a cell in an Excel worksheet.  And some have values that, even if it did write its value there, the data would be mostly unrecognizable.  There are several data Types involved.  Most are just something like String, Double, Integer, Boolean, & Date, but there are a couple that hole an 'IPictureDisp', which is not a normal image file type that you can simply look at in any normal image viewer.

On the other half of your question, many (maybe even most) iProperties are ReadOnly, meaning that you can get their values, but can not change their names or their values.  And there is no way to 'Link' an Excel spreadsheet to the iProperties of an Inventor document.  And there likely would not be a way for the Inventor document to know if its properties do not match the Excel file, if the Excel file gets changed.  There would most likely have to be a manual step involved after making edits to the Excel file, that would initiate the comparison process.  Code could be created that would find the Excel file, read its contents, and attempt to ensure that the iProperties of a specific Inventor document match the values in the Excel file, if everything was perfectly formatted for the data types involved on the Excel side, and the code included lots of error checking.

Projects like that are usually somewhat complex, and highly custom to a single user's needs though.  Plus, I would advise you to stick to iLogic for something like that, instead of VBA, because VBA has been a big enough security risk the past few years that Autodesk has stopped including it in Inventor installations. (Link about that here.)

[joe_hancockRLPXP]

Thanks for the response. Let me read up on iProperties some more and go from there.

[op_thorsager]

idk if it's possible, but you can set iproperties through an excel sheet, so i guess you could write to the sheet aswell if you just flip the order.

I have this iproperty value which is updated accordingly to a cell within a spreadsheet

iProperties.Value("Custom", "ItemNmbr") = GoExcel.CellValue(xSpec, "Driver","H14") 

 If you flip this so it looks like this:

GoExcel.CellValue(xSpec, "Driver","H14") =iProperties.Value("Custom", "ItemNmbr")

then i guess it could work. However you need to specify which excelsheet you want it to write to with GoExcel.open

then its just a matter of making a checkbox in a form which dictates if you want to write to spread sheet, or want to get cell information to iproperties. 

Hope this makes sense..

Code could look like this (this code works for me atleast)

Dim oFileDlg As Inventor.FileDialog
Dim oDocFile As Document, oDocFileName As String

'Run File Selection Code
InventorVb.Application.CreateFileDialog(oFileDlg)

'Allow Selection Of Excel Files
oFileDlg.Filter = "Excel Spec Sheet (*.xls;*.xlsx;*xlsm)|*.xls;*.xlsx;*xlsm"

'Get File Path, setting the initial path to the same folder that the model is saved In.
oFileDlg.InitialDirectory = ThisDoc.Path
oFileDlg.CancelError = True

'Show Open File Dialog Window.
oFileDlg.ShowOpen()

If oFileDlg.FileName = "" 
	MessageBox.Show("No File Selected", "Error")


End If

'declares selected file name
Dim xSpec As String = oFileDlg.FileName
'Set Filename parameter to the path.
Parameter("Filename") = xSpec
GoExcel.Open(xSpec, "SHEETNAME")
'Print write parameter dictating wether to write or get information from/to excel
if Parameter(read_write) = true then
iProperties.Value("Custom", "ItemNmbr") = GoExcel.CellValue(xSpec, "(SHEETNAME","H14") 

else if Parameter(read_write) = false
GoExcel.CellValue(xSpec, "(SHEETNAME","H14") = iProperties.Value("Custom", "ItemNmbr") 
end if

GoExcel.save
GoExcel.close

I have not tested the use of this to write from iProperties to Excel, but i'd think it works the same way, as when you write from excel to iProperties, the code above where i get a cell value and write to a custom iProperty value works in my current project as it is right now. 

[WCrihfield]

Hi @op_thorsager.  Yes, you are correct about us being able to read data from Excel, or write data to Excel seemingly fairy easily, using some of the common iLogic shortcut snippets.  However, there is a lot to keep in mind about that process, and the tools used to do it.  For instance, if the Excel sheet has a lot of cell formatting, such as setting specific cells, rows, or columns to a specific expected Type of data, like a numerical value with a certain number places after the decimal, and comma separators, or a date which is to be formatted a very specific way, then you attempt to write a String type data to that cell from an Inventor iProperty, that may throw an error.  Or the other way around...if you get a value from Excel, and that cell is formatted as Text, and you attempt to write that value to an iProperty on Inventor's side that is expecting a numerical value, such as Double or Integer, that may also throw an error.  It is usually wise to collect values from Excel to a variable first, then determine whether it received anything at all, then either determine its data type, or convert the data's Type to the appropriate Type for the iProperty you will be writing it to.  Or the other way around...collecting a value from an iProperty, ensuring that something was retrieved, then (if necessary) converting that value to a certain Type of data before attempting to write it to a specifically formatted cell in Excel.

Plus, if you are going to be doing a lot of data exchanges between Inventor and Excel, then a few additional things can be done to improve performance greatly.  If using iLogic's GoExcel tools, be sure to use its GoExcel.Open line first, then do not include the Excel file's name, or sheet name, when using the other GoExcel lines for either reading or writing to that sheet.  Whenever you include the file name and sheet name, it is like re-opening the file, the closing it again immediately afterwards, which is more processing.  When you have used the Open method, then do not include the file name and sheet name, it simply reads or writes to that sheet without any other processing.  Also, there is a certain processing 'expense' for every line of code that either reads from, or writes to Excel, so when possible, it is always best to retrieve or write all (or as much as possible) data to/from Excel in as few lines as possible.  For instance, you can retrieve an entire area of the Excel sheet at one time as a 2-dimensional Array of Object (Object(,)), then work with that data as needed, then write that whole block of data back at one time, which can significantly reduce processing time.  There are lots more too, but just some food for thought.

[WCrihfield]

Hi guys.  Attached is a PDF I exported from an Excel file I created some time ago, that I think you (and others) may find helpful.  It shows you the Name, DisplayName, InternalName, associated Enum, Count (number of properties in it), & Item # (Index), of each PropertySet (collections which contain iProperties).   It also shows you the Item # (Index), Name, DisplayName, PropID, Enum variation, personal notes, & Data Type, of each Property (AKA: iProperty) in each PropertySet.  It also includes some of the ones you might encounter when working with Content Center files.  It is laid out in a somewhat of an advanced way, so it may be a little hard on the eyes at first, because it contains a whole lot of data.  Some of that data was attained through personal research/testing, and some was copied from Inventor's Help documentation.  There was not much room for the personal notes about each one, so I had to be extremely brief.  Some of those notes came from the Enum variation's explanation, within Inventor's Help documentation, and some are different from the Help documentation, due to the results of personally testing those aspects myself.  For instance, some that are described as ReadOnly in the Help documentation, can actually be written to in some situations.