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,712 Views
25 Replies
Replies (25)

bradeneuropeArthur
Mentor
Mentor

Hi,

 

Changed your code with a reference to Excel 2016:

 

Hope that it works!

 

AddReference "C:\Windows\assembly\GAC_MSIL\Microsoft.Office.Interop.Excel\15.0.0.0############\Microsoft.Office.Interop.Excel.dll"


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("Structured")'("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 Microsoft.Office.Interop.Excel.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")
Dim sortOrder As Microsoft.Office.Interop.Excel.Sort
sortOrder.Orientation = Microsoft.Office.Interop.Excel.XlSortOrder.xlAscending


r.Sort(r.Columns(1), Microsoft.Office.Interop.Excel.XlSortOrder.xlAscending, , , , , , Microsoft.Office.Interop.Excel.XlYesNoGuess.xlYes, , , Microsoft.Office.Interop.Excel.XlSortOrientation.xlSortColumns, Microsoft.Office.Interop.Excel.XlSortMethod.xlPinYin, )
oNow = DateString & "_" & TimeString
oNow = oNow.Replace("/","_")
oNow = oNow.Replace(":","_")
xlWorkBook.SaveAs(Filename:="C:\Temp\BOM-" & oNow & ".xlsx")

xlWorkbook.Close (True)
xlApp.Quit

###### means your version of Excel 2016 path.

search for the dll and replace #####

 

If assistance needed please let me know.

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

Hi,

 

Found some mistake:

 

use this:

 

AddReference "C:\Windows\assembly\GAC_MSIL\Microsoft.Office.Interop.Excel\15.0.0.0############\Microsoft.Office.Interop.Excel.dll"


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("Structured")'("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 Microsoft.Office.Interop.Excel.Range 
' How many Colums defined by G how many rows defined by i
r= xlWorksheet.Range("A1:G" & row)

'IE
'r= xlWorksheet.Range("A1:G100")
Dim sortOrder As Microsoft.Office.Interop.Excel.Sort
sortOrder.Orientation = Microsoft.Office.Interop.Excel.XlSortOrder.xlAscending


r.Sort(r.Columns(1), Microsoft.Office.Interop.Excel.XlSortOrder.xlAscending, , , , , , Microsoft.Office.Interop.Excel.XlYesNoGuess.xlYes, , , Microsoft.Office.Interop.Excel.XlSortOrientation.xlSortColumns, Microsoft.Office.Interop.Excel.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,

I found the path.

 

image.png

 

Unfortunately, the items do not sort out and an error occurs

 

 

image.png

 

Ed

0 Likes

bradeneuropeArthur
Mentor
Mentor

Hi,

 

I cannot read the error message because of the language.

can you translate it to English?

 

try this:

 

r= xlWorksheet.Range("A1:G100" )

 

as test.

 

please let me know if it works 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

bradeneuropeArthur
Mentor
Mentor

Hi,

 

I also needed to translate the BOM to English:

So Change Also this:

 

AddReference "C:\Windows\assembly\GAC_MSIL\Microsoft.Office.Interop.Excel\15.0.0.0############\Microsoft.Office.Interop.Excel.dll"


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 Microsoft.Office.Interop.Excel.Range 
' How many Colums defined by G how many rows defined by i
r= xlWorksheet.Range("A1:G" & row)

'IE
'r= xlWorksheet.Range("A1:G100")
Dim sortOrder As Microsoft.Office.Interop.Excel.Sort
sortOrder.Orientation = Microsoft.Office.Interop.Excel.XlSortOrder.xlAscending


r.Sort(r.Columns(1), Microsoft.Office.Interop.Excel.XlSortOrder.xlAscending, , , , , , Microsoft.Office.Interop.Excel.XlYesNoGuess.xlYes, , , Microsoft.Office.Interop.Excel.XlSortOrientation.xlSortColumns, Microsoft.Office.Interop.Excel.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

bradeneuropeArthur
Mentor
Mentor

Hi,

 

Put the sort after the save of the document.

 

oNow = DateString & "_" & TimeString
oNow = oNow.Replace("/","_")
oNow = oNow.Replace(":","_")
xlWorkBook.SaveAs(Filename :="C:\Temp\BOM-" & oNow & ".xlsx")
r.Sort(r.Columns(1), Microsoft.Office.Interop.Excel.XlSortOrder.xlAscending, , , , , , Microsoft.Office.Interop.Excel.XlYesNoGuess.xlYes, , , Microsoft.Office.Interop.Excel.XlSortOrientation.xlSortColumns, Microsoft.Office.Interop.Excel.XlSortMethod.xlPinYin, )

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