Export BOM to excel - including parts / assemblies set to 'Reference' BOM structure

[Erumb_Tony]

Firstly I am new to this forum and to i-logic, so my code may be sloppy or unnecessary (please bear with it). We are working on a file clean-up activity in our organization. It requires us to check large assemblies, part-by-part for incorrect i-properties, unconstrained sketches, wrong file locations, etc. We have been working on this by exporting the top-level assembly's Structured BOM (with 'All levels' and a delimiter, so that it is easy to understand and work with the exported BOM).

The problem is that many users have (by mistake) set purchased items and sometimes even fabrication items to "Reference" in top assembly and many of its sub-assemblies. The result is that they get dropped out of our exported Excel spreadsheet. The spreadsheet data is used for many other subsequent processes, so we need the BOM to contain all of the data, irrespective of what BOM structure it is.

To get this done easily, I have created an i-logic code that will pull out the part number, title of the reference items into an Input Box, so that I can copy them into an Excel file, use the 'Convert Text to Column Wizard' in Excel to split these two entries and then copy-paste them onto the main BOM spreadsheet.

The process is much quicker than manual entry, but it would be better if the code can just do the entire work - scan through the entire assembly, check each sub-assembly, look for reference items, add them to the part list along with the item no. (with delimiter) and then export the whole structured BOM to an Excel file. Any help will be appreciated!

This is the code I have: 

Dim AssyDoc As AssemblyDocument
Dim AssyCompDef As AssemblyComponentDefinition
Dim Occs As ComponentOccurrences

AssyDoc = ThisApplication.ActiveDocument
AssyCompDef = AssyDoc.ComponentDefinition
Occs = AssyCompDef.Occurrences

Dim OccCount As Integer 'Total number of occurrences
Dim RefOccCount As Integer 'Total number of occurences that are set to reference
Dim i As Integer 'Iteration integer for occurrences
Dim j As Integer 'Iteration integer for occurrences that are set to reference
j = 1
RefOccCount = 0
OccCount = Occs.Count

For i = 1 To OccCount
Dim Occ As ComponentOccurrence
Occ = Occs.Item(i)
If Occ.BOMStructure = kReferenceBOMStructure
RefOccCount = RefOccCount + 1
Else
End If
Next

If RefOccCount < 1
MsgBox("No Reference items found !", vbOKCancel,"i-Logic")
Else

Dim RefList(RefOccCount) As String

For i = 1 To OccCount
Dim Occ As ComponentOccurrence
Occ = Occs.Item(i)
If Occ.BOMStructure = kReferenceBOMStructure

Dim OccDocCD As ComponentDefinition
Dim OccDoc As Document
Dim PropSets As PropertySets
Dim PropSet1 As PropertySet
Dim PropSet2 As PropertySet
Dim PNo As [Property]
Dim OccTitle As [Property]

OccDocCD = Occ.Definition
OccDoc = OccDocCD.Document

PropSets = OccDoc.PropertySets
PropSet1 = PropSets.Item("Design Tracking Properties")
PropSet2 = PropSets.Item("Inventor Summary Information")

PNo = PropSet1.Item("Part Number")
OccTitle = PropSet2.Item("Title")

RefList(j) = j & "|" & PNo.Value & "|" & OccTitle.Value
j = j + 1

Else
End If
Next

Dim FinalText As String
FinalText = Join (RefList, vbCrLf)

InputBox("List of Reference Items:", "i-Logic", FinalText)

End If

[A.Acheson]

---

The process is much quicker than manual entry, but it would be better if the code can just do the entire work - scan through the entire assembly, check each sub-assembly, look for reference items, add them to the part list along with the item no. (with delimiter) and then export the whole structured BOM to an Excel file. Any help will be appreciated!

---

Could you explain a little more what the workflow is and where you would like some help. The code posted doesn't have any excel interaction. Do you want help implementing that? You mentioned adding to the parts list. Is that to the drawing parts list? I would think you would be changing the assembly and the BOM adjust accordingly and not the parts list correct? The parts list will update once the BOM has been changed. How are you currently exporting the BOM by code? There are many different methods with adv/disadv. 

[Erumb_Tony]

@A.Acheson 

Yes, I do need help with excel interaction. I must have wrongly used the 'parts list' term (what we need has nothing to do with the drawing parts list), so it is just the Assembly BOM that I wanted to imply.

Currently, I am not exporting the Structured Assembly BOM by code, it is done by the usual method. Once that is done, I'd use a filter on that Excel to show only the Assembly files. Then I'd manually open each one of those assemblies on Inventor, run this i-logic rule that will identify and pull out the part number and title fields from reference items that are in that assembly. I'd copy paste what that code turns in, to the main Excel file. If the code can export the structured BOM into Excel, include the reference items, that would be great. Hope I answered your question.

[A.Acheson]

Would you want to switch the BOM structure back to Default and then publish the BOM via the Structured/Parts Tabs? This would be the easiest way without putting in a lot of work but I understand this might affect other assemblies and you maybe want to control the changing of these assemblies on a case by case basis. 

[A.Acheson · Accepted solution]

Try this out to see if it works for you. It is a basic BOM export with some excel interaction to add the referenced document information to the end of the sheet.

Option Explicit On
AddReference "microsoft.office.interop.excel.dll"
Imports XL = Microsoft.Office.Interop.Excel

Sub Main

    ' Get path of current Assy File
	Dim oPath As String = ThisDoc.Path

	Dim xlBOMPath As String  = oPath  & "\" & "BOM" & ".xlsx"
	MessageBox.Show(xlBOMPath, "Title")

	' Check if file exists, if it does then delete 
	If System.IO.File.Exists(xlBOMPath)Then
		System.IO.File.Delete(xlBOMPath)
	End If

	'  a reference to the assembly document.
	' This assumes an assembly document is active.
	Dim oDoc As AssemblyDocument = ThisApplication.ActiveDocument
	
	'  a reference to the BOM
	Dim oBOM As BOM = oDoc.ComponentDefinition.BOM

	'  the structured view to 'all levels'
	oBOM.StructuredViewFirstLevelOnly = False

	oBOM.StructuredViewDelimiter = "-"

	' Make sure that the structured view is enabled.
	oBOM.StructuredViewEnabled = True

	'  a reference to the "Structured" BOMView
	Dim oStructuredBOMView As BOMView = oBOM.BOMViews.Item("Structured")

	' Export the BOM view to an Excel file
	oStructuredBOMView.Export( xlBOMPath, kMicrosoftExcelFormat)
	
	'[ work with Parts Only BOM
	' Make sure that the parts only view is enabled.
	'oBOM.PartsOnlyViewEnabled = True

	' a reference to the "Parts Only" BOMView
	' Dim oPartsOnlyBOMView As BOMView = oBOM.BOMViews.Item("Parts Only")

	' Export the BOM view to an Excel file
	' oPartsOnlyBOMView.Export (myXLS_File, kMicrosoftExcelFormat)
	']
	
	'[Open the Excel Workbook
	Dim xlApp As XL.Application = CreateObject("Excel.Application")
	Dim xlWb As XL.Workbook
	
	'comment out or change to false in order to not show Excel
	xlApp.Visible = True 

	If IO.File.Exists(xlBOMPath) = True Then
		xlWb = xlApp.Workbooks.Open(xlBOMPath)
	End If

	Call ReferenceDocuments(xlApp)
		
	xlApp.Columns.AutoFit 
	'xlWb.Save
	'xlWorkbook.Close (False)
	'xlApp.Quit
']
	
End Sub

Sub ReferenceDocuments(xlApp)
	
	Dim AssyDoc As AssemblyDocument = ThisApplication.ActiveDocument
	Dim AssyCompDef As AssemblyComponentDefinition = AssyDoc.ComponentDefinition

	Dim RefOccCount As Integer = 0 'Total number of occurences that are set to reference

		For Each Occ As ComponentOccurrence In AssyCompDef.Occurrences
			If Occ.BOMStructure = BOMStructureEnum.kReferenceBOMStructure
				Dim OccDocCD As ComponentDefinition = Occ.Definition
				Dim OccDoc As Document = OccDocCD.Document
				Dim PropSets As PropertySets = OccDoc.PropertySets
				Dim PropSet1 As PropertySet = PropSets.Item("Design Tracking Properties")
				Dim PropSet2 As PropertySet = PropSets.Item("Inventor Summary Information")
				Dim PNo As [Property]= PropSet1.Item("Part Number")
				Dim OccTitle As [Property] = PropSet2.Item("Title")

				Dim DocType As String
				If OccDoc.DocumentType.ToString.Contains("Part") Then
					 DocType = "Part"
				ElseIf OccDoc.DocumentType.ToString.Contains("Assembly") Then
					DocType = "Assembly"
				End If
			
				RefOccCount = RefOccCount + 1
				
				' Add Title only once in sheet
				If RefOccCount = 1 Then
					Call XLAddRefTitle(xlApp)
				End If
				
				Call XLAddRow(RefOccCount, PNo.Value, OccTitle.Value, DocType, xlApp)
				
			End If
		Next
		If RefOccCount < 1 Then
			MsgBox("No Reference items found !", vbOKCancel,"i-Logic")
		End If
End Sub

Sub XLAddRefTitle(xlApp As XL.Application)
	Dim xlWs As XL.Worksheet = xlApp.ActiveWorkbook.ActiveSheet
	Dim oRange As xl.Range = xlApp.Cells(xlWs.Rows.Count, 1)
	Dim LastRow As Integer = oRange.End(xl.XlDirection.xlUp ).Rows.Row
	xlWs.Range("A" & LastRow+1).Value = "DOCUMENTS WITH REFERENCED BOM STRUCTURE"

	'Modify the Range with some formatting
	With xlWs.Range("A" & LastRow+1 & ":" & "K" & LastRow+1)
	    .Merge
	    .HorizontalAlignment = XL.Constants.xlCenter
	    .VerticalAlignment = XL.Constants.xlCenter
		.Interior.ColorIndex = 3
	End With
	
	'Add Titles in case the BOM Title Changes
	LastRow  = oRange.End(xl.XlDirection.xlUp ).Rows.Row
	xlWs.Range("A" & LastRow + 1).Value = "ITEM"
	xlWs.Range("B" & LastRow + 1).Value = "PART NUMBER"
	xlWs.Range("C" & LastRow + 1).Value = "TITLE"
	xlWs.Range("D" & LastRow + 1).Value = "DOCUMENT TYPE"
End Sub

Sub XLAddRow(ByVal Item As Integer, ByVal PN As String, ByVal Title As String, DocType As String,xlApp As XL.Application)
	Dim xlWs As XL.Worksheet = xlApp.ActiveWorkbook.ActiveSheet
	Dim oRange As xl.Range = xlApp.Cells(xlApp.ActiveWorkbook.ActiveSheet.Rows.Count, 1)
	Dim LastRow As Integer = oRange.End(xl.XlDirection.xlUp).Rows.Row
	xlWs.Range("A" & LastRow + 1).Value = Item
	xlWs.Range("B" & LastRow + 1).Value = PN
	xlWs.Range("C" & LastRow + 1).Value = Title
	xlWs.Range("D" & LastRow + 1).Value = DocType
End Sub

[Erumb_Tony]

@A.Acheson 

Sorry for the late reply. I tried your code and it works ! Thanks for your reply. The comments you entered in your code made me understand much more than I thought I would. So, thanks again !

I am learning to code with vb.net and it would be great to know what the lines 1,2 & 3 do. Are they necessary when working with Excel ?

[A.Acheson]

So "Option Explicit On" is an optional statement. I like to use it in these more complicated codes to ensure mistakes are seen up front before code is run. 

Here is the official documentation here and below the description of its use.

"When Option Explicit On or Option Explicit appears in a file, you must explicitly declare all variables by using the Dim or ReDim statements. If you try to use an undeclared variable name, an error occurs at compile time. The Option Explicit Off statement allows implicit declaration of variables"

As for the reference and import lines. These are required  as they allow declaration of excel objects within the vb.net environment. From the dll (dynamic Link Library)

AddReference "microsoft.office.interop.excel.dll"
Imports XL = Microsoft.Office.Interop.Excel

 Try and commented them out and see what impact they have on the rest of your code. 

As for the BOM export code that is found here in the API help if you need further explanations or where the code is coming from. 

[Erumb_Tony]

Thanks a lot !