Export Parts List/BOM to Excel Using iLogic & Template

Anonymous · ‎07-04-2016

Hello all,

I've scoured these forums yet there are no solutions to the threads on this topic. The community here is great and have helped me on a number of occasions, so hopefully we'll get this sorted not only for me, but for others who search the forums in the future!

We currently have two parts lists on our drawings. One is a compact "reference" parts list which sits on page 1. The other is a full material list which we place off-sheet. The off-sheet material list is what we need to export to Excel for our Material Allocation department - as they assign their data to the three columns and don't have access to Inventor.

So for now we have to do the following:

Place both parts lists in the drawing
Right-click on the large material list and click "Export"
Navigate to the destination folder
Assign the template using the "Options" button
Save the material list

I like iLogic and how it saves time. I have seen a code from 2011 from @Anonymous_Waguespack which seems to do the trick on his example, however I cannot get it to work. It has been pasted on these forums many times but I'll paste it again in post #2 so I don't clog this post up.

Ideally I'd just make a code and reference to it in a form so users just have to click a button.

Can anyone help with this problem?

Anonymous · ‎07-04-2016

This is the code I tried to copy:

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

Anonymous · ‎07-04-2016

Can you change this line

oPartslist = oSheet.PartsLists(1)

to

oPartslist = oSheet.PartsLists(2)

Anonymous · ‎07-04-2016

Or try this

Format:HTML Format Version:1.0 StartHTML: 165 EndHTML: 22145 StartFragment: 314 EndFragment: 22113 StartSelection: 314 EndSelection: 314SyntaxEditor Code Snippet

'-------------Start of ilogic ------------------------------------------------
Imports System.IO

'Check to see if file is a drawing, exit if not
Dim oDoc As DrawingDocument
    'oDoc = ThisApplication.ActiveDocument
    If ThisApplication.ActiveDocument.DocumentType <> kDrawingDocumentObject Then
        
      Return
    End If

' Parts List Export Settings

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

'define oDoc
oDoc = ThisDoc.Document

'specify the drawing sheet
oSheet = oDoc.Sheets ("Sheet:1") ' sheet by name

'Check If a Parts List Exists
Try 
Dim oPartslist As PartsList

'set as the first parts list found on sheet 1
oPartslist = oSheet.PartsLists(2)

'get Excel target folder path
oFolder = Left(oPath, InStrRev(oPath, "ENTER PATH TO TEMPLATE HERE")) & "ENTER PATH TO TEMPLATE HERE"


'check For existing XLS file And delete it If found
If Dir(oFolder & "\" & oName & "*" & ".xls") <> "" Then
Kill (oFolder & "\" & oName & "*" & ".xls")



Else  

End If
' create a new NameValueMap object
oOptions = ThisApplication.TransientObjects.CreateNameValueMap

'specify an existing template file to use For formatting colors, fonts, etc
oOptions.Value("Template") = "OENTER PATH TO TEMPLATE HERE\PartListExport.xls"
 
'specify the columns to export         
oOptions.Value("ExportedColumns") = "ITEM;QTY;DESCRIPTION;PART NUMBER / MATERIAL"
 
'specify the start cell
oOptions.Value("StartingCell") = "A4"
 
'specify the XLS tab name, I used the filename of the drawing
oOptions.Value("TableName") = ThisDoc.FileName(False)& " Rev " & iProperties.Value("Project", "Revision Number") 'without extension

'include the parts list title row
oOptions.Value("IncludeTitle") = True          

'autofit the column width in the xls file
oOptions.Value("AutoFitColumnWidth") = True 
    
   
' export the Partslist to Excel with above options
oPartslist.Export(oFolder & "\" & oName & " Rev " & iProperties.Value("Project", "Revision Number") & ".xls", _
PartsListFileFormatEnum.kMicrosoftExcel, oOptions)  


'Exit If No Parts List Exists
Catch 

End Try


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

Anonymous · ‎07-04-2016

@Anonymous

Thanks for the code. I'm trying to get it to suit my application. Unfortunately the Kill command deleted my template Excel file as well as the others that were in the folder. So while I wait for our IT guy to restore the backup I'll try and figure out why the code deleted all Excel files instead of just those matching the name of the drawing.

Anonymous · ‎07-05-2016

Update:

With help from

@lynlaysaymo

I have a code which exports the material list using the template. The only issue is it is exporting to my drawing file path which is on my workstation as I'm using Vault. I need it to go to the server so my colleagues can access it.

Can anyone help with writing code that will export the file to a specified filepath? The code above won't work for me.

Anonymous · ‎07-05-2016

Another update:

I've managed to get the code to work by extracting parts from the above code. Reinstating the "Kill" command is proving difficult though, and I need to make sure it doesn't delete all the files in the folder like it did before...

The code I have so far is:

Format:HTML Format Version:1.0 StartHTML: 165 EndHTML: 15501 StartFragment: 314 EndFragment: 15469 StartSelection: 314 EndSelection: 314SyntaxEditor Code Snippet

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

'get Excel target folder path
oFolder = Left(oPath, InStrRev(oPath, "ENTER DESTINATION FILE PATH HERE")) & "ENTER DESTINATION FILE PATH HERE"


' create a new NameValueMap object

oOptions = ThisApplication.TransientObjects.CreateNameValueMap

'specify an existing template file 

'to use For formatting colors, fonts, etc

oOptions.Value("Template") = "ENTER TEMPLATE FILE PATH HERE\Template.xlsx"

 

'specify the columns to export          

oOptions.Value("ExportedColumns") = "REV;QTY;WIDTH;LENGTH;THK;ITEM;DESCRIPTION;PART NUMBER;MATERIAL;MASS"

 

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

'choose to autofit the column width in the xls file

oOptions.Value("AutoFitColumnWidth") = False

       

' export the Partslist to Excel with above options
oPartslist.Export(oFolder & "\" & oName & " Rev " & iProperties.Value("Project", "Revision Number") & ".xls", _
PartsListFileFormatEnum.kMicrosoftExcel, oOptions)

Anonymous · ‎07-05-2016

hi jazz

to save to a folder use;

'get Excel target folder path
oFolder = Left(oPath, InStrRev(oPath, "O:\\whatever you want\goes here")) & ""O:\\whatever you want\goes here"

re: the kill command,

not sure what is happening with this at your end, I'll look into it further though.

If you don't need this functionality you could just comment it out... when running the rule you should be prompted that the file exists and to click ok to overwrite the file?

Anonymous · ‎07-05-2016

When it gives the prompt and I tell it to overwrite, the resulting parts list is strange. It's like it has taken some of the template but not all of it. So for example the company logo is there but some of the formatting is wrong and information layout is strange...

I'll try again in case it was a glitch.

Anonymous · ‎10-31-2017

how can I get this to do partlist 2? or an specific partlist style only for that export? I have one that's called PartListExport BOM style that I would like it to call up. Thanks!

Format:HTML Format Version:1.0 StartHTML: 165 EndHTML: 836 StartFragment: 314 EndFragment: 804 StartSelection: 314 EndSelection: 314SyntaxEditor Code Snippet

oPartslist = oSheet.PartsLists(2)

doesn't work.

GosponZ · ‎11-01-2017

Check this. Don't forget Template

https://forums.autodesk.com/t5/inventor-customization/export-two-part-lists-table-to-excel-on-two-ta...