- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report
Export BOM with options
I found this excellent blog which shows how you can export a parts list from an .idw file to a pre-existing XLS file
http://inventortrenches.blogspot.co.uk/2011/06/ilogic-export-parts-list-with-options.html
Is there a way to achieve a similar result with a BOM from a .iam file?
So far my iLogic code goes something like this
ThisBOM.Export("Structured", "fileName1", kMicrosoftExcelFormat)
Which produces this:
I'd like to export this data to a fancy looking pre-existing XLS file that has an 'Extended Cost' column J with the formula =I2*D2 and a 'Total' cell underneath.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report
The Inventor API Help has a pretty decent example which demonstrates the .IAM Bom functionality. It would be pretty easy to augment the code you have with the logic of accessing the .IAM BOM
This sample demonstrates the Bill of Materials API functionality in assemblies.
VBA Sample Code
Have an assembly document open and run the following sample.
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
Best of Luck
---------------------------------------------------------------------------------------------------------------------------------
If you find this reply helpful or insightful, please use the 'Accept as Solution' or 'Kudos' button below.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report
Hi, thanks for your reply.
I tried running the rule but got the following error:
Error in rule program format:
All other Sub's or Function's must be after Sub Main()
I'm running as an iLogic rule, should this be run as a macro?
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report
This should be run as VBA macro but it can work as iLogic code too.
Some simple adjustments should do.
I corrected the error as the message stated in your reply
Error in rule program format:
All other Sub's or Function's must be after Sub Main()
And commented the debug messages because iLogic cant debug.. sadly.. ![]()
You now have to choose what you want to do with it and tell it to do so. Because this does not do anything at the moment. If you want to export values into excel you should teach it how to do so.
Sub Main BOMQuery()
' a reference to the assembly document.
' This assumes an assembly document is active.
Dim oDoc As AssemblyDocument
oDoc = ThisApplication.ActiveDocument
Dim FirstLevelOnly As Boolean
If MsgBox("First level only?", vbYesNo) = vbYes Then
FirstLevelOnly = True
Else
FirstLevelOnly = False
End If
' a reference to the BOM
Dim oBOM As BOM
oBOM = oDoc.ComponentDefinition.BOM
' 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
'a reference to the "Structured" BOMView
Dim oBOMView As BOMView
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
oRow = oBOMRows.Item(i)
'a reference to the primary ComponentDefinition of the row
Dim oCompDef As ComponentDefinition
oCompDef = oRow.ComponentDefinitions.Item(1)
Dim oPartNumProperty As Inventor.Property
Dim oDescripProperty As Inventor.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
oPartNumProperty = oCompDef.PropertySets _
.Item("Design Tracking Properties").Item("Part Number")
'Get the file property that contains the "Description"
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.
oPartNumProperty = oCompDef.Document.PropertySets _
.Item("Design Tracking Properties").Item("Part Number")
'Get the file property that contains the "Description"
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
Please kudo if this post was helpfull
Please accept as solution if your problem was solved
Inventor 2014 SP2
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report
Thank you for your replies. Sorry for the huge delay, another big project came up and I had to put this on the back burner. I can get the code working for exporting to a new excel document, but really this isn't any different to the ThisBOM.Export iLogic function (minus the ability to re-order columns)
After hours of trawling forums and blogs, I am yet to find a way of exporting to an existing excel template from an assembly file, as is possible through the drawing parts list. Exporting from the parts list has one serious flaw, its not possible to get an all level structured BOM without manually expanding each row through the parts list editor.
Is anybody doing something different or has a workaround/solution to either of these methods?
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report
Here's something from here: http://beinginventive.typepad.com/files/ExportPartslistToExcel/ExportPartslistToExcel.txt
let me know if it works, I'm trying to get something similar happening.
'Expand legacy parts list to all levels
Dim counter As Integer
Dim k As Long
counter = 1
While counter < partList.PartsListRows.Count
For k = counter To partList.PartsListRows.Count
Dim orow As PartsListRow
Set orow = partList.PartsListRows.Item(k)
counter = k
While orow.Expandable And Not (orow.Expanded)
orow.Expanded = True
counter = counter + 1
Wend
Next k
Wend
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report
See this already existing thread. Adam has a proposal for a solution at the end of it.
--------------------------------------
Did you find this reply helpful ? If so please use the 'Accept as Solution' or 'Like' button below.
Inventor 2018.2.3, Build 227 | Excel 2013+ VBA
ERP/CAD Communication | Custom Scripting
Machine Design | Process Optimization
iLogic/Inventor API: Autodesk Online Help | API Shortcut In Google Chrome | iLogic API Documentation
Vb.Net/VBA Programming: MSDN | Stackoverflow | Excel Object Model
Inventor API/VBA/Vb.Net Learning Resources: Forum Thread
Sample Solutions:Debugging in iLogic ( and Batch PDF Export Sample ) | API HasSaveCopyAs Issues |
BOM Export & Column Reorder | Reorient Skewed Part | Add Internal Profile Dogbones |
Run iLogic From VBA | Batch File Renaming| Continuous Pick/Rename Objects
Local Help: %PUBLIC%\Documents\Autodesk\Inventor 2018\Local Help
Ideas: Dockable/Customizable Property Browser | Section Line API/Thread Feature in Assembly/PartsList API Static Cells | Fourth BOM Type