Announcements
Attention for Customers without Multi-Factor Authentication or Single Sign-On - OTP Verification rolls out April 2025. Read all about it here.

Export BOM with options

Anonymous

Export BOM with options

Anonymous
Not applicable

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:

BOM example.JPG

 

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.

 

0 Likes
Reply
1,952 Views
6 Replies
Replies (6)

NSBowser
Advocate
Advocate

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.
0 Likes

Anonymous
Not applicable

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?

0 Likes

Jef_E
Collaborator
Collaborator

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.. :slightly_smiling_face:

 

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
0 Likes

Anonymous
Not applicable

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? 

0 Likes

Anonymous
Not applicable

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
0 Likes

MechMachineMan
Advisor
Advisor

See this already existing thread. Adam has a proposal for a solution at the end of it.

 

https://forums.autodesk.com/t5/inventor-customization/thisbom-export-column-order-problem/m-p/490203...


--------------------------------------
Did you find this reply helpful ? If so please use the 'Accept as Solution' or 'Like' button below.

Justin K
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
0 Likes