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
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.
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
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.
Can't find what you're looking for? Ask the community or share your knowledge.