Options or custimazation to BOM export via ilogic to excel

[Anonymous]

Hello,

I'm interested in exporting a parts list from either an assembly or drawing. I've tried various codes and approaches but I can't seem to get it right.

Firstly, the .idw parts list approach only allows you to export columns that are present in your parts list.

The .iam BOM approach has no documentation on formatting (changing the table name of the spreadsheet etc)

Ideally I would lik to be able to export a BOM from an assembly, into perhaps a template, then to have the code change the tab name to the designer iproperty from the assembly file.

Then for a dialogue to ask for a quantity and populate this number into a specified column in the output spreadsheet.

It might be a bit much but so far all I have is the code below:

-------------------------------------------------------------------------------------------------------------

'BOM Publisher
oDoc = ThisDoc.ModelDocument
If oDoc.DocumentType = kPartDocumentObject Then
MessageBox.Show("You need to be in an Assembly to Export a BOM", "Databar: iLogic - BOM Publisher")
Return
End If
oDoc = ThisApplication.ActiveDocument
Dim oBOM As BOM
oBOM = oDoc.ComponentDefinition.BOM
'Options.Value("Author") = iProperties.Value("Summary", "Author")
'==========================================================================================
'You can change the output path by editing oPATH below
oPATH = ("c:\temp\") 'If you change this, remember to keep a \ at the end
'==========================================================================================
'STRUCTURED BoM ===========================================================================
'' the structured view to 'all levels'
'oBOM.StructuredViewFirstLevelOnly = False
'' Make sure that the structured view is enabled.
'oBOM.StructuredViewEnabled = True
'Dim oStructuredBOMView As BOMView
'oStructuredBOMView = oBOM.BOMViews.Item("Structured")
'' Export the BOM view to an Excel file
'oStructuredBOMView.Export (oPATH + ThisDoc.FileName(False) + ".xls", kMicrosoftExcelFormat)
'==========================================================================================
'PARTS ONLY BoM ===========================================================================
' Make sure that the parts only view is enabled.
oBOM.PartsOnlyViewEnabled = True
Dim oPartsOnlyBOMView As BOMView
oPartsOnlyBOMView = oBOM.BOMViews.Item("Parts Only")
' Export the BOM view to an Excel file
'oPartsOnlyBOMView.Export (oPATH + "BOM-PartsOnly.xls", kMicrosoftExcelFormat)
oPartsOnlyBOMView.Export (oPATH + ThisDoc.FileName(False) + " GA" + ".xls", kMicrosoftExcelFormat)
'==========================================================================================
i = MessageBox.Show("Preview the BOM?", "Databar: iLogic - BOM Publisher",MessageBoxButtons.YesNo)
If i = vbYes Then : launchviewer = 1 : Else : launchviewer = 0 : End If
If launchviewer = 1 Then ThisDoc.Launch(oPATH + ThisDoc.FileName(False) + " GA" + ".xls")

---------------------------------------------------------------------------------

Thanks in advance,

Ahmed.

[philip1009]

When looking at your BOM in the assembly file, in the lower left corner you have an import and export button, this is for BOM settings stored as an xml file.  Setup the BOM as you want and then click the export button to save the xml file in a location where it's safe and available to all users.

Enable the view you want to use first, then use this line to import your saved settings:

SyntaxEditor Code Snippet

ThisDoc.Document.ComponentDefinition.BOM.ImportBOMCustomization("C:\Users\Public\Documents\BOM Settings.xml")

Then sort, renumber, and export.

However, you might find that the Excel export might not exactly match the settings you want.  This is a known issue that Autodesk is supposed to be working on for the next patch.  For example I've found that the columns aren't in the exact order I want, but that's not too big of a deal for me.

[Anonymous]

Thank you Philip, is it possible to customize the output?

for example, how would i set the excel tab name based on a iproperty of the assembly, i.e. designer.

Thanks,

Ahmed.

[philip1009 · Accepted solution]

Yes, there's a third parameter option in the Export code that specifies the name of the sheet, just add another comma and space at the end of kMicrosoftExcelFormat then input the string for the name manually or by iProperty value:

SyntaxEditor Code Snippet

oPartsOnlyBOMView.Export (oPATH + ThisDoc.FileName(False) + " GA" + ".xls", kMicrosoftExcelFormat, iProperties.Value("Project", "Designer"))

[Anonymous]

Thats amazing, thank you, I've been trying to do that for a while and there doesn't seem to be any documentation on it.

If I was to need user input, i.e. for the code to ask the user for the "number of complete runs?", is it possible to launch a an input box, where the user inputs the number, and then this number is transfered into the sheet and used to calculate an overall quantity required for each part.

Is something like that possible?

[Anonymous]

So far I have something like this:

-----------------------------------------

SyntaxEditor Code Snippet

'define the file to create/open
myXLS_File = oPATH + ThisDoc.FileName(False) + " GA" + ".xls"
myparam = InputBox("Enter Number Runs Required", "Quantity", "1")

'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 = False
'suppress prompts (such as the compatibility checker)
excelApp.DisplayAlerts = False

'check for existing file 
If Dir(myXLS_File) <> "" Then
'workbook exists, open it
excelWorkbook = excelApp.Workbooks.Open(myXLS_File)
ExcelSheet = excelWorkbook.Worksheets(1)
Else
'workbook does NOT exist, so create a new one
excelWorkbook = excelApp.Workbooks.Add
End If

'Insert data into Excel.
With excelApp
    .Range("J2").Select
    .ActiveCell.FormulaR1C1 = C2*myparam
End With   

'set all of the columns to autofit
excelApp.Columns.AutoFit   
'save the file
excelWorkbook.SaveAs (myXLS_File)

'close the workbook And the Excel Application
'uncomment If you want To close the xls File at the End
excelWorkbook.Close
excelApp.Quit
excelApp = Nothing
---------------------------------------

 I can't get the calculation to work though, or think of a way to have it select the column from  J2 upto the last row of the BOM.

[philip1009]

For API documentation just go to the drop down menu from the Help button on the top right:

Just keep in mind that only standard Inventor API is documented, any add-ins and some other functionality isn't either exposed in API or the API isn't documented.  Also just know that not all of the API is accessible in iLogic, mainly Events, but there's some other functions that iLogic can't use.  The samples provided are meant for VBA application instead of iLogic so it takes some time to understand the differences.

For code that's specific to iLogic it's good to know about the Snippets on the left side of the Rule Editor window, using Snippets is much faster and easier than going through the API, then just use API when a Snippet can't be used.  Here's an example for popping up an user input box from a Snippet:

For the quantity calculation, there's many ways to go about that.  There's plenty of Excel functions in the Snippets section to directly edit the created Excel spreadsheet, unfortunately I'm not as familiar with Excel but I'm sure if you can find the right cells you can input equations and stuff to calculate quantities.  If it were me, I'd just make a temporary component pattern of the whole assembly to make the BOM the correct quantity, export the BOM, then undo the component pattern.  But that method may not be ideal depending on the size and complexity of the assembly.

[Anonymous]

The snippets on the side dont seem to have an option for generating a formula.

I can get it to input the calculation, but it comes in as a static number.

Perhaps it needs to iterate through?

Do you think this is possible?

-----------------------------------------------------------------------

SyntaxEditor Code Snippet

'define the file to create/open
myXLS_File = oPATH + ThisDoc.FileName(False) + " GA" + ".xls"
myparam = InputBox("Enter Number of Databar Runs Required", "Quantity", "1")

'Get the Inventor user name From the Inventor Options
'myName= ThisApplication.GeneralOptions.UserName

'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 = False
'suppress prompts (such as the compatibility checker)
excelApp.DisplayAlerts = False

'check for existing file 
If Dir(myXLS_File) <> "" Then
'workbook exists, open it
excelWorkbook = excelApp.Workbooks.Open(myXLS_File)
ExcelSheet = excelWorkbook.Worksheets(1)
Else
'workbook does NOT exist, so create a new one
excelWorkbook = excelApp.Workbooks.Add
End If

i = GoExcel.FindRow(myXLS_File, iProperties.Value("Project", "Designer"),"QTY", ">", 0)
c2 = GoExcel.CellValue(myXLS_File, iProperties.Value("Project", "Designer"), "C2")
'Insert data into Excel.

With excelApp
    .Range("J2").Select
    .ActiveCell.FormulaR1C1 = c2*myparam
End With   

'set all of the columns to autofit
excelApp.Columns.AutoFit   
'save the file
excelWorkbook.SaveAs (myXLS_File)

'close the workbook And the Excel Application
'uncomment If you want To close the xls File at the End
excelWorkbook.Close
excelApp.Quit
excelApp = Nothing
----------------------------------------------------------

This is as far as i have gotten.

[philip1009]

My understanding with Excel is that you start all equations with "=" and then input the equation, i.e. "=C2+C3".  Other than that, I'm not sure how it can be done, I'm not familiar enough with the Excel API to be much help.  If it were up to me, I'd find a way to maintain those BOM calculations in Inventor and then just do a simple Export.

[Anonymous]

Thanks for getting back to me, I did try that. Unfortunately it didn't work. The only other thing I can think of is to have inventor pattern the entire assembly with illogic code, but I'm not sure how to do this, the snippits only give examples for patterning a specified part. The assembly is bound to change from project to project, if there was a command or method to pattern every component in an assembly, this would help greatly.
Thanks,
Ahmed.

[Anonymous]

Thanks for getting back to me, I did try that. Unfortunately it didn’t work. The only other thing I can think of is to have inventor pattern the entire assembly with illogic code, but I’m not sure how to do this, the snippits only give examples for patterning a specified part. The assembly is bound to change from project to project, if there was a command or method to pattern every component in an assembly, this would help greatly.

Thanks,

Ahmed.

[philip1009]

Patterning occurrences in an assembly is different than other patterns.  You have to use an object collection as input for what occurrences will be used in the pattern.  Thankfully you're doing the entire assembly so creating that collection is easy since you'll just add all occurrences instead trying to filter out ones you don't want in the pattern.

SyntaxEditor Code Snippet

oDef = ThisDoc.Document.ComponentDefinition
oAxes = oDef.WorkAxes

Quantity = 2'Input Quantity for first line of pattern.

'Quantity = 2'Optional Input Quantity for second line of pattern to make a grid.

colBOM = ThisApplication.TransientObjects.CreateObjectCollection
'Create Object Collection for use in Add Rectangular Pattern Method.

For Each oOcc As ComponentOccurrence In oDef.Occurrences
	colBOM.Add(oOcc)
Next
'For Each Component Occurrence in this Assemblies list of Occurrences, add to the colBOM collection object.

oPattern = oDef.OccurrencePatterns.AddRectangularPattern(colBOM, _'Collection used for input.
oAxes.Item("X Axis"), _'Axis used to determine the first line of the pattern.
True, _'Boolean to determine the direction of the first line.
0, _'Distance between pattern elements on the first line in centimeters.
Quantity)', _'Quantity of pattern elements in first line.
'oAxes.Item("Y Axis"), _'Optional Axis used to determine the second line of the pattern to make a grid.
'True, _'Optional Boolean to determine the direction of the second line to make a grid.
'0, _'Optional Distance between pattern elements on the second line in centimeters.
'Quantity2, _'Quantity of pattern elements in second line.)

You need the first set of 4 arguments in the Add Pattern method to make a pattern of occurrences in the assembly, the second set of 4 arguments are used only if you want to make a grid, which isn't needed for the purpose of multiplying a quantity, but I'll make you aware of it for future use.  Let me know if you have any questions on this method.  This also gives you the pattern occurrence as oPattern, so if you want to delete it after making the BOM export, you can with oPattern.Delete.

[Anonymous]

Thank you so much for this.

It works wonders, but I've hit a wall. It doesn't seem to work if there are patterns already within the assembly.

Is there anyway around this?

Alot of the time we will use the same lengths of material to generate what we need, this isusuall just a pattern.

Thanks again,

Ahmed.

[philip1009 · Accepted solution]

Sorry, I forgot to take account of that, here's the update.  All Occurrences can be easily checked whether or not they're part of a pattern, so they're only added to the collection if they're not part of a Pattern, then there's another loop adding all Patterns to the collection to be patterned:

SyntaxEditor Code Snippet

oDef = ThisDoc.Document.ComponentDefinition
oAxes = oDef.WorkAxes

Quantity = 2'Input Quantity for first line of pattern.

'Quantity = 2'Optional Input Quantity for second line of pattern to make a grid.

colBOM = ThisApplication.TransientObjects.CreateObjectCollection
'Create Object Collection for use in Add Rectangular Pattern Method.

For Each oOcc As ComponentOccurrence In oDef.Occurrences
	If oOcc.IsPatternElement = False Then colBOM.Add(oOcc)
Next
For Each oPat As OccurrencePattern In oDef.OccurrencePatterns
	colBOM.Add(oPat)
Next
'For Each Component Occurrence in this Assemblies list of Occurrences, add to the colBOM collection object.

oPattern = oDef.OccurrencePatterns.AddRectangularPattern(colBOM, _'Collection used for input.
oAxes.Item("X Axis"), _'Axis used to determine the first line of the pattern.
True, _'Boolean to determine the direction of the first line.
0, _'Distance between pattern elements on the first line in centimeters.
Quantity)', _'Quantity of pattern elements in first line.
'oAxes.Item("Y Axis"), _'Optional Axis used to determine the second line of the pattern to make a grid.
'True, _'Optional Boolean to determine the direction of the second line to make a grid.
'0, _'Optional Distance between pattern elements on the second line in centimeters.
'Quantity2, _'Quantity of pattern elements in second line.)

[Anonymous]

Great!! I've just tried it, it works. How did you gain so much ilogic programming skill!

I can't find any documentation!

Thanks,

Ahmed.

[philip1009]

Here's the way to the API documentation provided by Autodesk:

The samples provided are meant for VBA instead of iLogic so they can't be just copied over without some translation, but the majority of the API is accessible in iLogic, also keep in mind that not everything Inventor does is either exposed in the API or is documented.  For example, hardly any of the Add-In environments come with an API guide other than Inventor standard features.  Pretty much all of my knowledge has come from that guide, this forum, and a bit of YouTube.

Here's a playlist teaching Visual Basic, the code that iLogic is based on:

https://www.youtube.com/watch?v=mM3zB3QWuv8&index=1&list=PLC601DEA22187BBF1

Here's a playlist going over a bunch of iLogic specific topics, Ketiv AVA is a good channel for Autodesk products:

https://www.youtube.com/playlist?list=PLetTiL10gBjH1duj0BKQObSejXfTD16Vh