BOM Auto Export

[JamieSENG]

I came across the below code and thought all my wishes had been granted. However, as with every other forum post I've seen up to know an issue is the exported columns are rearranged into alphabetical order. Is this something anyone has been able to figure out?

'Adapted from Inventor API Samples by Clint Brown @ClintCadline
'Originally posted on Cadline Community https://www.cadlinecommunity.co.uk/hc/en-us/articles/212849309
oDoc = ThisDoc.ModelDocument
If oDoc.DocumentType = kPartDocumentObject Then
MessageBox.Show("You need to be in an Assembly to Export a BOM", "Cadline: iLogic - BOM Publisher")
Return
End If
oDoc = ThisApplication.ActiveDocument
Dim oBOM As BOM
oBOM = oDoc.ComponentDefinition.BOM
'==========================================================================================
'You can change the output path by editing oPATH below
oPATH = ("c:\temp\") 'If you change this, remember to keep a \ at the end
'==========================================================================================
'STRUCTURED BoM ===========================================================================
' the structured view to 'all levels'
oBOM.StructuredViewFirstLevelOnly = False
' Make sure that the structured view is enabled.
oBOM.StructuredViewEnabled = True
Dim oStructuredBOMView As BOMView
oStructuredBOMView = oBOM.BOMViews.Item("Structured")
' Export the BOM view to an Excel file
oStructuredBOMView.Export (oPATH + ThisDoc.FileName(False) + ".xls", kMicrosoftExcelFormat)
'==========================================================================================
'PARTS ONLY BoM ===========================================================================
'' Make sure that the parts only view is enabled.
'oBOM.PartsOnlyViewEnabled = True
'Dim oPartsOnlyBOMView As BOMView
'oPartsOnlyBOMView = oBOM.BOMViews.Item("Parts Only")
'' Export the BOM view to an Excel file
'oPartsOnlyBOMView.Export (oPATH + "BOM-PartsOnly.xls", kMicrosoftExcelFormat)
'==========================================================================================
i = MessageBox.Show("Preview the BOM?", "Cadline: iLogic - BOM Publisher",MessageBoxButtons.YesNo)
If i = vbYes Then : launchviewer = 1 : Else : launchviewer = 0 : End If 
If launchviewer = 1 Then ThisDoc.Launch(oPATH + ThisDoc.FileName(False) + ".xls")

I'm also interested in what export options I can use and any pre-formatting I can do with illogic.

Some of the typical tasks I do within the bom editor before exporting manually.

First thing is check both bom types are enabled

Import my xml for column structure

Structured bom

Renumber items from 1

Check all levels is selected (I see it’s an option in the above code but I don’t know if that’s an issue if it wasn’t set as that)

Update mass (which I assume updates in parts only to)

Parts only

Sort by category then by part number in ascending order

Then I export both boms manually redirecting to a specific folder and using a temp name.

With both worksheets open in excel I combine the parts only with the structured and go about the usual formatting such auto-width deleting the tabs I don’t require and putting a border around what’s left. I then go about splitting the data it into multiple sheets by copying the sheet and further deleting the columns and rows not relevant to that specific sheet.

At the moment I’ve been putting together an excel template to save myself some time in formatting and creating the sheet. I’m hoping to automate as much as possible including the splitting of the data. I’m between splitting my data using a macro or making my structured and pt only tables into existing connection tables in the other sheets so the information updates as per originals. Obviously i have to get my data from inventor to excel which will leave me with two spreadsheet so at the point am i able to call on my template and copy the exported data from row A2 so it excludes headings and paste into correct sheet of template in A2 (which is part of a pre-made table)

I suppose my real question is what are my limitations using illogic to export my boms and at what what point will I have to rely on excel vba.

[MechMachineMan]

I think pretty much starting at rearranging columns you need to rely on the excel API.

[basnederveen]

To accomplish what I wanted to achieve I cycle through the parts only BOM and add the iproperties I want to print to excel to an array. Then I print the array columns in excel in whichever order I want.

You can use this code to open an excel template and basically fill every cell 

Dim xlApp As Object
Dim xlwb As Object
Dim xlws As Object
Set xlApp = CreateObject("Excel.Application")
Set xlwb = xlApp.Workbooks.Open(oTemplate)
Set xlws = xlwb.Worksheets(1)
xlApp.Visible = True

' use this to fill a cell
xlws.cells(row,col) = text

[JamieSENG]

Let and Set functions aren't supported anymore, what would be the alternative.

I assume your boms exported through the parts list of a drawing?

I export mine from a top-level assembly, would it work still?

Also, how is it working?

[JamieSENG]

The VBA in inventor? It's totally past my capabilities at this point if so.

So with that in mind id probably look to use the code within my original post and modify it as much as possible. 

If I can export my structured bom to a temp folder under a temp name can I name the sheet? After that id to open it and continue exporting the parts only but now it export into a new sheet within the structured bom that exported.

Then id call a macro that went about reordering the columns and doing the formatting.

Then potentially take that formatted data from each sheet and have it go into the structured and pt only sheets of the template bom.

I'm more confident of getting things done in excel because I normally just record a macro and modify it to work. I've no clue where id even begin using vba so is the above a more reasonable to ilogics capabilities?

[JamieSENG]

I just thought as I posted my previous reply couldn't I just call for my template and add the data directly to those relevant sheets?

[basnederveen]

Hi,

Adding the relevant information to the sheets you want is definitily possible in inventor VBA. I do not know about iLogic though, its probably also possible.

Below is some code I made to export some properties to excel. First the properties are stored in an array and then printed to excel, I was trying to print them directly but sometimes it skipped some values, thats why I tried the array approach. It creates a new excel file now but by changing 1 line (which is commented now) and setting your template you can make it fill a template.

You can configure the columns in the top of the code.

Private Sub BOMtest()

'generate mto script tests
'set variables
Dim oTemplate As String: oTemplate = "set ur template"
Dim oStartRow As Integer: oStartRow = 10

' set last column for array, array starts at 0 so its -1 quantity comes straight out of bom so we dont need to store it
Dim lastCol As Integer: lastCol = 7

'set individual columns for excel, use a number no letter
Dim nrCol As Long: nrCol = 1
Dim titleCol As Long:  titleCol = 2
Dim stocknoCol As Long: stocknoCol = 7
Dim comCol As Long: comCol = 5
Dim descCol As Long: descCol = 4
Dim massCol As Long: massCol = 8
Dim matCol As Long: matCol = 3
Dim qtyCol As Long: qtyCol = 9

'set assembly
Dim asm As AssemblyDocument
Set asm = ThisApplication.ActiveDocument

' set reference to referenced documents
Dim rds As DocumentsEnumerator
Set rds = asm.AllReferencedDocuments

' define bom
Dim oBOM As BOM
Set oBOM = asm.ComponentDefinition.BOM

' enable the parts only view
oBOM.PartsOnlyViewEnabled = True

' set the parts only view
Dim oBOMview As BOMView
Set oBOMview = oBOM.BOMViews("Parts only")

'set the properties we want to print
Dim oDescripProperty As Property
Dim oTitleProperty As Property
Dim oStockNoProperty As Property
Dim oCommentsProperty As Property

' store the columns in an array for easier printing
Dim oArray() As String
ReDim oArray(oBOMview.BOMRows.Count - 1, lastCol) As String

Dim a As Double: a = 0

' iterate trough bom rows
Dim oBOMrow As BOMRow
For Each oBOMrow In oBOMview.BOMRows
    
    ' setthe component definition
    Dim ocompdef As ComponentDefinition
    Set ocompdef = oBOMrow.ComponentDefinitions.Item(1)

    'Get the file property that contains the "Description"
    Set oDescripProperty = ocompdef.Document.PropertySets _
        .Item("Design Tracking Properties").Item("Description")
    
    ' set the title property
    Set oTitleProperty = ocompdef.Document.PropertySets _
    .Item("Inventor Summary Information").Item("Title")
    
     ' set the Stock Number property
    Set oStockNoProperty = ocompdef.Document.PropertySets _
    .Item("Design Tracking Properties").Item("Stock Number")
    
    ' set the Comments property
    Set oCommentsProperty = ocompdef.Document.PropertySets _
    .Item("Inventor Summary Information").Item("Comments")
    
    ' fill array
    oArray(a, 0) = (a + 1)
    oArray(a, 1) = oTitleProperty.Value
    oArray(a, 2) = oStockNoProperty.Value
    oArray(a, 3) = oCommentsProperty.Value
    oArray(a, 4) = oDescripProperty.Value
    oArray(a, 5) = Round(ocompdef.MassProperties.Mass, 3)
    oArray(a, 6) = ocompdef.Material.Name
    oArray(a, 7) = oBOMrow.ItemQuantity

    a = a + 1
    
Next
 'Stop
' set excel app and add worksheet
Dim xlApp As Object
Dim xlwb As Object
Dim xlws As Object
Set xlApp = CreateObject("Excel.Application")
'set xlwb = xlApp.workbooks.open(oTemplate)
Set xlwb = xlApp.Workbooks.Add
Set xlws = xlwb.Worksheets(1)
xlApp.Visible = True

' write more stuff
xlws.cells(2, 2) = asm.DisplayName
xlws.cells(4, 2) = Date

' set column heads
xlws.cells((oStartRow), nrCol) = "Nr."
xlws.cells((oStartRow), titleCol) = "Title"
xlws.cells((oStartRow), descCol) = "Description"
xlws.cells((oStartRow), massCol) = "Mass"
xlws.cells((oStartRow), matCol) = "Material"
xlws.cells((oStartRow), qtyCol) = "Quantity"

' print the array
Dim b As Double: b = 0

For b = 1 To oBOMview.BOMRows.Count

    ' print array
    xlws.cells(oStartRow + b, nrCol) = oArray(b - 1, 0)
    xlws.cells(oStartRow + b, titleCol) = oArray(b - 1, 1)
   ' xlws.Cells(oStartRow + b, stocknoCol) = oArray(b - 1, 2)
   ' xlws.Cells(oStartRow + b, comCol) = oArray(b - 1, 3)
    xlws.cells(oStartRow + b, descCol) = oArray(b - 1, 4)
    xlws.cells(oStartRow + b, massCol) = oArray(b - 1, 5)
    xlws.cells(oStartRow + b, matCol) = oArray(b - 1, 6)
    xlws.cells(oStartRow + b, qtyCol) = oArray(b - 1, 7)
Next


' save and close the document
' xlwb.SaveAs ("full string + doc name.xlsx")

' close the document
' Call xlwb.Close
End Sub

[MechMachineMan]

The task at hand is the road, the programming language you choose is the vehicle.

Some vehicles will struggle going up and down hills on the road, some will you get you there faster or more efficiently. All vehicles should generally be able to get you there if you try hard enough.

VBA and vb.net will both be able to accomplish the task. "iLogic" is not a programming language per se, but just an add-in that recognizes text and snippets and converts it to the relevant vb.net. 

[JamieSENG · Accepted solution]

Followed based on your advice, results work.

Im now able to export using an ilogic rule structured and pt only BoMs. Then using the below, reorder columns and move to my external BoM.

Sub Reorder_PartColumns()
    
    'move sheet to template
    Windows("PartsBOM Original.xls").Activate
    Sheets("Sheet1").Move Before:=Workbooks( _
    "External BOM Template.xlsm").Sheets(5)

    'set sheet name
    Sheets("Sheet1").Select
    Sheets("Sheet1").Name = "PartsBOM Original"
    
    'order columns
    Dim arrColOrder As Variant, ndx As Integer
    Dim Found As Range, counter As Integer
    
    arrColOrder = Array("Part Number", "Title", ..........")
    
    counter = 1
    
    Application.ScreenUpdating = False
    
    For ndx = LBound(arrColOrder) To UBound(arrColOrder)
    
        Set Found = Rows("1:1").Find(arrColOrder(ndx), LookIn:=xlValues, LookAt:=xlWhole, _
                          SearchOrder:=xlByColumns, SearchDirection:=xlNext, MatchCase:=False)
        
        If Not Found Is Nothing Then
            If Found.Column <> counter Then
                Found.EntireColumn.Cut
                Columns(counter).Insert shift:=xlToRight
                Application.CutCopyMode = False
            End If
            counter = counter + 1
        End If
        
    Next ndx
    
    Application.ScreenUpdating = True
       
End Sub

Been at this a while so its a relief its come together.

Thanks for your help @basnederveen, @MechMachineMan