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
Solved! Go to Solution.
Solved by A.Acheson. Go to Solution.
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.
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.
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.
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
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 ?
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.
Can't find what you're looking for? Ask the community or share your knowledge.