iLogic to Export BOM from Assembly to an Excel template – Parts Only

[Anonymous]

Hello everyone,

I was found this code sometimes ago and it works great. From Assembly exported BOM Parts only in Excel file arranged as I wish.

There I need to make just one update.  I want to import this BOM to Excel template. Now imports it in raw Excel file.

Many thanks for any suggestion and help.

AddReference "Microsoft.Office.Interop.Excel" 'To use excel
Imports System.Windows.Forms
Imports System.IO
Imports Microsoft.Office.Interop.Excel        'To use excel

Sub Main()
	Dim oDoc As AssemblyDocument = ThisApplication.ActiveDocument

	
	ExportBOM(oDoc)
End Sub

Private BOMCustomizationFile As String = "C:\Users\BOM Export - 01.xml"

Private excelApp As Microsoft.Office.Interop.Excel.Application
Private xlws As Worksheet

Sub ExportBOM(oDoc As Document)

'File Path Creation/Processing
	'Note, the following line will cause an error if the document is not saved.
	Dim oExportPath As String = System.IO.Path.GetDirectoryName(oDoc.FullFileName) & "\BOM\"
	Dim oExportName As String = oExportPath & System.IO.Path.GetFileNameWithoutExtension(oDoc.FullFileName)& "-BOM" 'without extension'get BOM Target folder path
	
	If Not System.IO.Directory.Exists(oExportPath) Then: System.IO.Directory.CreateDirectory(oExportPath): End If

	If Dir(oExportName & ".xlsx") <> "" Then
		Kill (oExportName & ".xlsx")
	End If 

'Inventor BOM Processing
	Dim oBOM As BOM = oDoc.ComponentDefinition.BOM

	oBOM.ImportBOMCustomization(BOMCustomizationFile)
	'oBOM.StructuredViewEnabled = True
	oBOM.PartsOnlyViewEnabled = True
	'oBOM.StructuredViewFirstLevelOnly = False
	oBOM.PartsOnlyViewNumberingScheme = kNumericNumbering
	
	'Dim oStructuredBOMView As BOMView
	'oStructuredBOMView = oBOM.BOMViews.Item("Structured")
	'oStructuredBOMView.Export(oExportName & ".xlsx", kMicrosoftExcelFormat)
	
	Dim oPartsOnlyBOMView As BOMView
	oPartsOnlyBOMView = oBOM.BOMViews.Item("Parts Only")
	oPartsOnlyBOMView.Export (oExportName & ".xlsx", kMicrosoftExcelFormat)
	
'Excel processing
	excelApp = CreateObject("Excel.Application")
	excelApp.Visible = True
	excelApp.DisplayAlerts = False
	
	wb = excelApp.Workbooks.Open(oExportName & ".xlsx")
	xlws = wb.Worksheets(1)
	
	Call ReorderXLBOM()
	
	excelApp.Columns.AutoFit
	excelApp = Nothing

End Sub

Private Sub ReorderXLBOM()
    Dim ndx As Integer
    Dim Found As Range
    Dim counter As Integer = 1

	Dim arrColOrder() As String = {"Part Number", "REV", "Title", "Subject", "Keywords", "Dimenzije surovca", "Dolzina", "Category", "Comments", "Prva obdelava", "BOM Structure", "Thumbnail", "QTY", "Material", "Cert_materiala", "Obd_notranja", "Obd_zunanja", "Status"}
    'arrColOrder = Array("Item", "QTY", "Part Number", "Description", "Stock Number")

	On Error Resume Next

    For ndx = LBound(arrColOrder) To UBound(arrColOrder)

        'Set Found = xlws.Rows("1:1").Find(arrColOrder(ndx),, LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByColumns, SearchDirection:=xlNext, MatchCase:=False)
        Found = xlws.Rows("1:1").Find(arrColOrder(ndx), , -4163, 1, 2, 1, False)
    
        If Err.Number <> 0 Then
            MsgBox ("Error With Excel FIND function: " & Err.Number & " :: " & Err.Description & vbLf & vbLf & ndx)
            Err.Clear
        End If

        If Not Found Is Nothing Then
                If Found.Column <> counter Then
                        Found.EntireColumn.Cut
                        xlws.Columns(counter).Insert(-4161)
                        excelApp.CutCopyMode = False
                End If
                counter = counter + 1
        End If
    Next
    
    If Err.Number <> 0 Then
            MsgBox ("Reorder Columns Rule Error: " & Err.Number & " :: " & Err.Description & vbLf & vbLf & ndx)
            Err.Clear
    End If   
End Sub

Public Function oFolderDlg
    Dim dialog = New FolderBrowserDialog()
'    dialog.SelectedPath = Application.StartupPath
	dialog.ShowNewFolderButton = True
'	openFileDialog1.InitialDirectory 
    If DialogResult.OK = dialog.ShowDialog() Then
        oPath = dialog.SelectedPath
	Else
		MsgBox("No File Selected. Aborting Rule")
		oPath = ""
    End If
	Return oPath
End Function

All the best,

Jernej Puc 

[A.Acheson]

I don't think there is an easy method to send the BOM to a template directly. The below can be integrated into your rule and will just copy the sheet from the BOM Export to your template. Change extensions etc as required. 

AddReference "Microsoft.Office.Interop.Excel" 'To use excel
Imports System.Windows.Forms
Imports System.IO
Imports Microsoft.Office.Interop.Excel        'To use excel

Sub Main
'Reference the file location	
Dim oBOMExport As String
oBOMExport = "C:\Users\Desktop\WFH\BOM\BOM Export.xlsx"

Dim oTemplate As String
oTemplate = "C:\Users\Desktop\WFH\BOM\External BOM Template.xlsx"
	
Dim xlws_oBOMExport As Worksheet
Dim xlws_oTemplate As Worksheet

Dim xlApp As Object
xlApp = CreateObject("Excel.Application")

'Open BOMExport Workbook
Dim xlwb_oBOMExport As Workbook
xlwb_oBOMExport = xlApp.Workbooks.Open(oBOMExport)

'Open Template Workbook
Dim xlwb_oTemplate As Workbook
xlwb_oTemplate = xlApp.Workbooks.Open(oTemplate)

'Open Excel App
xlApp.Visible = True

'Copy Between
xlwb_oBOMExport.Worksheets("Sheet1").Copy(After :=xlwb_oTemplate.Worksheets("Sheet1"))

xlwb_oTemplate.save

End Sub

 I am using a similar code to yours but combine ilogic and excel VBA to work together. I create the BOM export and save to disk, then using ilogic go excel open the template and populated a cell. This triggers the macro enable excel template to run and copy the sheet, then  other excel macro runs to sort and filter BOM based on categories. It is a workaround if your not familiar with manipulating excel via VB.Net. 

[Anonymous]

Hello,

Thank you very much for your kindly reply and for rule. This is not ecexly what I looking for.

But you helped me a little anyway. Thank you for your time 🙂

In my case I have a little more complexing template. Because I have a lot of cells fulfilled and preseted.

From this reason I need to copy completely BOM to into a specific cells.

All the best,

Jernej Puc

[A.Acheson]

You should be able to utilize excel range method this will  preserve your formatting in the template.

https://docs.microsoft.com/en-us/office/vba/api/excel.range.copy

This should is possible in your rule. I am not a whizz kid on this by any means mostly learned through trial an error. But other users know this quite well. So if you need more help just post here.

[Anonymous]

Thank you very much for this information.

I am very grateful to you.

Wish you a nice day,

Jernej Puc