Possible to export a particular BOM format (for MRP) with an iLogic rule?

[claudio.ibarra]

I'd like to create a button (a rule fired from a Global Form) that produces an XLS BOM sheet ready to copy into our MRP system. Right now, the members of my team export a BOM using the Export feature, and then manually make some formatting edits to prepare it for the MRP system. It's time consuming, and the edits they make follow some simple rules, which screams "automation" to me.

A typical unedited Parts List export might look like this:

The steps the team members take are:

1. Delete "Description" and "Mass" columns entirely.
2. Delete any row with a blank cell in the "Keywords" iProperty column.
3. Add a column before the "ITEM" column titled "MATL"
4. Set the "MATL" column equal to 10X the "ITEM" column.

So the above "pre-edit" example would finish like this: 

That's it. Is it possible, if a "BOM.xls" or "BOM.xlsx" already exists a user's desktop, that an external iLogic rule called from a Global Form can add a sheet to the Excel file with the drawing filename (without extension) as the sheet name, pre-formatted as described above? It would save a lot of time in the long run. 

Thank you in advance to anyone who reads and/or helps! 

[JelteDeJong]

try this ilogic rule:

Public Class ThisRule

    Private workbook'  As Microsoft.Office.Interop.Excel.workbook
    Private sheet'  As Microsoft.Office.Interop.Excel.Worksheet
    Private myXLS_File = "c:\temp\MRP.xlsx"

    Sub Main()
        Dim doc As AssemblyDocument = ThisDoc.Document

        loadWorkSheet(doc.DisplayName)
        sheet.Range("A1").Value = "MATL"
        sheet.Range("B1").Value = "ITEM"
        sheet.Range("C1").Value = "QTY"
        sheet.Range("D1").Value = "Keywords"

        Dim i As Integer = 2
        For Each bomRow As BOMRow In doc.ComponentDefinition.BOM.BOMViews.Item("Structured").BOMRows

            Dim def As PartComponentDefinition = bomRow.ComponentDefinitions.Item(1)
            Dim cDoc As Document = def.Document

            Dim Keywords As String = getIProperty(cDoc, "Inventor User Defined Properties", "Keywords")

            If (String.IsNullOrWhiteSpace(Keywords) = False) Then
                sheet.Range("A" & i).Value = multiplyBy10(bomRow.ItemNumber)
                sheet.Range("B" & i).Value = bomRow.ItemNumber
                sheet.Range("C" & i).Value = bomRow.TotalQuantity
                sheet.Range("D" & i).Value = Keywords
            End If

            i = i + 1
        Next

        'workbook.Save()
        'workbook.Close()
    End Sub

    Private Function multiplyBy10(value As String) As String
        Dim Val = Convert.ToDecimal(value)
        Return Math.Round(val * 10, 0)
    End Function



    Private Function getIProperty(doc As Document, setName As String, propertyName As String) As String
        Dim prop As [Property]
        Try
            Dim propSet As PropertySet = doc.PropertySets.Item(setName)
            prop = propSet.Item(propertyName)
        Catch ex As Exception
            Return ""
        End Try

        Return prop.Value
    End Function

    Private Sub loadWorkSheet(sheetName As String)
        Dim excelApp  = CreateObject("Excel.Application") ' As Microsoft.Office.Interop.Excel.Application
        excelApp.Visible = True
        excelApp.DisplayAlerts = False

        If Dir(myXLS_File) <> "" Then
            workbook = excelApp.Workbooks.Open(myXLS_File)
        Else
            workbook = excelApp.Workbooks.Add
        End If

        Try
            sheet = workbook.Worksheets.Item(sheetName)
        Catch ex As Exception
            sheet = workbook.Worksheets.Add()
            sheet.Name = sheetName
        End Try
    End Sub

End Class

[claudio.ibarra]

When I try to run it from a drawing, I get this error:

I saw "Inventor.AssemblyDocument" and thought maybe I should run it from the assembly level.

When I try to run it from the drawing's assembly, it seems to get further -- the MRP.xlsx file opens before I get this error: 

Could this be because I'm using Inventor Pro 2018?

[JelteDeJong]

i was expecting that you wanted to run this from an assembly. Anyway you managed to figure out that the rule is supposed to be run from an assembly.

I think i spotted the problem in the rule. (i tested it only on an assembly with only parts.) Try to replace the line:

Dim def As PartComponentDefinition = bomRow.ComponentDefinitions.Item(1)

with

Dim def As ComponentDefinition = bomRow.ComponentDefinitions.Item(1)

let me know if that helpt.

[claudio.ibarra]

The good news -- it runs without error, it adds the column titles and sheet name. The bad news -- the columns are empty. 

[JelteDeJong]

Can you check the line:

Dim Keywords As String = getIProperty(cDoc, "Inventor User Defined Properties", "Keywords")

This line trys to get the value of property "Keywords". I assumed it is a custom iProperty and that is is written correct. can u check this.

you could also change the following line:

    Private Function getIProperty(doc As Document, setName As String, propertyName As String) As String
        Dim prop As [Property]
        Try
            Dim propSet As PropertySet = doc.PropertySets.Item(setName)
            prop = propSet.Item(propertyName)
        Catch ex As Exception
            Return "Exception in function 'getIProperty'. message: " & ex.Message
        End Try

        Return prop.Value
    End Function

[claudio.ibarra]

If I'm using the "Keywords" iProperty and not a custom iProperty, I imagine I will have to change this line?

getIProperty(cDoc, "Inventor User Defined Properties", "Keywords")

[claudio.ibarra]

This is the error I get after adding 

            Return "Exception in function 'getIProperty'. message: " & ex.Message

[JhoelForshav]

Hi @claudio.ibarra 

Keywords is in the property set "Inventor Summary Information". So this would probably work 🙂

getIProperty(cDoc, "Inventor Summary Information", "Keywords")

[claudio.ibarra]

It wrote to the XLS file! It looks great! 

Is it possible to manipulate it within the code to sort by item number and remove the blank rows?

[JhoelForshav]

Try this 🙂

Public Class ThisRule

    Private workbook'  As Microsoft.Office.Interop.Excel.workbook
    Private sheet'  As Microsoft.Office.Interop.Excel.Worksheet
    Private myXLS_File = "c:\temp\MRP.xlsx"

    Sub Main()
        Dim doc As AssemblyDocument = ThisDoc.Document

        loadWorkSheet(doc.DisplayName)
        sheet.Range("A1").Value = "MATL"
        sheet.Range("B1").Value = "ITEM"
        sheet.Range("C1").Value = "QTY"
        sheet.Range("D1").Value = "Keywords"
		
		Dim oBomView As BOMView = doc.ComponentDefinition.BOM.BOMViews.Item("Structured")
		oBomView.Sort("Item")
		InventorVb.DocumentUpdate()


        Dim i As Integer = 2
        For Each bomRow As BOMRow In oBOMView.BOMRows

            Dim def As PartComponentDefinition = BOMRow.ComponentDefinitions.Item(1)
            Dim cDoc As Document = def.Document

            Dim Keywords As String = getIProperty(cDoc, "Inventor Summary Information", "Keywords")

            If (String.IsNullOrWhiteSpace(Keywords) = False) Then
                sheet.Range("A" & i).Value = multiplyBy10(BOMRow.ItemNumber)
                sheet.Range("B" & i).Value = BOMRow.ItemNumber
                sheet.Range("C" & i).Value = BOMRow.TotalQuantity
                sheet.Range("D" & i).Value = Keywords
				i = i + 1
            End If

           ' i = i + 1
        Next

        'workbook.Save()
        'workbook.Close()
    End Sub

    Private Function multiplyBy10(value As String) As String
        Dim Val = Convert.ToDecimal(value)
        Return Math.Round(Val * 10, 0)
    End Function



    Private Function getIProperty(doc As Document, setName As String, propertyName As String) As String
        Dim prop As [Property]
        Try
            Dim propSet As PropertySet = doc.PropertySets.Item(setName)
            prop = propSet.Item(propertyName)
        Catch ex As Exception
            Return ""
        End Try

        Return prop.Value
    End Function

    Private Sub loadWorkSheet(sheetName As String)
        Dim excelApp  = CreateObject("Excel.Application") ' As Microsoft.Office.Interop.Excel.Application
        excelApp.Visible = True
        excelApp.DisplayAlerts = False

        If Dir(myXLS_File) <> "" Then
            workbook = excelApp.Workbooks.Open(myXLS_File)
        Else
            workbook = excelApp.Workbooks.Add
        End If

        Try
            sheet = workbook.Worksheets.Item(sheetName)
        Catch ex As Exception
            sheet = workbook.Worksheets.Add()
            sheet.Name = sheetName
        End Try
    End Sub

End Class

[claudio.ibarra]

I re-removed Part from PartComponentDefinition (is it a good sign when I recognize the error? I feel like I'm learning 😀 ), and all of the extra rows were removed! It's so close!

I still can't seem to figure out how it's determining the order. My assumption for what oBomView.Sort("Item") means is apparently wrong. 

[JhoelForshav · Accepted solution]

I thought it would work to sort the BOM view like that...

Tested another way now that doesn't make any changes to the BOM view. Instead i create a class for excel rows and adds objects of that class to a list that i then sort with respect to itemnumber. Works for me when i test it 🙂

Public Class ThisRule

	Private workbook'  As Microsoft.Office.Interop.Excel.workbook
	Private sheet'  As Microsoft.Office.Interop.Excel.Worksheet
	Private myXLS_File = "c:\temp\MRP.xlsx"
	Dim oList As New List(Of ExcelRowObject)
	Sub Main()
		Dim doc As AssemblyDocument = ThisDoc.Document

		loadWorkSheet(doc.DisplayName)
		sheet.Range("A1").Value = "MATL"
		sheet.Range("B1").Value = "ITEM"
		sheet.Range("C1").Value = "QTY"
		sheet.Range("D1").Value = "Keywords"

		Dim oBomView As BOMView = doc.ComponentDefinition.BOM.BOMViews.Item("Structured")
		Dim i As Integer = 2
		For Each bomRow As BOMRow In oBomView.BOMRows

			Dim def As ComponentDefinition = BOMRow.ComponentDefinitions.Item(1)
			Dim cDoc As Document = def.Document

			Dim Keywords As String = getIProperty(cDoc, "Inventor Summary Information", "Keywords")

			If (String.IsNullOrWhiteSpace(Keywords) = False) Then
				oList.Add(New ExcelRowObject(Keywords, BOMRow.ItemNumber, BOMRow.TotalQuantity, multiplyBy10(BOMRow.ItemNumber)))
			End If
		Next
		oList.Sort(Function(x, y) x.ItemNumber.CompareTo(y.ItemNumber))
		For Each oItem As ExcelRowObject In oList
			sheet.Range("A" & i).Value = oItem.MultiplyBy10
			sheet.Range("B" & i).Value = oItem.ItemNumber
			sheet.Range("C" & i).Value = oItem.TotalQuantity
			sheet.Range("D" & i).Value = oItem.Keywords
			i = i + 1
		Next
		'workbook.Save()
		'workbook.Close()
	End Sub

	Private Function multiplyBy10(value As String) As String
		Dim Val = Convert.ToDecimal(value)
		Return Math.Round(Val * 10, 0)
	End Function



	Private Function getIProperty(doc As Document, setName As String, propertyName As String) As String
		Dim prop As [Property]
		Try
			Dim propSet As PropertySet = doc.PropertySets.Item(setName)
			prop = propSet.Item(propertyName)
		Catch ex As Exception
			Return ""
		End Try

		Return prop.Value
	End Function

	Private Sub loadWorkSheet(sheetName As String)
		Dim excelApp = CreateObject("Excel.Application") ' As Microsoft.Office.Interop.Excel.Application
		excelApp.Visible = True
		excelApp.DisplayAlerts = False

		If Dir(myXLS_File) <> "" Then
			workbook = excelApp.Workbooks.Open(myXLS_File)
		Else
			workbook = excelApp.Workbooks.Add
		End If

		Try
			sheet = workbook.Worksheets.Item(sheetName)
		Catch ex As Exception
			sheet = workbook.Worksheets.Add()
			sheet.Name = sheetName
		End Try
	End Sub

End Class
Class ExcelRowObject
	Public Property Keywords As String
	Public Property ItemNumber As Integer
	Public Property TotalQuantity As Integer
	Public Property MultiplyBy10 As Integer
	Public Sub New(K, I, T, M)
		Keywords = K
		ItemNumber = I
		TotalQuantity = T
		MultiplyBy10 = M
	End Sub
End Class

[claudio.ibarra]

I'm going to mark your most recent code as a solution because it works! I don't know if the MRP system will care about the way it puts 10-19 between 1 and 2, instead of 1-9 then 10-19. That's something I will have to test. 

Thank you so much for your help!! 

[JhoelForshav]

The 10-19 thing sounds odd... Are you sure you tried the latest code?

I first uploaded a code that I think might do that because i used strings instead of integers to sort.

But I edited that code so I think it should work now?

Try copying the code again 🙂

[claudio.ibarra]

I thought I had it, but when I re-copied and re-ran it, it ended up in order! Thank you so much!! 

[JhoelForshav]

Im happy to help!

We shouldn't forget that @JelteDeJong did a lot of the work here as well 🙂

[bsnyderACLUW]

@JhoelForshav @claudio.ibarra @JelteDeJong 

Hey everyone, 

I know this forum is a bit old but I figured I should post anyways.

I am currently trying to get this to work in Inventor 2020 but the excel file has nothing but the titles in it. I know this was mentioned earlier in the post but I have used the most updated code here at the bottom of the page and tried everything I could find in this forum. I have also removed everything but one column of "values" and played with the code trying to get something to work. Yet, I got no where. Any help will be appreciated. 

Thanks, 

[A.Acheson]

@bsnyderACLUW @JhoelForshav @claudio.ibarra @JelteDeJong

Just had a quick look at this. this export is very specific to the iproperty keyword in each part. If that iproperty is empty no BOM values  will be exported. Only the column titles will be created in the excel sheet. The below snippet controls this behavior. 

Dim oBomView As BOMView = doc.ComponentDefinition.BOM.BOMViews.Item("Structured")
		Dim i As Integer = 2
		For Each bomRow As BOMRow In oBomView.BOMRows

			Dim def As ComponentDefinition = BOMRow.ComponentDefinitions.Item(1)
			Dim cDoc As Document = def.Document

			Dim Keywords As String = getIProperty(cDoc, "Inventor Summary Information", "Keywords")

			If (String.IsNullOrWhiteSpace(Keywords) = False) Then
				oList.Add(New ExcelRowObject(Keywords, BOMRow.ItemNumber, BOMRow.TotalQuantity, multiplyBy10(BOMRow.ItemNumber)))
			End If
		Next

 You will need to provide more details on what BOM values you want to export.  as your intended workflow could be very different to this one.  

Hope this helps.

[bsnyderACLUW]

@A.Acheson 

Thanks for replying,

The items I want to export is the quantity, part number, description, and material.

I figured I could replace "Keywords" with "Material" or MaterialType" across the board as I don't need or use the Iproperty Keywords but I still don't get anything outputting to the excel sheet.  I don't know if if I have the right syntax but it does run without error. 

Thanks,

EDIT:

I have also injected a numerical value into the Keywors Iproperty of all the parts in my assembly and ran the program in this forum but i still get no output. 

Thanks,