I was asked if it was possible to format the BoM export from Inventor’s assembly environment, similar to the way that parts lists are formatted in drawings.
I wrote some code to do this, below is an animated GIF showing the code in action:
My solution was to build an iLogic utility that does the following:
- Creates a temporary drawing from a pre-defined drawing template (stored in C:\Temp)
- In this temporary drawing – a single view of the current assembly is added
- A parts list is automatically added (the style is pulled from the drawing template)
- The parts list is exported to a formatted excel sheet (based on a specified Excel template, saved in C:\Temp)
- The Excel sheet is saved into a folder with the name of the original assembly file.
The drawing template file is in Inventor 2021 format and should work with newer versions. If you would like to use it in older versions see template notes at the bottom of this post.
To get it all to work, you will need to download this Zip File, unblock it
Then place the 2 files into “C:\Temp” on your PC.
Next you need to run the rule below from an assembly file (.IAM). I always use external rules for this sort of thing,as I am then able to run this from any assembly, and I don’t need to maintain code in multiple places.
oOptions = ThisApplication.TransientObjects.CreateNameValueMap' create a new NameValueMap object
'iLogic Utility by @ClintBrown3D originally posted here --> https://clintbrown.co.uk/export-formatted-parts-list-with-ilogic
'Parts list placement from Autodesk Inventor API samples
'Parts list export based on Curtis Waguespack's blog post --> https://inventortrenches.blogspot.com/2011/06/ilogic-export-parts-list-with-options.html
'XLS SETUP--------------------------------------------------------------XLS SETUP---------------------------------------------------------------------------XLS SETUP
oOptions.Value("Template") = "C:\Temp\PartListExport.xlsx" 'specify an existing template file 'to use For formatting colors, fonts, etc
oExcelSaveLocation = "C:\Temp\Parts Lists\" ' Make sure path has "\" at end -> eg C:\Temp\Parts Lists\
If(Not System.IO.Directory.Exists(oExcelSaveLocation)) Then: System.IO.Directory.CreateDirectory(oExcelSaveLocation):End If 'Create XLS Parts List folder
'specify the columns to export
oOptions.Value("ExportedColumns") = "ITEM;QTY;PART NUMBER;DESCRIPTION;MATERIAL;MASS" 'These Must match what is shown on the drawing!!!
'specify the start cell
oOptions.Value("StartingCell") = "A2"
'XLS SETUP--------------------------------------------------------------XLS SETUP---------------------------------------------------------------------------XLS SETUP
On Error GoTo ClintsErrorTrap
Dim oDrawDoc As DrawingDocument ' Set a reference to the drawing document.' This assumes a drawing document is active
oDrawDoc = ThisApplication.ActiveDocument
Dim oSheet As Sheet 'Set a reference to the active sheet.
oSheet = oDrawDoc.ActiveSheet
Dim oDrawingView As DrawingView ' Set a reference to the first drawing view on' the sheet. This assumes the first drawing' view on the sheet is not a draft view.
oDrawingView = oSheet.DrawingViews(1)
Dim oBorder As Border ' Set a reference to th sheet's border
oBorder = oSheet.Border
Dim oPlacementPoint As Point2d
If Not oBorder Is Nothing Then ' A border exists. The placement point' is the top-right corner of the border
oPlacementPoint = oBorder.RangeBox.MaxPoint
Else' There is no border. The placement point' is the top-right corner of the sheet.
oPlacementPoint = ThisApplication.TransientGeometry.CreatePoint2d(oSheet.Width, oSheet.Height)
End If
Dim oPartsList As PartsList ' Create the parts list.
oPartsList = oSheet.PartsLists.Add(oDrawingView, oPlacementPoint)
ThisDoc.Document.SaveAs("C:\Temp\DeleteMe2.dwg" , False)
oRead = System.IO.File.OpenText("C:\TEMP\part1.txt") 'Get Original Assembly file name
EntireFile1 = oRead.ReadLine()
oRead.Close()
oDrawingName = EntireFile1
oDoc = ThisDoc.Document'define oDoc
path_and_name = oExcelSaveLocation + oDrawingName
'specify the drawing sheet
oSheet = oDoc.Sheets("Sheet:1") ' sheet by name
oPartsList = oSheet.PartsLists(1) ' say there is a Partslist on the sheet.
oOptions.Value("IncludeTitle") = True 'choose to include the parts list title row
oOptions.Value("AutoFitColumnWidth") = True 'choose to autofit the column width in the xls file
If Dir(path_and_name & ".xlsx") <> "" Then : Kill (path_and_name & ".xlsx"): End If 'check for existing XLS file and delete it if found
oPartsList.Export(path_and_name & ".xlsx",PartsListFileFormatEnum.kMicrosoftExcel, oOptions) ' export the Partslist to Excel with options
ThisDoc.Launch(path_and_name & ".xlsx") 'Open Parts List
ThisDoc.Document.Close(True)
Return
ClintsErrorTrap :
MsgBox("We've encountered a mystery")
ThisDoc.Document.Close(True)
Template Notes:
The Template drawing is set up with a local Parts List Style, which I have called “Unofficial” (you could call it anything really!), this style has every possible property added to the “Parts List Column Chooser”. The reason for this, is that if a property is specified in the iLogic Code, but it is not visible on the drawing, there will be an error when the parts list is exported.
If you wish to make this code work with an earlier version of Inventor you will need to do something similar (ensure that the columns you need are available on the drawing).
iLogic Code in the Template is as follows:
oOptions = ThisApplication.TransientObjects.CreateNameValueMap' create a new NameValueMap object
'iLogic Utility by @ClintBrown3D originally posted here --> https://clintbrown.co.uk/export-formatted-parts-list-with-ilogic
'Parts list placement from Autodesk Inventor API samples
'Parts list export based on Curtis Waguespack's blog post --> https://inventortrenches.blogspot.com/2011/06/ilogic-export-parts-list-with-options.html
'XLS SETUP--------------------------------------------------------------XLS SETUP---------------------------------------------------------------------------XLS SETUP
oOptions.Value("Template") = "C:\Temp\PartListExport.xlsx" 'specify an existing template file 'to use For formatting colors, fonts, etc
oExcelSaveLocation = "C:\Temp\Parts Lists\" ' Make sure path has "\" at end -> eg C:\Temp\Parts Lists\
If(Not System.IO.Directory.Exists(oExcelSaveLocation)) Then: System.IO.Directory.CreateDirectory(oExcelSaveLocation):End If 'Create XLS Parts List folder
'specify the columns to export
oOptions.Value("ExportedColumns") = "ITEM;QTY;PART NUMBER;DESCRIPTION;MATERIAL;MASS" 'These Must match what is shown on the drawing!!!
'specify the start cell
oOptions.Value("StartingCell") = "A2"
'XLS SETUP--------------------------------------------------------------XLS SETUP---------------------------------------------------------------------------XLS SETUP
On Error GoTo ClintsErrorTrap
Dim oDrawDoc As DrawingDocument ' Set a reference to the drawing document.' This assumes a drawing document is active
oDrawDoc = ThisApplication.ActiveDocument
Dim oSheet As Sheet 'Set a reference to the active sheet.
oSheet = oDrawDoc.ActiveSheet
Dim oDrawingView As DrawingView ' Set a reference to the first drawing view on' the sheet. This assumes the first drawing' view on the sheet is not a draft view.
oDrawingView = oSheet.DrawingViews(1)
Dim oBorder As Border ' Set a reference to th sheet's border
oBorder = oSheet.Border
Dim oPlacementPoint As Point2d
If Not oBorder Is Nothing Then ' A border exists. The placement point' is the top-right corner of the border
oPlacementPoint = oBorder.RangeBox.MaxPoint
Else' There is no border. The placement point' is the top-right corner of the sheet.
oPlacementPoint = ThisApplication.TransientGeometry.CreatePoint2d(oSheet.Width, oSheet.Height)
End If
Dim oPartsList As PartsList ' Create the parts list.
oPartsList = oSheet.PartsLists.Add(oDrawingView, oPlacementPoint)
ThisDoc.Document.SaveAs("C:\Temp\DeleteMe2.dwg" , False)
oRead = System.IO.File.OpenText("C:\TEMP\part1.txt") 'Get Original Assembly file name
EntireFile1 = oRead.ReadLine()
oRead.Close()
oDrawingName = EntireFile1
oDoc = ThisDoc.Document'define oDoc
path_and_name = oExcelSaveLocation + oDrawingName
'specify the drawing sheet
oSheet = oDoc.Sheets("Sheet:1") ' sheet by name
oPartsList = oSheet.PartsLists(1) ' say there is a Partslist on the sheet.
oOptions.Value("IncludeTitle") = True 'choose to include the parts list title row
oOptions.Value("AutoFitColumnWidth") = True 'choose to autofit the column width in the xls file
If Dir(path_and_name & ".xlsx") <> "" Then : Kill (path_and_name & ".xlsx"): End If 'check for existing XLS file and delete it if found
oPartsList.Export(path_and_name & ".xlsx",PartsListFileFormatEnum.kMicrosoftExcel, oOptions) ' export the Partslist to Excel with options
ThisDoc.Launch(path_and_name & ".xlsx") 'Open Parts List
ThisDoc.Document.Close(True)
Return
ClintsErrorTrap :
MsgBox("We've encountered a mystery")
ThisDoc.Document.Close(True)
Notes:
This version of the code was briefly tested in Inventor 2021.
As always, please test all iLogic code extensively on non-production files. Do not use any code in a production environment until YOU have thoroughly tested it and have verified that it works as expected. Always back up any data before running any experimental code. You are ultimately responsible for any iLogic code that you run, so make sure you test it thoroughly!
