You want to use some iLogic code to automatically export your parts list to an XLS file and set the options and formatting.
Solution:
Setting options can be helpful when you use an XLS file to hand off BOM data to a MRP system. Typically when doing this kind of thing having the formatting consistent is important. Using iLogic to set these options will ensure human error does not cause a problem down stream in the process.
Another way you use the exported parts lists is for costing. If you generate take offs or quotes for your sales staff, or use Inventor for small projects where you'd like to quickly create estimates, you can setup an XLS template file containing pricing information and then use it to import the parts list data.
Here I'll share some sample code that exports a parts list and uses the export options to specify a custom XLS file template, choose which columns to export, choose which cells to populate and set other options. I've set up the XLS template to do some calculations on the data automatically as an extra touch.
Here is the drawing file I'll use for this example:
To understand the options let's first look at the options available when you right-click on a parts list table and choose Export. Here you can see the options found after clicking the Options button when exporting the parts list as an Excel file:
Here are the results of exporting a typical parts list with no options set. I use a basic exported XLS file such as this to hand data to our MRP system for all of our Inventor drawings.
Here are the results of setting some of the options, such as the Start Cell set to A3, the parts list Title, selecting only 3 columns, and setting the column widths to auto fit:
Here is an XLS file I've customized to use as an export template. This file provides a destination for the Quantity, Part Number, and Description column data. It also contains a look up list of cataloged part numbers and prices. The goal is to export the Parts List from the Inventor drawing and automatically insert it into this template. This allows a formula in the Unit Cost column to match the part number from the Part Number column with the PN column and then insert the corresponding value from the Price column. The Extended Cost column will then multiply the Unit Cost column with the QTY column:
 |
| Click to Enlarge |
 |
| Click to Enlarge |
=(VLOOKUP(B5,$G$5:$H$13,2,FALSE))
Here's a link to a video detailing Excel's vlookup function, in case you've never used it before.
And the formula used in the Extended Cost cells is simply:
=D5*A5
Here is the the example iLogic code. You could set this up to run on the After Save Document event trigger so that the rule is triggered automatically each time the file is saved, or you could run it manually.
'-------------Start of ilogic ------------------------------------------------
'get the path and name of the drawing file
path_and_name = ThisDoc.PathAndFileName(False) ' without extension
'define oDoc
oDoc = ThisDoc.Document
'specify the drawing sheet
oSheet = oDoc.Sheets("Sheet:1") ' sheet by name
'oSheet = oDoc.Sheets(1) ' first sheet
' say there is a Partslist on the sheet.
oPartslist = oSheet.PartsLists(1)
' create a new NameValueMap object
oOptions = ThisApplication.TransientObjects.CreateNameValueMap
'specify an existing template file
'to use For formatting colors, fonts, etc
oOptions.Value("Template") = "C:\Temp\PartListExport.xls"
'specify the columns to export
oOptions.Value("ExportedColumns") = "QTY;PART NUMBER;DESCRIPTION"
'specify the start cell
oOptions.Value("StartingCell") = "A3"
'specify the XLS tab name
'here the file name is used
oOptions.Value("TableName") = ThisDoc.FileName(False) 'without extension
'choose to include the parts list title row
'in this example "Ye Old List of Parts" is written to the StartingCell
oOptions.Value("IncludeTitle") = True
'choose to autofit the column width in the xls file
oOptions.Value("AutoFitColumnWidth") = True
' export the Partslist to Excel with options
oPartslist.Export(path_and_name & ".xls", _
PartsListFileFormatEnum.kMicrosoftExcel, oOptions)
'-------------End of ilogic ------------------------------------------------
Edits:
I ran into a situation when using the export template, where the XLS file was being appended rather than replaced. You can use this snippet to delete the existing file before saving out the new one if you run into this:
Edits:
I ran into a situation when using the export template, where the XLS file was being appended rather than replaced. You can use this snippet to delete the existing file before saving out the new one if you run into this:
'check for existing XLS file and delete it if found
if Dir(path_and_name & ".xls") <> "" then
Kill (path_and_name & ".xls")
else
end if
