I'm looking to write a VBA macro that'll run through an entire assembly and list all the assemblies and parts that have a custom property like "Inhouse" set to a certain value (YES/NO)...
Anyone able to point me at an example of something that at least runs through a whole assembly?
Thanks
Solved! Go to Solution.
Solved by Gruff. Go to Solution.
This is from memory so you probably will have to do some digging in the API help.
----
Dim oTopDoc as Inventor.Document
Dim oDoc as Inventor.Document
Dim oPropSet As Inventor.PropertySet
Dim oProp As Inventor.Property
Dim sHold As String
Dim sFoundDocs as string
Set oTopDoc = ThisApplication.ActiveDocument
sFoundDocs = ""
For each oDoc in oTopDoc.AllReferencedDocuments
Set oPropSet = oDoc.PropertySets("User Defined Properties")
sHold = ""
For Each oProp In oPropSet
If oProp.Name = "InHouse" Then
sHold = oProp.Value
Exit For
End If
Next oProp
If sHold <> "" then
sFoundDocs = sFoundDocs & oDoc.FullFileName & vbcrlf
End If
Next oDoc
msgbox sFoundDocs
Yes the oTopDoc.AllReferencedDocuments will give all parts/assemblies from the main assembly and all sub-assemblies. There is only one possible draw back, which concernes derived parts.
If you don't have any derived parts don't worry about this and even if you do it would only effect the script in certain circumstances. Lets say you have a left hand part and derived a right hand part. If you only have the right hand some where in the assembly you run this command on, when using .AllReferencedDocuments it will also include the left hand part.
I hope what I said doesn't confuse or alarm you, just want to give a heads up since i've run into the same thing before and it was an issue for me.
It's not alarming to me. We don't do much, if anything, with derived parts.
In essence, we're trying to develop a tool to allow us tag things for shipping location and then turn around a generate a report that says these pieces go here and these pieces go there, etc. If can made as simple as a field in a part/assembly that says ship: <pick location from pull down>, and then running the program to generate the report - it's a huge win.
I just ran the file on a quick dummied up assembly file.
Contents of file were two assemblies.
Assembly 1 had 3 parts with no tagged sub-pieces.
Assembly 2 had about 10 parts with 2 tagged sub-pieces.
Both top level assemblies were tagged.
My expectation was that the function would return the two tagged upper level assemblies and the two tagged lower level parts for a total of 4 pieces in the final message box.
Turns out it's because the sub-component files weren't in fact files, but virtual parts.
My guess is that while the virtual parts are seen, since the output is only listing fullfilename for each object found - there's not a filename for a virtual part. I'd need to get it to return something else, like the Description line.
Straight from wiki help.
The virtual component is a component that requires no modeling of geometry and no file. It is the equivalent to a custom part in a parts list.
Virtual components are considered and treated as real components for all practical purposes. They have a browser representation, have properties such as quantity, BOM Structure, Part Number, and so on.
You can create them using the create component command inside an assembly and ticking a check box on that dialog.
That's pretty much what i've done. I'm taking a BOM output and search it for row's that meet the criteria and then only exporting those rows. Seeing as i'll have to deal with old pieces that won't necessarily have the field in question in them (i.e. library parts usually), i'm having to use error handling to deal with those row.
On Error Resume Next ' oTagProperty.Value = "" Set oTagProperty = oCompDef.Document.PropertySets _ .Item("User Defined Properties").Item("TAG_NO") If Len(oTagProperty.Value) > 0 Then
sText = sText + items to export here & vbcrlf
end if
There's probably a more elegant way of handling whether or not the property is present on the object or not, though 🙂
I think you are missing what I am getting at.
Virtual Parts show up in the BOM.
Instead of looping through the assembly parts and looking at properties,
programmically grab the BOM object (Assuming you have added your property to the BOM output.
You can even add it with visibility turned off if you need to.)
Do one pass through the actual BOM to get your filenames and property values. I do not know
if Virtual parts can have a property, but if they can then you can get the output you want.
I've switched to this at the moment. It's from the API Samples/BOM
Public Sub BOMQuery() ' Set a reference to the assembly document. ' This assumes an assembly document is active. Dim oDoc As AssemblyDocument Set oDoc = ThisApplication.ActiveDocument Dim FirstLevelOnly As Boolean If MsgBox("First level only?", vbYesNo) = vbYes Then FirstLevelOnly = True Else FirstLevelOnly = False End If ' Set a reference to the BOM Dim oBOM As BOM Set oBOM = oDoc.ComponentDefinition.BOM ' Set whether first level only or all levels. If FirstLevelOnly Then oBOM.StructuredViewFirstLevelOnly = True Else oBOM.StructuredViewFirstLevelOnly = False End If ' Make sure that the structured view is enabled. oBOM.StructuredViewEnabled = True 'Set a reference to the "Structured" BOMView Dim oBOMView As BOMView Set oBOMView = oBOM.BOMViews.Item("Structured") Debug.Print "Item"; Tab(15); "Quantity"; Tab(30); "Part Number"; Tab(70); "Description" Debug.Print "----------------------------------------------------------------------------------" 'Initialize the tab for ItemNumber Dim ItemTab As Long ItemTab = -3 Call QueryBOMRowProperties(oBOMView.BOMRows, ItemTab) End Sub Private Sub QueryBOMRowProperties(oBOMRows As BOMRowsEnumerator, ItemTab As Long) ItemTab = ItemTab + 3 ' Iterate through the contents of the BOM Rows. Dim i As Long For i = 1 To oBOMRows.Count ' Get the current row. Dim oRow As BOMRow Set oRow = oBOMRows.Item(i) 'Set a reference to the primary ComponentDefinition of the row Dim oCompDef As ComponentDefinition Set oCompDef = oRow.ComponentDefinitions.Item(1) Dim oPartNumProperty As Property Dim oDescripProperty As Property If Typeof oCompDef Is VirtualComponentDefinition Then 'Get the file property that contains the "Part Number" 'The file property is obtained from the virtual component definition Set oPartNumProperty = oCompDef.PropertySets _ .Item("Design Tracking Properties").Item("Part Number") 'Get the file property that contains the "Description" Set oDescripProperty = oCompDef.PropertySets _ .Item("Design Tracking Properties").Item("Description") Debug.Print Tab(ItemTab); oRow.ItemNumber; Tab(17); oRow.ItemQuantity; Tab(30); _ oPartNumProperty.Value; Tab(70); oDescripProperty.Value Else 'Get the file property that contains the "Part Number" 'The file property is obtained from the parent 'document of the associated ComponentDefinition. Set oPartNumProperty = oCompDef.Document.PropertySets _ .Item("Design Tracking Properties").Item("Part Number") 'Get the file property that contains the "Description" Set oDescripProperty = oCompDef.Document.PropertySets _ .Item("Design Tracking Properties").Item("Description") Debug.Print Tab(ItemTab); oRow.ItemNumber; Tab(17); oRow.ItemQuantity; Tab(30); _ oPartNumProperty.Value; Tab(70); oDescripProperty.Value 'Recursively iterate child rows if present. If Not oRow.ChildRows Is Nothing Then Call QueryBOMRowProperties(oRow.ChildRows, ItemTab) End If End If Next ItemTab = ItemTab - 3 End Sub
For whateve reason they're not reading all values from the BOM, though. They are reaching outside the bom for a couple, but i'm not sure why. Part Number and Description are both standard BOM fields.
It's not physically resident in every ipt file, but it resident in every iam file.
Plus, if i use the straight export to bom sample, the entire thing exports.with all the columns present in the top level iam file.
I have modifited the above to kick out the entries I want and using debug.print get the output i'm looking for. So, it's nearly a success.
At the present, though, i'm trying to send the qualifying entries to excel - and just the columns i'm after. Only problem i have is that no matter what I do, it doesn't seem to move down to the second level. I'm fairly sure the problem is that in order to go down to the next level it re-call's the function it's presently in. As the second part stands now, i can't get the lower level parts/assemblies to make it to excel - meanwhile, though, all the debug.print lines work perfectly.
Private Sub QueryBOMRowProperties(oBOMRows As BOMRowsEnumerator, ItemTab As Long) If ExcelSession <> True Then Dim fExcel As Excel.Application Dim fWB As Workbook Set fExcel = New Excel.Application Set fWB = fExcel.Workbooks.Open("R:\Inventor\VBA\blank.xlsx") ExcelSession = True fExcel.Visible = True End If 'Dim MyCount As Integer ItemTab = ItemTab + 3 ' Iterate through the contents of the BOM Rows. Dim i As Long For i = 1 To oBOMRows.Count ' Get the current row. Dim oRow As BOMRow Set oRow = oBOMRows.Item(i) 'Set a reference to the primary ComponentDefinition of the row Dim oCompDef As ComponentDefinition Set oCompDef = oRow.ComponentDefinitions.Item(1) Dim oPartNumProperty As Property Dim oDescripProperty As Property Dim oTagProperty As Property If TypeOf oCompDef Is VirtualComponentDefinition Then 'Get the file property that contains the "Part Number" 'The file property is obtained from the virtual component definition Set oPartNumProperty = oCompDef.PropertySets _ .Item("Design Tracking Properties").Item("Part Number") 'Get the file property that contains the "Description" Set oDescripProperty = oCompDef.PropertySets _ .Item("Design Tracking Properties").Item("Description") On Error Resume Next Set oTagProperty = oCompDef.PropertySets _ .Item("User Defined Properties").Item("TAG_NO") If Len(oTagProperty.Value) > 0 Then Debug.Print Tab(0); oTagProperty.Value; Tab(20); oDescripProperty.Value; Tab(90); oPartNumProperty.Value; Tab(110); oRow.ItemQuantity; Tab(120); MyCount; fExcel.Range("B" & MyCount + 9).Select fExcel.ActiveCell.Value = oTagProperty.Value fExcel.Range("C" & MyCount + 9).Select fExcel.ActiveCell.Value = oDescripProperty.Value fExcel.Range("F" & MyCount + 9).Select fExcel.ActiveCell.Value = oPartNumProperty.Value fExcel.Range("G" & MyCount + 9).Select fExcel.ActiveCell.Value = oRow.ItemQuantity ' MsgBox (oDescripProperty.Value) MyCount = MyCount + 1 End If Else 'Get the file property that contains the "Part Number" 'The file property is obtained from the parent 'document of the associated ComponentDefinition. Set oPartNumProperty = oCompDef.Document.PropertySets _ .Item("Design Tracking Properties").Item("Part Number") 'Get the file property that contains the "Description" Set oDescripProperty = oCompDef.Document.PropertySets _ .Item("Design Tracking Properties").Item("Description") On Error Resume Next ' oTagProperty.Value = "" Set oTagProperty = oCompDef.Document.PropertySets _ .Item("User Defined Properties").Item("TAG_NO") If Len(oTagProperty.Value) > 0 Then Debug.Print Tab(0); oTagProperty.Value; Tab(20); oDescripProperty.Value; Tab(90); oPartNumProperty.Value; Tab(110); oRow.ItemQuantity; Tab(120); MyCount; fExcel.Range("B" & MyCount + 9).Select fExcel.ActiveCell.Value = oTagProperty.Value fExcel.Range("C" & MyCount + 9).Select fExcel.ActiveCell.Value = oDescripProperty.Value fExcel.Range("F" & MyCount + 9).Select fExcel.ActiveCell.Value = oPartNumProperty.Value fExcel.Range("G" & MyCount + 9).Select fExcel.ActiveCell.Value = oRow.ItemQuantity 'MsgBox (oDescripProperty.Value) MyCount = MyCount + 1 End If 'Recursively iterate child rows if present. If Not oRow.ChildRows Is Nothing Then Call QueryBOMRowProperties(oRow.ChildRows, ItemTab) End If End If Next ItemTab = ItemTab - 3 'ActiveWorkbook.SaveAs ("R:\drafting\bom\SL-JZ-FABs1r0.xlsx") 'ActiveWorkbook.Close savechanges:=False, FileName:="R:\Inventor\VBA\blank.xlsx" 'activework.Close savechanges:=True, FileName:="R:\\drafting\bom\SL-JZ-FABs1r0.xlsx" 'fExcel.Quit End Sub
I commented out the file saving in hopes of seeing where the lower level info was going, but I can't tell. Seems to go nowhere.
My guess is that re-calling the queryBOMRowProperties function is messing something up in regards to the excel writing. hence My ExcelSession flag. Still doesn't work with that. Seems like the file should still be open and work, butit's not seeing a file to write to.
I've been thinking i just nned to make a string for the whole thing then send it to another function to export to excel, but converting multiple lines of delimited text is proving frustrating. I know i have to split it into lines, then in each line split up the entries, but it's kicking my butt right now.
This was working just great, but i've run into a bizarre issue.
As long as the first part in the main assembly is a normal part or virtual part it works just fine. If it's a library part, it generates a blank line of text in the QueryBOMRowProperties() for debug.print.
Not sure why that would be an issue as reading the information is fine otherwise and doesn't require write access or anything.
Ad Gruff, do you have something you can point me to that grabs the whole BOM as you were talking about? I can't seem to find anything, but i may not be looking in the right spot 🙂