topic Re: iLogic BOM Export Column Order in Inventor Programming Forum

iLogic BOM Export Column Order

danmorick — Mon, 20 Jul 2015 17:53:10 GMT

Hi,

I am attempting to use ImportBOMCustomization to prepare the assembly BOM for automatic export. What I need is a certain collection of columns in a certain order in Excel format. But when I export the BOM through iLogic, the resulting .xls has the correct collection of properties but for some reason it reorders the columns alphabetically; i.e., the first column heading based on alphabetical order shows up in column A. So for example my column headings from left to right have been reordered to "Description, File Path, Item, Part Number, Unit Quantity", which does not correspond to the order in my custom .xml. I do not get this reordering when I import the customization .xml and export the BOM manually through Inventor.

Is there some setting I am unaware of to prevent this?

Thanks for any input.

Re: iLogic BOM Export Column Order

Anonymous — Fri, 05 Feb 2016 21:42:40 GMT

Hi,

I have the same issue with Inventor Professional 2016.

If you would do the export manually from the BOM window, it will export it in the right order. But when exporting with the VBA Macro it will change the columns order.

Is there any information Autodesk could share with us?

Thanks

Horacio

Re: iLogic BOM Export Column Order

Anonymous — Mon, 20 Mar 2017 22:04:23 GMT

Hi There,

I am trying to achieve the same also.

I have already set up my BOM in the column sequence I want and when I export manually through the application window, it exports out perfect.

However when using an iLogic driven rule to export the BOM, the columns in the output file are automatically placed in alphabetical order.

Can someone please refer us to a solution on the forum if it has already been solved as I cant seem to find it.

Thanks, Ash

Re: iLogic BOM Export Column Order

Anonymous — Thu, 18 May 2017 04:20:58 GMT

Hi there everybody.

It seems this issue is common to all of us.

I found out the same issue in Inventor 2015 when exporting from a top level assembly. If I do from a normal assembly with no subassemblies, export goes fine, exactly as inventor would do by hand, but if done from the main assembly, with subassemblies, I have got the same issue you mention.

No patch up to today it seems, so no other workaround than updating to a newer version?

Kindly yours,

Jorge Hevia

Re: iLogic BOM Export Column Order

MechMachineMan — Thu, 18 May 2017 17:49:47 GMT

Hi. Please see this other thread with a viable solution posted.

https://forums.autodesk.com/t5/inventor-customization/thisbom-export-column-order-problem/m-p/4902030/highlight/true#M49080

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
	
	'Dim oPath As String
	'oPath = oFolderDlg
	'oFileName = oPath & "Bruce.xml"
	
	ExportBOM(oDoc)
End Sub

Private BOMCustomizationFile As String = "C:\CustFile.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) '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.StructuredViewFirstLevelOnly = False
	
	Dim oStructuredBOMView As BOMView
	oStructuredBOMView = oBOM.BOMViews.Item("Structured")
	oStructuredBOMView.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 = {"Item", "QTY", "Part Number", "Description", "Stock Number"}
    '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