Export BOM with defined Columns

[steveoliver]

Hi Guys

I am enquiring before asking for hints!

Is it possible to export an assembly BOM to an excel spreadsheet but specify in the macro what columns to export.

We have a specific requirement for the columns that are exported as the data is being used in a database. however as the columns are set up per seat i need the setup to be done via a macro.. so pretty much invisible to the user.

any help / info would be gratefully appreciated

this is in IV11 but we will be moving to 2008 / 2009 shortly!

Thanks

Steve

[steveoliver]

ok more info.. i have managed to adapt a macro now that seems to grab the info and another that can dump it into a notepad text file with separations so i can import it into excel. Dumping direct into excel is along the line..

macro is as follows


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
Dim oMaterialProperty As Property
Dim oRevProperty As Property
Dim oCommentsProperty 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")

'Get the file property that contains the "Material"
Set oMaterialProperty = oCompDef.PropertySets.Item("Design Tracking Properties").Item("Material")

'Get the file property that contains the "Document Revision"
Set oRevProperty = oCompDef.Document.PropertySets.Item("{F29F85E0-4FF9-1068-AB91-08002B27B3D9}").ItemByPropId(kRevisionSummaryInformation)

'Get the file property that contains the "Comments"
Set oCommentsProperty = oCompDef.Document.PropertySets.Item("{F29F85E0-4FF9-1068-AB91-08002B27B3D9}").ItemByPropId(kCommentsSummaryInformation)

Print #1, Tab(ItemTab); oRow.ItemNumber; "\"; oRow.ItemQuantity; "\"; oPartNumProperty.Value; "\"; oDescripProperty.Value; "\"; oMaterialProperty.Value; "\"; oRevProperty.Value; "\"; oCommentsProperty.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")

'Get the file property that contains the "Material"
Set oMaterialProperty = oCompDef.Document.PropertySets.Item("Design Tracking Properties").Item("Material")

'Get the file property that contains the "Document Revision"
Set oRevProperty = oCompDef.Document.PropertySets.Item("{F29F85E0-4FF9-1068-AB91-08002B27B3D9}").ItemByPropId(kRevisionSummaryInformation)

'Get the file property that contains the "Comments"
Set oCommentsProperty = oCompDef.Document.PropertySets.Item("{F29F85E0-4FF9-1068-AB91-08002B27B3D9}").ItemByPropId(kCommentsSummaryInformation)


Print #1, Tab(ItemTab); oRow.ItemNumber; "\"; oRow.ItemQuantity; "\"; oPartNumProperty.Value; "\"; oDescripProperty.Value; "\"; oMaterialProperty.Value; "\"; oRevProperty.Value; "\"; oCommentsProperty.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


now i want to add the following but cant work out how? component type, BOM structure (normal phantom etc) and Full File path

my main aim is to automate the BOM export to Excel in a standardised format, not relying on the individual seat of inventor having the BOM View customised correctly.

Thanks in advance all help appreciated!

Steve Oliver

[Anonymous]

The API doesn't currently provide access to the BOM table, which is where
you would have access over the columns. The best approach is something like
what you're already doing. Output the BOM in some format and then clean it
up as a seperate step. This can be done like you're doing now, through an
intermediate file, or you could still write it directly into Excel and then
use Excel's API to delete the column's you don't want.
--
Brian Ekins
Autodesk Inventor API

[steveoliver]

Thanks Brian

i was thinking that this was the case, its a shame as i am so close to getting this done.

The issue i have with doing the export BOM method is that i cannot guarantee consistency all the time due to the output from the BOM database to excel being entirely dependant on the runtime column customisation of each seat. By having a macro it means i can strip the data out of the parts / database and have a consistent format per seat regardless.

as i have only a few areas left to cover, is there perhaps any workarounds i can use to get around this limitation?

does 2008 / 2009 have any improvement on the API?

Thanks

Steve Oliver

[Anonymous]

The other option is to generate the BOM yourself. This way your completely
independent of the formatting the user has chosen. The API to the BOM data
essentially just provides the structure and to get the information you want
to show for each document you use other API functionality, typically
iProperties, to extract it. Look at the sample in the programming help
titled "BOM Object". It demonstrates this.
--
Brian Ekins
Autodesk Inventor API