Quick Excel macro to rebuild an Excel Bill Of Materials

Posted on October 21, 2013

Let’s start this new week by sharing a small Excel macro which I have used for a while considering the use of a fairly constant format of BOM from our customers. This macro allows me to recreate BOM relationships from an Excel file in which we find a list of items with their BOM Level. It is quite easy to read for humans. We quickly understand that when a line level 3 and has the following level 4, there is a parent-child relationship between the two parts. However, for excel there is no relationship defined. Only our eyes and our understanding of the concept of BOM allows us to deduce the BOM structure. Here is an example of such BOM.

BOM

In order to migrate to a structured PLM solution, we need to recreate the relationships. It is necessary that we can indicate for each line which is the parent element.

First, I add a column after the “Level” Column to inform the Part Number of the parent element. I then apply the following macro (which needs to be configured for your file, initial constants to change. Hint: starting line is the first child, here it’s line 3).

Sub rebuild()

' initialize constant for your excel file '
Dim StartingLine As Integer
StartingLine = 3
Dim Column_Level As Integer
Column_Level = 1
Dim Column_ParentRef As Integer
Column_ParentRef = 2
Dim Column_PartRef As Integer
Column_PartRef = 3

' other technical variables '
Dim j As Integer
Dim StoreParents(40) As String

'initialize first parent Part Number '
initParent = Cells(StartingLine, 5)

' Start rebuilding BOM '
j = StartingLine
While Cells(j, Column_Level) <> ""

    If (CInt(Cells(j, Column_Level)) > CInt(Cells(j - 1, Column_Level))) Then

        ' level has increased so parentref is previous line part ref '
        StoreParents(Cells(j - 1, Column_Level)) = Cells(j - 1, Column_PartRef)
        Cells(j, Column_ParentRef) = Cells(j - 1, Column_PartRef)

    ElseIf (CInt(Cells(j, Column_Level)) < CInt(Cells(j - 1, Column_Level))) Then

        ' level has decreased so parentref is the stored partRef of new level - 1 '
        Cells(j, Column_ParentRef) = StoreParents(Cells(j, Column_Level) - 1)

    Else

        ' no level change, we keep the same ParentRef '
        Cells(j, Column_ParentRef) = Cells(j - 1, Column_ParentRef)

    End If

    ' move to next line '
    j = j + 1

Wend
End Sub

You should get a file with the parent references added. I purposely list an item level 3 followed by a level 1 to show that we can move up multiple levels at once without problem.

BOM_Built

And Voilà ! This is an easy piece of code but as many customer use this format. It can be quite handy to find that code quickly and apply it for a migration.


 

Yoann Maingon

Yoann Maingon is an Entrepreneur and a PLM enthousiast. He is our main blogger at Minerva as he has been publishing articles about General PLM concepts and Aras Innovator for more than three years.

More Posts

  • Tom

    Hi,

    Thanks a lot for the post. This is exactly what I need.

    I get an error when loading the code in VBA:
    There is a mistake with this line:
    While Cells(j, Column_Level) <> “”

    Can you advise what I do wrong?

    Best regards,
    Tom Hendriks

  • Yoann Maingon

    Try rewriting this line. There might be an issue with quotes. Also make sure you haven’t changed the setup of your excel file coding columns with numbers instead of letters.

  • Jasbir

    Hi All,

    I am working on Item Part. There is one property TotalWeight that I want to calculate. I am getting Rate from the Part attached to RelationshipItem Part BOM.
    Now I have created one Server Method which is as follows –

    //
    logic of getting property Rate by fetching Relationship Part BOM and calculating TotalWeight and then as follows
    //
    this.setAction(“edit”);
    this.setProperty(“_total_weight”,TotalWeight);
    return this.apply();

    I have set this method on Server Event onAfterUpdate.
    When I update the form and click on Save button I get the error “An item with the same key has already been added.”
    Would you please assist me here.

    Thanks,
    Jasbir

    • Hello Jasbir,

      The best practice of a Server method is as followed when you want to update an Item.

      // Method
      Innovator inn = this.getInnovator();
      Item thisItem = inn.newItem(this.getType(),”edit”);
      thisItem.setID(this.getID());
      thisItem.setProperty(“_total_weight”,TotalWeight);
      thisItem = thisItem.apply();
      return this;

      Remember that this is an XML DOM and has a lots of information in it, you can use this.getProperty(“item_number”,””); but when you want to edit, it is preferable to recreate the Item into the transaction to make sure you don’t have side effects.

      Hope it helps,
      BR,

      Damien

      • Jasbir

        Hi Damien,

        I am still getting the same error. When I update the form and click on Save button I get the error “An item with the same key has already been added.”

        I think it is not allowing it to edit as same ID is still not finished processing. I have been trapped in it.

        regards,
        Jasbir

Download Aras Innovator