topic Re: ThisBOM .export column order problem. in Inventor Programming Forum

ThisBOM .export column order problem.

tdipilla — Fri, 21 Mar 2014 19:53:49 GMT

I have an iLogic routine using the ThisBOM.export function. I am using the “parts only” format for my export. The format that ends up in my excel file has the columns in alphabetical order based on the column header. This is different than the layout in the part only view. What drives the column order in the ThisBOM function and can it be altered or controlled? Thanks

Re: ThisBOM .export column order problem.

adam.nagy — Wed, 09 Apr 2014 20:30:54 GMT

Hi,

I seem to remember that the order in the UI and through the API are not kept in sync. This thread says it too:

http://208.74.205.69/t5/Inventor-Customization/Export-Structured-BOM-view-with-All-Levels/td-p/3737084

You can do some sorting on the BOMView, but that as well might not be refelected back in the UI - it should have an effect on the Export function though.

Cheers,

Re: ThisBOM .export column order problem.

mslosar — Thu, 10 Apr 2014 13:42:49 GMT

I had a similar issue - exporting the structured view. Seems no matter what I did it always came out sorted by the model view tab. Turns out as of 2013 it was a bug in the SDK and someone else had reported it as well. I was supposed added to the list to be informed about updates on it but i've not heard anything. Of course that's just been about 2 months and they've been wrapping up 2015 in that time frame so I don't read much into no hearing anything yet.

Re: ThisBOM .export column order problem.

adam.nagy — Thu, 10 Apr 2014 13:56:09 GMT

Hi,

Did you get the change request ID of that?

Cheers,

Adam

Re: ThisBOM .export column order problem.

tdipilla — Thu, 10 Apr 2014 15:58:23 GMT

Thankyou for the input. I ended up just running a sort routines in the ilogic routine i have to format the final spreadsheet as required.

Re: ThisBOM .export column order problem.

mcloughlin_b — Tue, 02 May 2017 05:27:34 GMT

Can you advise how to do the sort routine on the created excel file. I am having the same issue where BOM is exported but excel columns are sorted alphabetically.

Thanks,

Bryan

Re: ThisBOM .export column order problem.

MechMachineMan — Tue, 02 May 2017 17:20:45 GMT

Depending on how you are doing it... the attached code is VBA to reorder columns.

Note:

It must have xlws set beforehand as a global variable.
It also only sorts if the data is on the first row.
You can change array to make it sort by a different order.
Words used in the array must match what is in the excel file.

Private Sub ReorderXLBOM()
    Dim arrColOrder
    Dim ndx
    Dim Found
    Dim counter

    arrColOrder = Array("Item", "QTY", "Part Number", "Description", "Stock Number")
    counter = 1

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)
        Set 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
                        Application.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

Re: ThisBOM .export column order problem.

mcloughlin_b — Thu, 04 May 2017 04:51:07 GMT

Hi Justin

My coding prowess is sketchy at best so can you please have a squizz at my code and advise how to add your VBA in to achieve the objective. The code is ilogic which I have pieced together. I basically import a custom BOM into the assembly (which has the columns as required) then export. I'm sure there is a more efficient way but it works, excepting for the reordering of columns.

Many thanks for your time.

Bryan

SyntaxEditor Code Snippet

Imports System.Windows.Forms
Imports System.IO

Sub Main()
' Get the active assembly
Dim oDoc As AssemblyDocument = ThisApplication.ActiveDocument

'set  a reference to the BOM
Dim oBOM As BOM = oDoc.ComponentDefinition.BOM

'Get user path for custom BOM
Dim oPath As String
    ' Search for the folder
    Dim Dialog = New FolderBrowserDialog()
    Dialog.ShowNewFolderButton = True
    Dialog.Description = "Choose Custom BOM Folder"

    ' Show dialog box
    If DialogResult.OK = Dialog.ShowDialog() Then
        ' User clicked 'ok' on dialog box - capture the export path
        oPath = Dialog.SelectedPath & "\"
    
    Else
        ' User clicked 'cancel' on dialog box - exit
        Return
    End If

'import custom BOM
Dim ofilename As String 'assume filename exists
ofilename = oPATH & "BRUCE EXPORT.xml"
MessageBox.Show("Custom BOM is:" & ofilename, "Title")
Call oBom.ImportBOMCustomization(ofilename)

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

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

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

'End Sub

' Export the BOM view to an Excel file
Dim ExportPath As String = ThisDoc.Path
Dim oExportName = ThisDoc.FileName(False) 'without extension'get BOM Target folder path
Dim oFolderBOM As String = Strings.Left(ExportPath, InStrRev(ExportPath, "\")) & "BOM"
MessageBox.Show("Export Path is:" & oFolderBOM, "Title")
'Check for the BOM folder and create it if it does not exist
 If Not System.IO.Directory.Exists(oFolderBOM) Then
         System.IO.Directory.CreateDirectory(oFolderBOM)
 End If
 'check For existing XLSX file And delete it If found
            If Dir(oFolderBom & "\" & oExportName & ".xlsx") <> "" Then
            Kill (oFolderBom & "\" & oExportName & ".xlsx")
                Else
            End If 

oStructuredBOMView.Export (oFolderBOM & "\" & oExportName & ".xlsx", kMicrosoftExcelFormat)

'define Excel Application object
excelApp = CreateObject("Excel.Application")
'set Excel to run visibly'change to false if you want to run it invisibly
excelApp.Visible = True

'open the workbook
wb = excelApp.Workbooks.Open(oFolderBOM & "\" & oExportName & ".xlsx")
'set all of the columns to autofit
excelApp.Columns.AutoFit
'suppress prompts (such as the compatibility checker)
excelApp.DisplayAlerts = False
'save the workbook
wb.Save

End Sub
'close the workbook, uncomment if you want to close the xls file at the end'wb.Close

Re: ThisBOM .export column order problem.

MechMachineMan — Thu, 04 May 2017 17:33:04 GMT

Here is a heavily reworked version of your code.

Note, the intent is for the BOM customization to be located in one location and then just imported from there.

Also note that the BOM order is set by an array of strings that corresponds to the column titles.

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

Re: ThisBOM .export column order problem.

mcloughlin_b — Fri, 05 May 2017 09:26:21 GMT

Hi Justin

That's genius, many thanks.

Bryan

Re: ThisBOM .export column order problem.

Cadkunde.nl — Tue, 09 May 2017 11:29:02 GMT

Brilliant code, I want to give you kudo's but I can click on the button all I want, nothing works (im signed in)

Edit: Ok, I had to click the icon, not the text, even though it looks like you can also click on the text. First time that has me fooled

Re: ThisBOM .export column order problem.

Anonymous — Thu, 20 Jul 2017 13:43:45 GMT

I get this error:

Excel cannot open the file 'A00001.xlsx' because the file format or file extension is not valid. Verify that the file has not been corrupted and that the file extension matches the format of the file.

What am I missing?

Re: ThisBOM .export column order problem.

adam.nagy — Thu, 20 Jul 2017 14:08:11 GMT

Maybe Inventor 2015 can only save to xls and not xlsx?

Try renaming the file to xls and open it in Excel.

Re: ThisBOM .export column order problem.

Anonymous — Thu, 20 Jul 2017 14:42:07 GMT

@adam.nagy thanks Adam, that did the trick!

Re: ThisBOM .export column order problem.

Anonymous — Thu, 20 Jul 2017 15:45:52 GMT

How possible is it to modify this create from an existing XLS template instead?

Re: ThisBOM .export column order problem.

adam.nagy — Thu, 20 Jul 2017 15:55:46 GMT

If you simply mean making the above code work with XLS, then you just have to replace all the ".xlsx" strings with ".xls"

Re: ThisBOM .export column order problem.

Anonymous — Thu, 20 Jul 2017 16:05:44 GMT

No sorry, I did that and its working as expected.

I was wondering if its possible to adapt the code to use a pre-existing excel template

Curtis' blog shows how to do it through a drawing part list:

http://inventortrenches.blogspot.co.uk/2011/06/ilogic-export-parts-list-with-options.html

But I need a rule that will export all levels of the structured BOM (ideally to a template which will have my costing columns on it)

Re: ThisBOM .export column order problem.

adam.nagy — Mon, 24 Jul 2017 14:31:24 GMT

From Curtis' code it looks like you'll just have to add a third parameter to the Export function:

oOptions = ThisApplication.TransientObjects.CreateNameValueMap


'specify an existing template file
'to use For formatting colors, fonts, etc

oOptions.Value("Template") = "C:\Temp\PartListExport.xls"
oStructuredBOMView.Export (oExportName & ".xlsx", kMicrosoftExcelFormat, oOptions)

Cheers,

Re: ThisBOM .export column order problem.

Anonymous — Tue, 21 Nov 2017 15:43:10 GMT

It's really amazing how random that inventor excel export winds up, so I have reluctantly added MMM's column sorting code into my export "program":

'heavily reformat exported xls...
'On Error Resume Next
Set excel_app = CreateObject("Excel.Application")
'excel_app.Visible = True
excel_app.Workbooks.Open (bomname)
Dim rows As Integer
Dim rowbottom As String
rows = excel_app.ActiveSheet.UsedRange.rows.Count
rowbottom = "A1" & ":" & "A" & rows
excel_app.Range("A1").EntireColumn.Insert
excel_app.Range(rowbottom) = "=TRIM(LEFT(SUBSTITUTE(MID(CELL(""filename"",A1),FIND(""["",CELL(""filename"",A1))+1,255),"".xl"",REPT("" "",255)),255))"
excel_app.Range("F1").EntireColumn.Insert
excel_app.Range("F1") = "Assembly Qty"
excel_app.Range("A1") = "Order"

'try some sorting
Dim arrColOrder
    Dim ndx As Integer
    Dim Found As Range
    Dim counter As Integer
    arrColOrder = Array("Order", "Item", "Part Number", "BOM Structure", "QTY", "Assembly Qty", "A1 Router", "A1 Time", "B1 Laser", "B1 Time", "C1 Saw-AL", "C1 Time", "D1 Saw-ST", "D1 Time", "E1 C-Axis", "E1 Time", "F1 Machining", "F1 Time", "G1 Debur", "G1 Time", "H1 Brake", "H1 Time", "I1 Weld", "I1 Time", "J1 RWeld", "J1 Time", "K1 Assembly", "K1 Time", "L1 Outsource", "L1 Time", "M1 Ship", "Bulk", "Setup")
    counter = 1

On Error Resume Next

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

'was not successful setting global variable for worksheet, tried some bfi:

        'Set Found = Sheet1.rows("1:1").Find(arrColOrder(ndx), , LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByColumns, SearchDirection:=xlNext, MatchCase:=False)
        Set Found = Sheet1.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
                        Application.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

excelApp.Columns.AutoFit
excel_app.ActiveWorkbook.Save
excel_app.ActiveWorkbook.Close

I believe if I roll back to xlws in this code instead of "Sheet1" and Public xlws as string, xlws = Sheet1, this would work, but I don't know where to declare these global variables!!

When this runs at best, I get error with excel find 424: object required - obviously the arrayed names aren't being passed.

I tried making a new otherwise empty module where I set xlws as a public string, that works, gets me a different error of invalid qualifier for the set found = xlws.rows line. I can dim xlws as Sheet1 inside of the sub, get the same result.

I'm sure I'll learn something from this... Thanks everyone.

Re: ThisBOM .export column order problem.

MechMachineMan — Tue, 21 Nov 2017 15:42:57 GMT

Try this instead:

Feed it the worksheet you want to process in the call in your code, DON'T modify the code aside from the 2 bolded sections; header row indicator, and the array of the desired column order.

Private Sub ReorderXLBOM(ByVal excelapp As Application, ByVal xlws As Worksheet)
    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