topic Re: export bom with different order in Inventor Programming Forum

export bom with different order

skyngu — Sun, 14 Aug 2011 22:25:05 GMT

hi,

I use vba to export bom to excel. But the orders are different from using "export buttom" in BOM. The "description" is at the front. anyone knows why?

spec: inventor 2011 & windwo 7

thanks

Re: export bom with different order

skyngu — Mon, 15 Aug 2011 14:58:42 GMT

Is this a bug? I tried few times. The order of columns are different. Thanks

Public Sub BOMExport()

Dim oAssy As AssemblyDocument
Set oAssy = ThisApplication.ActiveDocument

' Set a reference to the BOM
Dim oBOM As BOM
Set oBOM = oAssy.ComponentDefinition.BOM

'Set file save path & temp picture
Dim sPath As String
sPath = Left(oAssy.FullFileName, Len(oAssy.FullFileName) - 4) & ".xls"

' switch structured view to all levels or first level only
oBOM.StructuredViewFirstLevelOnly = True

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

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

' Export the BOM view to an Excel file
oStructuredBOMView.Export sPath, kMicrosoftExcelFormat

end sub

Re: export bom with different order

skyngu — Tue, 23 Aug 2011 03:26:46 GMT

anyone has the same problem?

Re: export bom with different order

Anonymous — Tue, 23 Aug 2011 15:09:53 GMT

Yeah, I had the same problem. It looks to sort the columns in alphabetical order. So I had to wirte some code to sort it the way i want after the Excel file is generated.

Dan

Re: export bom with different order

skyngu — Tue, 23 Aug 2011 17:12:56 GMT

but it is fine in inventor 2010.

Re: export bom with different order

Anonymous — Tue, 23 Aug 2011 17:46:16 GMT

I know but it's this way in 2011 and 2012. I don't understand why it was changed to that. Hopefully it will be fixed in a service pack or hotfix but don't count on it. In the meantime, here is what I used to get my code to work. I am only using the Item, Component Type, Part Number, Qty, and Description columns. This will sort it in that order.

    Public Sub RearrangeColumns(ByVal strExcelFileName As String)

        Try
            oExcelApp = GetObject(, "Excel.Application")
        Catch ex As Exception
            oExcelApp = CreateObject("Excel.Application")
            oExcelApp.Visible = False
        End Try

        Dim oBook As Workbook
        Dim oSheet As Worksheet

        oBook = oExcelApp.Workbooks.Open(strExcelFileName)
        oSheet = oBook.WorkSheets(1)

       'cut the item and paste it at the beginning
        oSheet.Columns("C:C").select()
        oExcelApp.Selection.cut()
        oSheet.Columns("A:A").Select()
        oExcelApp.Selection.Insert(Microsoft.Office.Interop.Excel.XlDirection.xlToRight)

       'cut the Part number and paste it as the 3rd column
        oSheet.Columns("D:D").select()
        oExcelApp.Selection.cut()
        oSheet.Columns("C:C").Select()
        oExcelApp.Selection.Insert(Microsoft.Office.Interop.Excel.XlDirection.xlToRight)

        'cut the Qty and paste it as the 4th column
        oSheet.Columns("E:E").select()
        oExcelApp.Selection.cut()
        oSheet.Columns("D:D").Select()
        oExcelApp.Selection.Insert(Microsoft.Office.Interop.Excel.XlDirection.xlToRight)

        oBook.Save()

        oExcelApp.Visible = True

    End Sub

Hopefully that helps.

Re: export bom with different order

Anonymous — Tue, 23 Aug 2011 17:47:44 GMT

stupid formating. The smiley faces are suppose to be a colon and the letter D.

Re: export bom with different order

skyngu — Wed, 24 Aug 2011 11:58:01 GMT

thanks for your reply.

I hope there will be a patch from autodesk soon.

Re: export bom with different order

Anonymous — Tue, 24 Mar 2015 12:34:37 GMT

@jcneal wrote:
thanks for your reply.
I hope there will be a patch from autodesk soon.

Hello, I have found this same problem in Inventor 2015. Exported columns are sorted alphabetically when exporting, even after importing a .styxml style.

Is this patch still planned for release?