Quick Excel macro to rebuild an Excel Bill Of Materials

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.

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.

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