Inventor General Discussion

Inventor General Discussion

Reply
Valued Contributor
GavoGarmo
Posts: 81
Registered: ‎10-10-2012
Message 1 of 4 (1,008 Views)
Accepted Solution

Is it possible to export selected colums of a BOM to excel?

1008 Views, 3 Replies
11-28-2012 03:37 AM

I would like to be able to export the description column of a BOM to an excel file, and exclude all the other data. I can't see any reference to doing this in the API. We have almost 20 columns so deleting them once the file is made is a bit of a pain.

 

Thanks for any help.

Hi GavoGarmo,

 

Here is a quick ilogic rule to do this. In this example the BOM is exported to an XLS file and then Column D is being copied from the original XLS sheet into a new XLS sheet, then the original sheet is being deleted.

 

You can refer to this link to see how a basic rule is created, if iLogic is new to you:

http://inventortrenches.blogspot.com/2012/01/creating-basic-ilogic-rule-with-event.html

 

I hope this helps.
Best of luck to you in all of your Inventor pursuits,
Curtis
http://inventortrenches.blogspot.com

 

' a reference to the assembly document.
' This assumes an assembly document is active.
Dim oDoc As AssemblyDocument
oXLS = "C:\temp\BOM-StructuredAllLevels.xls"
oDoc = ThisApplication.ActiveDocument
'set a reference to the BOM
Dim oBOM As BOM
oBOM = oDoc.ComponentDefinition.BOM
'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")
' Export the BOM view to an Excel file
oStructuredBOMView.Export (oXLS, 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(oXLS)
'set all of the columns to autofit
excelApp.Columns.AutoFit
'suppress prompts (such as the compatibility checker)
excelApp.DisplayAlerts = false
'add a new sheet to the xls file
excelApp.sheets.Add.Name = "Sheet1"
'select the original exported sheet
excelApp.Sheets(2).Select
'select range to copy
excelApp.Columns("G").Copy
'select sheet to paste to
excelApp.Sheets("Sheet1").Select
'select range to paste to
excelApp.Columns("A").Select
'paste the selection
excelApp.ActiveSheet.Paste
'reset selected cell to A1
excelApp.Cells(1,1).Select
'delete the original sheet
excelApp.Sheets(2).Delete()
'save the workbook
wb.Save
''close the workbook, uncomment if you want to close the xls file at the end
''wb.Close

 

*Expert Elite*
harco
Posts: 879
Registered: ‎02-16-2006
Message 2 of 4 (978 Views)

Re: Is it possible to export selected columns of a BOM to excel?

11-28-2012 11:59 AM in reply to: GavoGarmo

To do this manually you may want to have a temporary BOM editor view xml file and a normal BOM editor xml file.

Export your normal editor view setting to an xml file.

Remove all the unwanted columns from your editor view, then export that as a temporary xml.

Save out the single column to excel.

 

This way you can import the temporary xml file to remove the columns.

Then re-import your normal column layout to reset the columns.

 

Slightly quicker than excel edit but not automatic sorry.

hth

 

 

*Expert Elite*
Curtis_Waguespack
Posts: 2,934
Registered: ‎03-08-2006
Message 3 of 4 (968 Views)

Copy and Paste Column in BOM XLS

11-28-2012 03:13 PM in reply to: GavoGarmo

Hi GavoGarmo,

 

Here is a quick ilogic rule to do this. In this example the BOM is exported to an XLS file and then Column D is being copied from the original XLS sheet into a new XLS sheet, then the original sheet is being deleted.

 

You can refer to this link to see how a basic rule is created, if iLogic is new to you:

http://inventortrenches.blogspot.com/2012/01/creating-basic-ilogic-rule-with-event.html

 

I hope this helps.
Best of luck to you in all of your Inventor pursuits,
Curtis
http://inventortrenches.blogspot.com

 

' a reference to the assembly document.
' This assumes an assembly document is active.
Dim oDoc As AssemblyDocument
oXLS = "C:\temp\BOM-StructuredAllLevels.xls"
oDoc = ThisApplication.ActiveDocument

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

'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")

' Export the BOM view to an Excel file
oStructuredBOMView.Export (oXLS, 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(oXLS)

'set all of the columns to autofit
excelApp.Columns.AutoFit
'suppress prompts (such as the compatibility checker)
excelApp.DisplayAlerts = false
'add a new sheet to the xls file
excelApp.sheets.Add.Name = "Sheet1"
'select the original exported sheet 
excelApp.Sheets(2).Select
'select range to copy
excelApp.Columns("G").Copy
'select sheet to paste to
excelApp.Sheets("Sheet1").Select
'select range to paste to
excelApp.Columns("A").Select
'paste the selection
excelApp.ActiveSheet.Paste
'reset selected cell to A1
excelApp.Cells(1,1).Select
'delete the original sheet
excelApp.Sheets(2).Delete()

'save the workbook
wb.Save

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

 



  solution.png  Did you find this reply helpful ? If so please use the Accept as Solution or  Kudos button below.





Distinguished Contributor
SKinzel
Posts: 133
Registered: ‎06-12-2007
Message 4 of 4 (932 Views)

Re: Is it possible to export selected colums of a BOM to excel?

12-04-2012 01:59 PM in reply to: GavoGarmo

Are you exporting from the Bill Of Material or from a Part List?  If you export from a Parts List, from within a drawing, you can chose what columns are exported by clicking on the OPTIONS button in the Export Dialog Box.  Or you could define a Parts List Style with only the columns you want to export.  This may be an annoying extra step if you don't already have drawings with a Parts List for the assembly.

 

Stuart Kinzel
Inventor 2013-64bit, HP EliteBook8740w Intel Core i5CPU 2.67 GHz
8GB memory
Windows 7 64bit
Need installation help?

Start with some of our most frequented solutions or visit the Installation and Licensing Forum to get help installing your software.