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

ilogic export BOM

idealogicERZYZ
Advocate

ilogic export BOM

idealogicERZYZ
Advocate
Advocate

Hello,
I'm using the following rule:

 

Dim oDoc As AssemblyDocument
oDoc = ThisApplication.ActiveDocument

Dim oBOM As BOM
oBOM = oDoc.ComponentDefinition.BOM

oBOM.PartsOnlyViewEnabled = True

Dim oBOMView As BOMView
oBOMView = oBOM.BOMViews.Item("Strukturalny")

xlApp = CreateObject("Excel.Application")

'comment out or change to false
'in order to not show Excel
xlApp.Visible = True
xlWorkbook = xlApp.Workbooks.Open("C:\Temp\Szablon.xlsx")

xlWorksheet = xlWorkbook.Worksheets.Item("BOM")

Dim row As Integer
row = 3

'xlWorksheet.Range("B4").Value = "ITEM"
'xlWorksheet.Range("C4").Value = "QTY"
'xlWorksheet.Range("D4").Value = "DESC"
'xlWorksheet.Range("E4").Value = "Part Number"

'Dim bRow As bomRow
bRows = oBOMView.BOMRows
For Each bRow In bRows

Dim rDoc As Document
rDoc = bRow.ComponentDefinitions.Item(1).Document

Dim docPropertySet As PropertySet
docPropertySet = rDoc.PropertySets.Item("Design Tracking Properties")

xlWorksheet.Range("A" & row).Value = bRow.ItemNumber
xlWorksheet.Range("B" & row).Value = docPropertySet.Item("Part Number").Value
xlWorksheet.Range("C" & row).Value = bRow.ItemQuantity
xlWorksheet.Range("D" & row).Value = docPropertySet.Item("Stock number").Value
xlWorksheet.Range("E" & row).Value = docPropertySet.Item("Vendor").Value
xlWorksheet.Range("F" & row).Value = docPropertySet.Item("Description").Value
xlWorksheet.Range("G" & row).Value = docPropertySet.Item("Cost").Value


row = row + 1
Next

oNow = DateString & "_" & TimeString
oNow = oNow.Replace("/","_")
oNow = oNow.Replace(":","_")
xlWorkBook.SaveAs(Filename:="C:\Temp\BOM-" & oNow & ".xlsx")

xlWorkbook.Close (True)
xlApp.Quit

 

 

 

The problem is that using this rule after export the new element adds to my list at the end of my xls template.

 

image.png

image.png

 

 

 

 

If I use the export icon from .iam - then structural BOM exports correctly:

 

image.png

 

And I get the correct xls:

 

 

image.png

 

Any idea why this is happening ...

 

 

Thanks

Ed

0 Likes
Reply
3,726 Views
25 Replies
Replies (25)

bradeneuropeArthur
Mentor
Mentor

Because it is the latest item added.

 

I you look how inventor exports the Bom this is different because they do not use the Item Number.

 

Solution:

 

Sort your Excel file afterwards via coding

Regards,

Arthur Knoors

Autodesk Affiliations:

Autodesk Software:Inventor Professional 2025 | Vault Professional 2024 | Autocad Mechanical 2024
Programming Skills:Vba | Vb.net (Add ins Vault / Inventor, Applications) | I-logic
Programming Examples:Drawing List!|Toggle Drawing Sheet!|Workplane Resize!|Drawing View Locker!|Multi Sheet to Mono Sheet!|Drawing Weld Symbols!|Drawing View Label Align!|Open From Balloon!|Model State Lock!
Posts and Ideas:Dimension Component!|Partlist Export!|Derive I-properties!|Vault Prompts Via API!|Vault Handbook/Manual!|Drawing Toggle Sheets!|Vault Defer Update!


! For administrative reasons, please mark a "Solution as solved" when the issue is solved !

0 Likes

MechMachineMan
Advisor
Advisor

I believe my signature has a nice example listed of how to sort the BOM after export to excel.


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

idealogicERZYZ
Advocate
Advocate

Justin,
But what solution ? Where is it ?

Please enter the link

 

Ed

0 Likes

bradeneuropeArthur
Mentor
Mentor

Hi,

 

Put this to your code:

 

' i is needed for the rows to be sorted. Maximum items of the BOM

Public Sub ExcelSortColumns(a As Workbook, i As Integer)

Dim b As Worksheet = a.Worksheets("SHEETNAME")

Dim r As Range = b.Range("A1:G" & i)

r.Sort(r.Columns(1), XlSortOrder.xlAscending, , , , , , XlYesNoGuess.xlYes, , , XlSortOrientation.xlSortColumns, XlSortMethod.xlPinYin, )

End Sub

Regards,

Arthur Knoors

Autodesk Affiliations:

Autodesk Software:Inventor Professional 2025 | Vault Professional 2024 | Autocad Mechanical 2024
Programming Skills:Vba | Vb.net (Add ins Vault / Inventor, Applications) | I-logic
Programming Examples:Drawing List!|Toggle Drawing Sheet!|Workplane Resize!|Drawing View Locker!|Multi Sheet to Mono Sheet!|Drawing Weld Symbols!|Drawing View Label Align!|Open From Balloon!|Model State Lock!
Posts and Ideas:Dimension Component!|Partlist Export!|Derive I-properties!|Vault Prompts Via API!|Vault Handbook/Manual!|Drawing Toggle Sheets!|Vault Defer Update!


! For administrative reasons, please mark a "Solution as solved" when the issue is solved !

idealogicERZYZ
Advocate
Advocate

After exporting it is an easy matter - but why it does not export by the rule.

 

Ed

0 Likes

bradeneuropeArthur
Mentor
Mentor

as i replied.

 

the item from the API is always the latest.

Also after renumbering the item of the API stays the latest Number.

 

The ItemNumber of the Bom is only a nickname

 

If you have ie:

 

API Item _________Bom ItemNumber

1                             2

2                             3

3                             4

and you add one

4                             5

 

and you renumber this

1                             2

2                             3

3                             4

4                             1

 

the API item keeps 4

 

Regards,

Arthur Knoors

Autodesk Affiliations:

Autodesk Software:Inventor Professional 2025 | Vault Professional 2024 | Autocad Mechanical 2024
Programming Skills:Vba | Vb.net (Add ins Vault / Inventor, Applications) | I-logic
Programming Examples:Drawing List!|Toggle Drawing Sheet!|Workplane Resize!|Drawing View Locker!|Multi Sheet to Mono Sheet!|Drawing Weld Symbols!|Drawing View Label Align!|Open From Balloon!|Model State Lock!
Posts and Ideas:Dimension Component!|Partlist Export!|Derive I-properties!|Vault Prompts Via API!|Vault Handbook/Manual!|Drawing Toggle Sheets!|Vault Defer Update!


! For administrative reasons, please mark a "Solution as solved" when the issue is solved !

0 Likes

idealogicERZYZ
Advocate
Advocate

Something does not work after my pasting.
Can you send the whole with my script?

 

Ed

0 Likes

bradeneuropeArthur
Mentor
Mentor
Dim oDoc As AssemblyDocument
oDoc = ThisApplication.ActiveDocument

Dim oBOM As BOM
oBOM = oDoc.ComponentDefinition.BOM

oBOM.PartsOnlyViewEnabled = True

Dim oBOMView As BOMView
oBOMView = oBOM.BOMViews.Item("Strukturalny")

xlApp = CreateObject("Excel.Application")

'comment out or change to false
'in order to not show Excel
xlApp.Visible = True
xlWorkbook = xlApp.Workbooks.Open("C:\Temp\Szablon.xlsx")

xlWorksheet = xlWorkbook.Worksheets.Item("BOM")

Dim row As Integer
row = 3

'xlWorksheet.Range("B4").Value = "ITEM"
'xlWorksheet.Range("C4").Value = "QTY"
'xlWorksheet.Range("D4").Value = "DESC"
'xlWorksheet.Range("E4").Value = "Part Number"

'Dim bRow As bomRow
bRows = oBOMView.BOMRows
For Each bRow In bRows

Dim rDoc As Document
rDoc = bRow.ComponentDefinitions.Item(1).Document

Dim docPropertySet As PropertySet
docPropertySet = rDoc.PropertySets.Item("Design Tracking Properties")

xlWorksheet.Range("A" & row).Value = bRow.ItemNumber
xlWorksheet.Range("B" & row).Value = docPropertySet.Item("Part Number").Value
xlWorksheet.Range("C" & row).Value = bRow.ItemQuantity
xlWorksheet.Range("D" & row).Value = docPropertySet.Item("Stock number").Value
xlWorksheet.Range("E" & row).Value = docPropertySet.Item("Vendor").Value
xlWorksheet.Range("F" & row).Value = docPropertySet.Item("Description").Value
xlWorksheet.Range("G" & row).Value = docPropertySet.Item("Cost").Value


row = row + 1
Next


Dim r As Range 
' How many Colums defined by G how many rows defined by i
r= xlWorksheet.Range("A1:G" & i)

'IE
'r= xlWorksheet.Range("A1:G100") r.Sort(r.Columns(1), XlSortOrder.xlAscending, , , , , , XlYesNoGuess.xlYes, , , XlSortOrientation.xlSortColumns, XlSortMethod.xlPinYin, ) oNow = DateString & "_" & TimeString oNow = oNow.Replace("/","_") oNow = oNow.Replace(":","_") xlWorkBook.SaveAs(Filename:="C:\Temp\BOM-" & oNow & ".xlsx") xlWorkbook.Close (True) xlApp.Quit

Regards,

Arthur Knoors

Autodesk Affiliations:

Autodesk Software:Inventor Professional 2025 | Vault Professional 2024 | Autocad Mechanical 2024
Programming Skills:Vba | Vb.net (Add ins Vault / Inventor, Applications) | I-logic
Programming Examples:Drawing List!|Toggle Drawing Sheet!|Workplane Resize!|Drawing View Locker!|Multi Sheet to Mono Sheet!|Drawing Weld Symbols!|Drawing View Label Align!|Open From Balloon!|Model State Lock!
Posts and Ideas:Dimension Component!|Partlist Export!|Derive I-properties!|Vault Prompts Via API!|Vault Handbook/Manual!|Drawing Toggle Sheets!|Vault Defer Update!


! For administrative reasons, please mark a "Solution as solved" when the issue is solved !

0 Likes

idealogicERZYZ
Advocate
Advocate

Hi, 

 

Type: Range is not defined
An element was not declared: XISortOrder. It may be unavailable from the level of its protection.

 

 image.png

 

0 Likes

bradeneuropeArthur
Mentor
Mentor

did you add a reference to Excel.

 

This is missing I think!

Regards,

Arthur Knoors

Autodesk Affiliations:

Autodesk Software:Inventor Professional 2025 | Vault Professional 2024 | Autocad Mechanical 2024
Programming Skills:Vba | Vb.net (Add ins Vault / Inventor, Applications) | I-logic
Programming Examples:Drawing List!|Toggle Drawing Sheet!|Workplane Resize!|Drawing View Locker!|Multi Sheet to Mono Sheet!|Drawing Weld Symbols!|Drawing View Label Align!|Open From Balloon!|Model State Lock!
Posts and Ideas:Dimension Component!|Partlist Export!|Derive I-properties!|Vault Prompts Via API!|Vault Handbook/Manual!|Drawing Toggle Sheets!|Vault Defer Update!


! For administrative reasons, please mark a "Solution as solved" when the issue is solved !

0 Likes

idealogicERZYZ
Advocate
Advocate

I do not know how

0 Likes

bradeneuropeArthur
Mentor
Mentor

Hi,

I can send you everything, but be carefull with using copied code from external without knowing how it works and how to modify it in future.

I little training in I logic or better in a programming language would be very useful and critical for you I think.

Please let me know how we can help you to a higher level regarding programming!?

Regards,

Arthur Knoors

Autodesk Affiliations:

Autodesk Software:Inventor Professional 2025 | Vault Professional 2024 | Autocad Mechanical 2024
Programming Skills:Vba | Vb.net (Add ins Vault / Inventor, Applications) | I-logic
Programming Examples:Drawing List!|Toggle Drawing Sheet!|Workplane Resize!|Drawing View Locker!|Multi Sheet to Mono Sheet!|Drawing Weld Symbols!|Drawing View Label Align!|Open From Balloon!|Model State Lock!
Posts and Ideas:Dimension Component!|Partlist Export!|Derive I-properties!|Vault Prompts Via API!|Vault Handbook/Manual!|Drawing Toggle Sheets!|Vault Defer Update!


! For administrative reasons, please mark a "Solution as solved" when the issue is solved !

0 Likes

idealogicERZYZ
Advocate
Advocate

Hi,

 

For now, this code is enough for me. If you can, send me the whole.
I will be grateful. Thank you.

 

Ed

0 Likes

bradeneuropeArthur
Mentor
Mentor
What version of excel are you using.
Need this for the reference path

Regards,

Arthur Knoors

Autodesk Affiliations:

Autodesk Software:Inventor Professional 2025 | Vault Professional 2024 | Autocad Mechanical 2024
Programming Skills:Vba | Vb.net (Add ins Vault / Inventor, Applications) | I-logic
Programming Examples:Drawing List!|Toggle Drawing Sheet!|Workplane Resize!|Drawing View Locker!|Multi Sheet to Mono Sheet!|Drawing Weld Symbols!|Drawing View Label Align!|Open From Balloon!|Model State Lock!
Posts and Ideas:Dimension Component!|Partlist Export!|Derive I-properties!|Vault Prompts Via API!|Vault Handbook/Manual!|Drawing Toggle Sheets!|Vault Defer Update!


! For administrative reasons, please mark a "Solution as solved" when the issue is solved !

0 Likes

idealogicERZYZ
Advocate
Advocate

MS Office 2016. Should I submit my template?

 

Ed

0 Likes

idealogicERZYZ
Advocate
Advocate

Hello
Can I ask for everything, along with the code.

 

greetings

Ed

0 Likes

bradeneuropeArthur
Mentor
Mentor
Of course

Regards,

Arthur Knoors

Autodesk Affiliations:

Autodesk Software:Inventor Professional 2025 | Vault Professional 2024 | Autocad Mechanical 2024
Programming Skills:Vba | Vb.net (Add ins Vault / Inventor, Applications) | I-logic
Programming Examples:Drawing List!|Toggle Drawing Sheet!|Workplane Resize!|Drawing View Locker!|Multi Sheet to Mono Sheet!|Drawing Weld Symbols!|Drawing View Label Align!|Open From Balloon!|Model State Lock!
Posts and Ideas:Dimension Component!|Partlist Export!|Derive I-properties!|Vault Prompts Via API!|Vault Handbook/Manual!|Drawing Toggle Sheets!|Vault Defer Update!


! For administrative reasons, please mark a "Solution as solved" when the issue is solved !

0 Likes

bradeneuropeArthur
Mentor
Mentor
Please send me a private message so I will not forget.
I am not at my desk right now.

Regards,

Arthur Knoors

Autodesk Affiliations:

Autodesk Software:Inventor Professional 2025 | Vault Professional 2024 | Autocad Mechanical 2024
Programming Skills:Vba | Vb.net (Add ins Vault / Inventor, Applications) | I-logic
Programming Examples:Drawing List!|Toggle Drawing Sheet!|Workplane Resize!|Drawing View Locker!|Multi Sheet to Mono Sheet!|Drawing Weld Symbols!|Drawing View Label Align!|Open From Balloon!|Model State Lock!
Posts and Ideas:Dimension Component!|Partlist Export!|Derive I-properties!|Vault Prompts Via API!|Vault Handbook/Manual!|Drawing Toggle Sheets!|Vault Defer Update!


! For administrative reasons, please mark a "Solution as solved" when the issue is solved !

0 Likes

idealogicERZYZ
Advocate
Advocate

Hi,

 

idealogic@onet.pl

 

thank you in advance
greetings

Ed

 

 

0 Likes