iLogic: Export Parts List with Options

Issue:
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

 Here are the results of the export using the template.

Click to Enlarge

The vertical look up formula used in the Unit Cost cells is:
=(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:
 

'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

Using iLogic to Find Dimension Overrides

Issue:
You'd like to be able to quickly identify dimension overrides in your Inventor drawings using iLogic.

Solution:
( Note that I've added an improved version of this rule at the end of this post in the Edit section)

You can use iLogic to toggle the color of the text for all overridden dimensions, making it easy to spot them.

To use this iLogic code you can hold the Shift key and right click and then choose the Select all Inventor Dimensions from the menu, or you can simply window select all of the objects in the drawing and let the iLogic filter for only the dimensions.

Once the items to check are pre-selected you can run the iLogic rule and the text of any dimensions that are "fudged" will be set to magenta:

Any dimension found to have the Hide Dimension Value check box selected will be targeted by the rule:

Dimension found to have the Override Displayed Value check box selected will be targeted by the rule also:

You can then run a second rule to set the colors back, if needed: 
One thing to know about the second rule as it is currently written, is that it sets the color back to black (RGB value 0,0,0) rather than back to ByLayer. This could lead to problems if you're not aware of this.

 

 According to the API help file and this link, you should be able to set text to ByLayer by setting ColorSourceType = kLayerColorSource
but I was not able to get this to work in the iLogic code.

Here is the first rule, used to identify the overrides:

'-------------Start of ilogic ------------------------------------------------

' Set a reference to the select set of the active document.

Dim oDoc As DrawingDocument

oDoc = ThisApplication.ActiveDocument

'Dim oColor As Inventor.Color

Dim oColor As Color

'(255,0,255) = magenta

oColor = ThisApplication.TransientObjects.CreateColor(255, 0, 255)

' Find all selected occurrences and add them to an ObjectCollection.

Dim oDrawingDims() As DrawingDimension

a = 0

Dim i As Long

For i = 1 To oDoc.SelectSet.Count

            If Not oDoc.SelectSet.Item(i) Is Nothing Then

                   If TypeOf oDoc.SelectSet.Item(i) Is DrawingDimension Then

                   a = a + 1

                   ReDim Preserve oDrawingDims(0 To a)

                   oDrawingDims(a) = oDoc.SelectSet.Item(i)

                   End If

          End If

Next

For b = 1 To a

            if oDrawingDims(b).HideValue = True _

            or oDrawingDims(b).ModelValueOverridden = True then

          oDrawingDims(b).Text.Color = oColor

            Else

          End if

Next

'-------------end of ilogic ------------------------------------------------

 
Here is the second rule, used to set the color back to black:

'-------------Start of ilogic ------------------------------------------------

' Set a reference to the select set of the active document.

Dim oDoc As DrawingDocument

oDoc = ThisApplication.ActiveDocument

'Dim oColor As Inventor.Color

Dim oColor As Color

'(0,0,0) = black

oColor = ThisApplication.TransientObjects.CreateColor(0, 0, 0)

' Find all selected occurrences and add them to an ObjectCollection.

Dim oDrawingDims() As DrawingDimension

a = 0

Dim i As Long

For i = 1 To oDoc.SelectSet.Count

            If Not oDoc.SelectSet.Item(i) Is Nothing Then

                   If TypeOf oDoc.SelectSet.Item(i) Is DrawingDimension Then

                   a = a + 1

                   ReDim Preserve oDrawingDims(0 To a)

                   oDrawingDims(a) = oDoc.SelectSet.Item(i)

                   End If

          End If

Next

For b = 1 To a

            if oDrawingDims(b).HideValue = True _

            or oDrawingDims(b).ModelValueOverridden = True then

          oDrawingDims(b).Text.Color = oColor

            Else

          End if

Next

'-------------End of ilogic ------------------------------------------------

*** Edit ***

(July 5, 2011) 

Here is an improved version of the previous rule(s). In this version the user doesn't need to pre-select the drawing dimension. Instead the rule iterates through all of the dimensions in the the active drawing sheet. Additionally, the two previous rules have been combined and the user selects between the two options via a radio button input box.

 And lastly, this rule sets the text color back to the ByLayer color by using the line:

oColor.ColorSourceType = ColorSourceTypeEnum.kLayerColorSource

A big Thank You to Mike Deck at Autodesk for the help with the kLayerColorSource enumeration and other suggestions to improve this rule.

Here is the improved code:

'-------------Start of ilogic ------------------------------------------------

' Set a reference to the active document.

Dim oDoc As DrawingDocument

oDoc = ThisApplication.ActiveDocument

'Define the drawing dims collection

Dim oDrawingDims As DrawingDimension

'Dim oColor As Inventor.Color

Dim oColor As Color

'Prompt user to choose highlight / un-highlight

Dim booleanParam as Object

booleanParam = InputRadioBox("Select an Option", _

"Hightlight Overrides", "Un-Hightlight Overrides", True, Title := "iLogic")

'Loop through all dimensions and set colors

For Each oDrawingDims In oDoc.ActiveSheet.DrawingDimensions

   If booleanParam = True then

   'set color to magenta

   oColor = ThisApplication.TransientObjects.CreateColor(255, 0, 255)

   else

   'set color to black

   oColor = ThisApplication.TransientObjects.CreateColor(0, 0, 0)

   oColor.ColorSourceType = ColorSourceTypeEnum.kLayerColorSource

   end if

  

   if oDrawingDims.HideValue = True _

   or oDrawingDims.ModelValueOverridden = True then

   oDrawingDims.Text.Color = oColor

   Else

   End if

Next

'-------------End of ilogic ------------------------------------------------