Export assembly BOM structure to excel, but add a custom column during the export

[blandb]

Can anyone assist with exporting a parts only tab from an assembly to excel, but also add a custom column that will do some math? For example, Take the "G_L" and multiply it by unit quantity and place into the custom column, then export all this to an excel file?

thanks.

[A.Acheson]

@blandb 

Just a couple of questions to try and sort out what might be needed. 

1. Do you have a sample assembly? If you do please attach.

2.Is the multiplication of the G_L parameter  available already in an existing BOM property?

3.Are you placing this data into an existing excel template? 
4. Do you have existing code you have attempted?
5. Do you use VBA/ ilogic?

6. Do you need to sort the columns to appear in a Particular order?

A regular excel export will just dump out the finished Bom into a new excel sheet each time. 

[blandb]

Thanks for the reply, I have ran across some variations of code to export BOM structures.

Think for example a balcony, there is a rail that goes across the front. Now I have that assembly, but it is also used on 100 other balconies. So I will take the assembly and place it in an upper level assembly where I will copy it say 100 times to account for the other balconies. Now from that main level we will run a parts only BOM to get the total required count and lengths for the pickets and posts. That is why I want to take the Item QTY say there are 500 pickets at 36" long and give a total of 18,000 inches so purchasing knows how much material to order. Same goes for top rail, posts, etc.

We can basically get away with the straight export of the parts only, its just the boss wanted to add a total column. I was just hoping there was an easy way with a rule to add that column and have iLogic just place in the text as needed. Instead of manually doing it in the excel file after it was created. If we could also sort based on stock number that way the list is in a nice order instead of scattered, that would be great as well.

thanks for your time.

[aelqabbany · Accepted solution]

I had to do something similar a while back. Let me know if it works for you.

		ExportSheet = ExportPath + "/Balcony" + ".xls"

		' Export the BOM view to an csv file
		oStructuredBOMView.Export(ExportSheet, kMicrosoftExcelFormat)
		
                'Adding Column in Excel
		Try
		GoExcel.Open(ExportSheet, "Sheet1") 'Open the sheet that you just exported
		GoExcel.CellValue("J1") = "Length" 'Give your columns names here
		For oRow = 2 To 1000 'Go through the rows
			If String.IsNullOrEmpty(GoExcel.CellValue("A" & oRow)) Then 
				Exit For 'Exit the loop when you reach an empty cell in column A
			Else
                                'Place your column values here
				GoExcel.CellValue("J" & oRow) = GoExcel.CellValue("C" & oRow) * GoExcel.CellValue("D" & oRow)
			End If
		Next
		GoExcel.Save
		GoExcel.Close

[blandb]

Any idea as to why it will not populate the cell? Is it due to unitless vs inch?

[aelqabbany]

Yup, the "in" is throwing the rule off.

Try this:

GoExcel.CellValue("J" & oRow) = Val(GoExcel.CellValue("E" & oRow)) * Val(GoExcel.CellValue("H" & oRow))

[blandb]

Ok, that worked, but is there a way to auto justify the columns in this? Simply just auto fitting the column widths? Also, to control the G_L from rounding to 3 decimal places. When exported it is taking 45.938 * 400 = 18375.2 instead of 18375.0. Thanks for all your support!

[aelqabbany]

You can use Round() to round your numbers.

Example:

GoExcel.CellValue("J" & oRow) = Round(Val(GoExcel.CellValue("E" & oRow)) * Val(GoExcel.CellValue("H" & oRow)))

 You can find more Round() options here:

I am not sure how your would auto-justify the column widths. Hopefully someone else can help with that.

[blandb]

Not sure why I didnt think of that...LOL

[blandb]

Just bumping to see if anyone knows how to justify the columns and auto adjust to length upon exporting a parts only tab..

[A.Acheson]

@blandb 

I don’t know if that is possible with ilogic go excel. 

You may need to convert your code to vb.net or VBA to use excel functionality. 

https://knowledge.autodesk.com/support/inventor/learn-explore/caas/simplecontent/content/how-to-acce...

Some of excel functions are not available in VBA.net, I had to resort to setting the columns width to a fixed dimension because I couldn’t get auto fit to work. 

https://docs.microsoft.com/en-us/office/vba/api/excel.range.autofit

Failing that a personal macro could do the trick if your stuck.  It takes more user inputs. 

[blandb]

After digging I found one of @Anonymous 's solutions:

https://forums.autodesk.com/t5/inventor-customization/how-to-create-a-excel-file-using-ilogic-thanks/td-p/3518240 

But where he has "Insert data", how can I combine the parts list export here?

I had my rule, then ran this, but blanked out his "Insert data" section and it auto fit all the cells, so this is effective, but has to be a simple way to combine this....here is my info below.  Would be nice to be able to insert the Entered Quantity Required in-between column "D" & "E".

qty = InputBox("Enter Rail Quantity needed", "Rail QTY", "1")


ThisBOM.Export("Parts Only", "Parts Only.xlsx", kMicrosoftExcelFormat)

ExportSheet = ThisDoc.Path+"\Parts Only.xlsx"



'		ExportPath = ThisDoc.Path
'		ExportSheet = ExportPath + "\Parts Only" + ".xls"
'oDoc = ThisApplication.ActiveDocument
'Dim oBOM As BOM
'oBOM = oDoc.ComponentDefinition.BOM

'' Export the BOM view 
'Dim oPartsOnlyBOMView As BOMView
'oPartsOnlyBOMView = oBOM.BOMViews.Item("Parts Only")
'oPartsOnlyBOMView.Export(ExportSheet, kMicrosoftExcelFormat)
	
                'Adding Column in Excel
		Try
		GoExcel.Open(ExportSheet, "Sheet1") 'Open the sheet that you just exported
		GoExcel.CellValue("D1") = "QTY for 1" 'Give your columns names here
		GoExcel.CellValue("G1") = "Length" 'Give your columns names here
		GoExcel.CellValue("H1") = "Total QTY REQ'D" 'Give your columns names here
		GoExcel.CellValue("I1") = "Total QTY/Length REQ'D" 'Give your columns names here


		For oRow = 2 To 1000 'Go through the rows
			If String.IsNullOrEmpty(GoExcel.CellValue("A" & oRow)) Then 
				Exit For 'Exit the loop when you reach an empty cell in column A
			Else
				GoExcel.CellValue("H" & oRow) = qty
                'Place your column values here
				If GoExcel.CellValue("G" & oRow) = "" Then
					GoExcel.CellValue("I" & oRow) = Val(GoExcel.CellValue("D" & oRow))* qty
				Else
				
				inc = .0625 ' rounding increment ( .125, .25, .5, etc)
				GoExcel.CellValue("I" & oRow) = Round(((Val(GoExcel.CellValue("D" & oRow)) * qty) * Round(Round(Val(GoExcel.CellValue("G" & oRow)), 4) / inc) * inc) / 12, 1) & "ft"
		
				End If
			End If
		Next
		Catch
		End Try

GoExcel.Save
GoExcel.Close

[A.Acheson · Accepted solution]

@blandb 

Your on the money with the example you found it is fairly simple to work with. Any reference to go excel will need to be replaced with the vb.net equivalent of sheet name, workbook, cell etc. 

I would start with just creating an excel file with the link posted and then integrate your code in once your comfortable with the functionality.