iLogic to change parts list style

[nealon416]

Hi!

I have been successful recently in creating a series of rules thanks to the posts of many helpful people, thank you all!

Below are 2 rules that run in succession to create and save a parts list from a base view in a drawing.

I am now trying to set the parts list style to a custom style, ideally while creating the list.

Also, I have to manually go into the list to turn off visibility of certain items (based on material), does anyone know if I can do this with Ilogic?  I ask because these steps precede a rule that automates placing base views of all parts in the parts list.  If I could get the style and visibility right, I could automate the entire process.

Can anyone help changing such a style with ilogic?

Thanks to all in advance!

Inventor & Vault Pro 2012

Windows 7, 64bit

Core i7 920@2.67

8GB

CREATE PARTS LIST____
' Set a reference to the drawing document.
' This assumes a drawing document is active.
Dim oDrawDoc As DrawingDocument
oDrawDoc = ThisApplication.ActiveDocument
'Set a reference to the active sheet.
Dim oSheet As Sheet
oSheet = oDrawDoc.ActiveSheet
' 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.
Dim oDrawingView As DrawingView
oDrawingView = oSheet.DrawingViews(1)
Dim oPlacementPoint As Point2d

oPlacementPoint = ThisApplication.TransientGeometry.CreatePoint2d(-15#, 20#)

' Create the parts list.
Dim oPartsList As PartsList
'Dim oPartsListStyle As oDrawDoc.StylesManager.PartsListStyle.Name
'oDrawDoc.StylesManager.PartsListStyle.Name = ("CNC Parts List")

oPartsList = oSheet.PartsLists.Add(oDrawingView, oPlacementPoint)

iLogicVb.RunRule(export cnc parts list)


EXPORT CNC PARTS LIST____
path_and_name = ThisDoc.PathAndFileName(False) ' without extension
Dim oDoc As Inventor.DrawingDocument
oDoc = ThisDoc.Document

'specify the drawing sheet
oSheet = oDoc.Sheets(1) ' first sheet

' say there is a Partslist on the sheet.
            'Dim oPartslist As PartsList
            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") = ""'"E:\_VAULT WORKING FOLDER\Templates\PARTSLISTEXPORT.xls"
            
            'specify the columns to export         
            'oOptions.Value("ExportedColumns") = "SYTELINE#;QTY"
            
            'specify the start cell
            oOptions.Value("StartingCell") = "A1"
            
            'specify the XLS tab name
            'here the file name is used
            oOptions.Value("TableName") = "Parts List"
            
            '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 not to autofit the column width in the xls file
            oOptions.Value("AutoFitColumnWidth") = False
                
            'choose to apply cell formatting in the xls file
            oOptions.Value("ApplyCellFormatting") = True
            
            ' export the Partslist to Excel with options
            oPartslist.Export (path_and_name & ".xls", PartsListFileFormatEnum.kMicrosoftExcel, oOptions)

[ChristinaForest]

And if i want to skip a column in excell, because i have a calcul in???

[Curtis_Waguespack]

Hi nealon416,

Here's an example ilogic rule that places a parts list on a specific layer and to a specific style.

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

'assumes a drawing document is active.
Dim oDrawDoc As DrawingDocument
oDrawDoc = ThisApplication.ActiveDocument
'Set a reference to the active sheet.
Dim oSheet As Sheet
oSheet = oDrawDoc.ActiveSheet
'set a reference to the first drawing view on the sheet. Assumes it's not a draft view.
Dim oDrawingView As DrawingView
oDrawingView = oSheet.DrawingViews(1)
'define the insert point
Dim oPlacementPoint As Point2d
oPlacementPoint = ThisApplication.TransientGeometry.CreatePoint2d (27#, 20.625#)
'create the parts list
Dim oPartsList As PartsList     
oPartsList = oSheet.PartsLists.Add(oDrawingView, oPlacementPoint)
'set parts list to a specific layer
oPartsList.Layer = oDrawDoc.StylesManager.Layers.Item("Visible (ANSI)")
'set parts list to a specific style
oPartsList.Style = oDrawDoc.StylesManager.PartsListStyles.Item("Material List (ANSI)")

[Curtis_Waguespack]

Hi sergelachance,

I'm not aware of a way to do this from Inventor. Could you simply move you calculation column and place it to the far right as I've shown in the example at this link?:

http://inventortrenches.blogspot.com/2011/06/ilogic-export-parts-list-with-options.html

If not it might be possible to move the column via some VB magic after the data is exported into the template.

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

[ChristinaForest]

this is my rule but it's possible to skip a column "k" in excell ???

Sorry for my horrible english 😞 my first language is french

[ChristinaForest]

 I think my biggest problem is my language lol

Thanks for all 🙂

[Curtis_Waguespack]

Hi sergelachance,

I don't know how to skip a column. But here is a rule that will write the parts list information to the XLS template, and then cut and insert an existing column with formulas (that is placed to the far right hand column in the template file) to a specific place in the resulting xls. 

Template:

Output File:

So as an example, you could move your column K to column Z or some other, and then have the rule cut Column Z and insert it as column K.

You can download the three attached files and place them in the same folder to test this rule.

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

Edit the silly face in the code below should be D : D without the spaces

'get the path and name of the drawing file
path_and_name = ThisDoc.PathAndFileName(False) ' without extension

'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

'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") = path_and_name & "_template.xls"
 
'specify the columns to export         
oOptions.Value("ExportedColumns") = "QTY;PART NUMBER;DESCRIPTION;Length;Width"
 
'specify the start cell
oOptions.Value("StartingCell") = "A1"
 
'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
oOptions.Value("IncludeTitle") = False

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

'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(path_and_name & ".xls")

'cut a column
excelApp.Columns("G:G").Cut 
'insert the cut column
excelApp.Range("D1").Insert
'adjust column width
excelApp.Columns("D:D").AutoFit
'reset selected cell to A1
excelApp.Cells(1,1).Select
'suppress prompts (such as the compatibility checker)
excelApp.DisplayAlerts = false
'save the workbook
wb.Save

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

[nealon416]

Thanks for your input Curtis... your website has been so important to my success using i-Logic.  I appreciate your help!

[ChristinaForest]

ALL WORK REALLY GREAT 🙂 IT'S POSSIBLE TO SPECIFY TO EXPORT THE PART LIST IN THE SHEET2 IN THE TEMPLATE???

[Curtis_Waguespack]

Hi sergelachance,

I couldn't find a way to specify sheet2 for the export (I might have missed something), but this code can be added to the end to cut the columns from the original exported sheet and paste them to sheet2, and then delete the original sheet. This may or maynot work for you depending on your needs.

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

'suppress prompts (such as the compatibility checker)
excelApp.DisplayAlerts = False
'select the original exported sheet 
excelApp.Sheets(ThisDoc.FileName(False)).Select
'select range to copy
excelApp.Range("A:G").Copy
'select sheet to paste to
excelApp.Sheets("Sheet2").Select
'select range to paste to
excelApp.Range("A:G").Select
'paste the selection
excelApp.ActiveSheet.Paste
'reset selected cell to A1
excelApp.Cells(1,1).Select
'delete the original sheet
excelApp.Sheets(ThisDoc.FileName(False)).Delete()
'save the workbook
wb.Save
'close the workbook, uncomment if you want to close the xls file at the end
'wb.Close

[ChristinaForest]

Hi Curtis 🙂

I dont use your cut in paste column finaly, work great but the sheet1 is at my cnc, i juste want to choose the sheet2 in my template to export my part list, it's possible???

This is my rule

'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") = "T:\MasterGDPRO\STYLE_DATA\CARTOUCHE\TEST.xls"  

 'specify the columns to export         

oOptions.Value("ExportedColumns") = "COMPOSANTE;STOCK NUMBER;PI2;LONGUEUR;LARGEUR;QTY;QTE TOTALE;ÉPAISSEUR;CHANTS;DETAIL;COMMANDE;L;M;VEINAGE;USINAGE;NESTING"

'specify the start cell

oOptions.Value("StartingCell") = "T2"  

 'specify the XLS tab name

'here the file name is used

oOptions.Value("TableName") = ThisDoc.FileName(False) 'without extension        

'choose to autofit the column width in the xls file

oOptions.Value("AutoFitColumnWidth") = True

'choose to apply cell formatting in the xls file

oOptions.Value("ApplyCellFormatting") = False

' export the Partslist to Excel with options

oPartslist.Export(path_and_name & ".xls", _

PartsListFileFormatEnum.kMicrosoftExcel, oOptions)

Thanks again for all your help 🙂

[ChristinaForest]

it's possible to copy iProperties.Value("Custom", "NAME") to sheet2 cell A1?

Thanks for your help 🙂

[adam.nagy]

Hi,

Export() does not seem to have an option specifying if the data goes on Sheet1 or Sheet2.

Maybe you can specify the sheet name as part of the starting cell. Did you try that?
e.g.: oOptions.Value("StartingCell") = "Sheet2!T2" 

However, I guess you could use the Excel API to do post processing on the xls file afterwards.

With the Excel API, you should be able to do any modification on the created xls file.

Cheers,

[ChristinaForest]

Thanks for your help Adam, i try that 🙂