topic Re: Export BOM to a excel Template in Inventor Programming Forum

Export BOM to a excel Template

rcoi — Tue, 17 Jan 2023 14:08:14 GMT

Good days, I've been learning to program in iLOGIC, so I'm new to it. From several forums I collected information to be able to export my BOM to an excel template but I cannot achieve it. I share the line that I programmed, it works but it throws me errors. I share them with you in case you recommend another path or what mistakes I have, I would appreciate a good hand. Thank you.

Dim oAsmDoc As AssemblyDocument
Dim oAsmCompDef As AssemblyComponentDefinition
Dim oBOMView As BOMView
oAsmDoc = ThisDoc.Document
oAsmCompDef = oAsmDoc.ComponentDefinition

oBOMView = oAsmCompDef.BOM.BOMViews.Item("Solo piezas")
oBOMView.Sort("Descripción", True)
oBOMView.Renumber(1)

oOptionsBOM = ThisApplication.TransientObjects.CreateNameValueMap
oOptionsBOM.Value("StartingCell") = "B3"
oOptionsBOM.Value("Template") = "C:\VAULT\Proyectos\INT\STD\COM\MAZOS\MAZO PARA BORRAR\BOM EXCEL TEMPLATE.xlsx"

oBOMView.Export("AER.xlsx", kMicrosoftExcelFormat, oOptionsBOM)

Re: Export BOM to a excel Template

WCrihfield — Tue, 17 Jan 2023 14:44:57 GMT

Hi @rcoi. Can you please tell us what the error messages are saying? There is often some key bits of information on the 'More Info' tab of the error message that can help point to the line of code where the error is being encountered. I see that you are only specifying the file name and file extension of the Excel file you are trying to export to. I believe this will attempt to save the Excel file to the same directory as the assembly file you obtained the BOM from. You may need to include the full file path, along with the file name & file extension if you need to save it somewhere more specific. I also noticed that the Excel file you are specifying as the Template has "VAULT" in its path. Are you sure that Template file is 'checked out' before attempting this export?

Re: Export BOM to a excel Template

rcoi — Tue, 17 Jan 2023 14:55:22 GMT

Thank you very much for your prompt response. I am working inside my VAUL workspace, but this excel file is not uploaded to the server (I never CHECKED IN it), just like my assembly.

On the other hand this is the error message I get:

System.ArgumentException: El parámetro no es correcto. (Excepción de HRESULT: 0x80070057 (E_INVALIDARG))
en System.RuntimeType.ForwardCallToInvokeMember(String memberName, BindingFlags flags, Object target, Int32[] aWrapperTypes, MessageData& msgData)
en Inventor.BOMView.Export(String FileName, FileFormatEnum FileFormat, Object Options)
en ThisRule.Main()
en Autodesk.iLogic.Exec.AppDomExec.ExecRuleInAssembly(Assembly assem)
en iLogic.RuleEvalContainer.ExecRuleEval(String execRule)

Re: Export BOM to a excel Template

WCrihfield — Tue, 17 Jan 2023 15:13:24 GMT

Well, that error message tells us that the error is happening at the line of code where you are calling the Export method, but simply says that one of the 'arguments' is 'invalid', for some reason. You could try specifying the full file path for the output Excel file, instead of just "AER.xlsx", but I don't expect that to be the problem. You may have to 'declare' the 'oOptionsBOM' variable, like 'Dim oOptionsBOM As NameValueMap', so that the method will recognize it. You may also need to include the full specification for the 'FileFormatEnum', like 'FileFormatEnum.kMicrosoftExcelFormat', instead of just the second part of it.

Re: Export BOM to a excel Template

rcoi — Tue, 17 Jan 2023 17:10:50 GMT

Thank you very much, I made the changes that you mentioned and now it does not throw any error and exports the BOM. But, it does not use the "template" or "StartingCell", as if "oOptions" were ignored.

Now this is the new Code

Dim oAsmDoc As AssemblyDocument
Dim oAsmCompDef As AssemblyComponentDefinition
Dim oBOMView As BOMView
Dim oOptionsBOM As NameValueMap
oAsmDoc = ThisDoc.Document
oAsmCompDef = oAsmDoc.ComponentDefinition

oBOMView = oAsmCompDef.BOM.BOMViews.Item("Solo piezas")
oBOMView.Sort("Descripción", True)
oBOMView.Renumber(1)

oOptionsBOM = ThisApplication.TransientObjects.CreateNameValueMap
oOptionsBOM.Value("StartingCell") = "B3"
oOptionsBOM.Value("Template") = "C:\VAULT\Proyectos\INT\STD\COM\MAZOS\MAZO PARA BORRAR\PARTES\BOM TEMPLATE.xlsx"

oBOMView.Export("C:\VAULT\Proyectos\INT\STD\COM\MAZOS\MAZO PARA BORRAR\AER.xlsx", FileFormatEnum.kMicrosoftExcelFormat, oOptions)

Re: Export BOM to a excel Template

WCrihfield — Tue, 17 Jan 2023 19:27:11 GMT

What year/version of Inventor are you using? When I look at the 2022 & 2023 online help page documentation for that BOMView.Export method, I only see one option name mentioned for use within the NameValueMap. That one option is named "Table Name", and its value is where you can specify the name for the sheet/tab in the Excel document. If not supplied, it will use the file name as the sheet name.

Re: Export BOM to a excel Template

rcoi — Wed, 18 Jan 2023 11:15:53 GMT

Im using Inventor 2019. But, from the information that you gave me, I can deduce that the way in which I have raised the code is not correct. I think I should continue investigating another way to export the BOM in a template.

Re: Export BOM to a excel Template

A.Acheson — Wed, 18 Jan 2023 14:18:58 GMT

The two ways to do this are contained in the below post.

1. Export line by line the BOM into open excel workbook.

2. Export whole BOM to temporary excel sheet then copy either the range or the sheet to an existing workbook. The link below copies the sheet you may want the range.

https://forums.autodesk.com/t5/inventor-ilogic-and-vb-net-forum/ilogic-code-to-export-bom-to-excel-file-overwrites-data-in-excel/m-p/7055782#M81120

Re: Export BOM to a excel Template

dardine4UZSF — Wed, 04 Sep 2024 20:58:56 GMT

I am rewriting an ilogic code I found that pushes the structured BOM to an excel file template. I have truble shooted most of the code but I am getting and error in the last few lines where it is pushing the iproerties to a specific row. Here are the lines at issue.

	sWrtieCell = "A" & CurrentRow
	GoExcel.CellValue(sWriteCell) = oItemNumberProperty
	
	sWrtieCell = "B" & CurrentRow
	GoExcel.CellValue(sWriteCell) = oDescripProperty.Value
	
	sWrtieCell = "C" & CurrentRow
	GoExcel.CellValue(sWriteCell) = oPartNumberProperty.Value

The expanded error code is:

System.InvalidCastException: Unable to cast object of type 'System.String' to type 'System.Object[,]'.
at Autodesk.iLogic.Core.ExcelInterface.LibXLRange.ConvertToObjectArray(Object o)
at Autodesk.iLogic.Core.ExcelInterface.LibXLRange.set_Value2(Object value)
at iLogic.GoExcel.set_CurrentCellValue(String cellAddress, Object value)
at ThisRule.QueryBOMRowProperties(BOMRowsEnumerator oBOMRows, String JobSpreadsheetName, Int32 iCurrRow) in external rule: ExportBOM:line 120
at ThisRule.Main() in external rule: ExportBOM:line 60
at Autodesk.iLogic.Exec.AppDomExec.ExecRuleInAssembly(Assembly assem)
at iLogic.RuleEvalContainer.ExecRuleEval(String execRule)

Any help would be appreciated.

Re: Export BOM to a excel Template

rcoi — Thu, 05 Sep 2024 11:05:52 GMT

Hi @dardine4UZSF, I will share with you the code I use to export my BOM to an excel template.

Dim oAsmDoc As AssemblyDocument
Dim oAsmCompDef As AssemblyComponentDefinition
Dim oBOMView As BOMView
oAsmDoc = ThisDoc.Document
oAsmCompDef = oAsmDoc.ComponentDefinition

oBOMView = oAsmCompDef.BOM.BOMViews.Item("Solo piezas")
oBOMView.Sort("Descripción", True)
oBOMView.Renumber(1)



'-----------------------

Dim oDoc As AssemblyDocument
oDoc = ThisApplication.ActiveDocument
'Dim oBOMView As BOMView

Dim oBOM As BOM
oBOM = oDoc.ComponentDefinition.BOM

oBOM.PartsOnlyViewEnabled = True



oBOMView = oBOM.BOMViews.Item("Solo Piezas")

xlApp = CreateObject("Excel.Application")

'comment out or change to false 
'in order to not show Excel
xlApp.Visible = True 

xlWorkbook = xlApp.Workbooks.Open("C:\VAULT\Proyectos\INT\STD\COM\MAZOS\PLANTILLA ESTANDAR MAZO GAS\PARTES\LISTADO MATERIARLES AER STD.xlsx")

xlWorksheet = xlWorkbook.Worksheets.Item("MATERIALES (PCP)")

Dim row As Integer
row = 5

'xlWorksheet.Range("B3").Value = "ITEM"
'xlWorksheet.Range("B3").Value = "CTDAD"
'xlWorksheet.Range("C3").Value = "DESC"
'xlWorksheet.Range("D3").Value = "Part Number"

'Dim bRow As bomRow
bRows = oBOMView.BOMRows
For Each bRow In bRows

	Dim rDoc As Document
	rDoc = bRow.ComponentDefinitions.Item(1).Document
	
	Dim docPropertySet As PropertySet
	docPropertySet = rDoc.PropertySets.Item("Design Tracking Properties")
	
	xlWorksheet.Range("A" & row).Value = bRow.ItemNumber
	xlWorksheet.Range("F" & row).Value = bRow.TotalQuantity
	xlWorksheet.Range("C" & row).Value = docPropertySet.Item("Description").Value
	xlWorksheet.Range("B" & row).Value = docPropertySet.Item("Stock Number").Value
	'xlWorksheet.Range("F" & row).Value = bRow.ItemQuantity
    xlWorksheet.Range("C1").Value = iProperties.Value("SUMMARY", "Comments")
	xlWorksheet.Range("C2").Value = iProperties.Value("PROJECT", "Part Number")
	
	
	row = row + 1
Next

'xlWorkbook.Save
'xlWorkbook.Close (False)
'xlApp.Quit

Hope it can help you.

Regards

Re: Export BOM to a excel Template

A.Acheson — Thu, 05 Sep 2024 14:34:58 GMT

	sWrtieCell = "A" & CurrentRow
	GoExcel.CellValue(sWriteCell) = oItemNumberProperty
	
	sWrtieCell = "B" & CurrentRow
	GoExcel.CellValue(sWriteCell) = oDescripProperty.Value
	
	sWrtieCell = "C" & CurrentRow
	GoExcel.CellValue(sWriteCell) = oPartNumberProperty.Value

The expanded error code is:

System.InvalidCastException: Unable to cast object of type 'System.String' to type 'System.Object[,]'.
at Autodesk.iLogic.Core.ExcelInterface.LibXLRange.ConvertToObjectArray(Object o)
at Autodesk.iLogic.Core.ExcelInterface.LibXLRange.set_Value2(Object value)
at iLogic.GoExcel.set_CurrentCellValue(String cellAddress, Object value)
at ThisRule.QueryBOMRowProperties(BOMRowsEnumerator oBOMRows, String JobSpreadsheetName, Int32 iCurrRow) in external rule: ExportBOM:line 120
at ThisRule.Main() in external rule: ExportBOM:line 60
at Autodesk.iLogic.Exec.AppDomExec.ExecRuleInAssembly(Assembly assem)
at iLogic.RuleEvalContainer.ExecRuleEval(String execRule)

For this one your missing the value property of the object. When added it will return a string if omitted it will return an object and this matches the error message.

oItemNumberProperty.Value

Re: Export BOM to a excel Template

dardine4UZSF — Thu, 05 Sep 2024 18:44:11 GMT

So I got this code an made to work exporting oItemNumberProperty, odescripProperty.Value, oPartNumberProperty.Value and oRevNumberValue. When I added oSource1Property = oCustomPropertySet.Item("Source 1") Source 1 is a custom iproperty field I have in my stuctured BOM. It now throws this error. At that line.

Error on line 116 in rule: ExportBOM, in document: 20.0000061.iam

Unspecified error (Exception from HRESULT: 0x80004005 (E_FAIL))

System.Runtime.InteropServices.COMException (0x80004005):

Unspecified error (Exception from HRESULT: 0x80004005 (E_FAIL))
at Microsoft.VisualBasic.CompilerServices.LateBinding.LateGet(Object o, Type objType, String name, Object[] args, String[] paramnames, Boolean[] CopyBack)
at Microsoft.VisualBasic.CompilerServices.NewLateBinding.LateGet(Object Instance, Type Type, String MemberName, Object[] Arguments, String[] ArgumentNames, Type[] TypeArguments, Boolean[] CopyBack)
at ThisRule.QueryBOMRowProperties(BOMRowsEnumerator oBOMRows, String JobSpreadsheetName, Int32 iCurrRow) in external rule: ExportBOM:line 116
at ThisRule.Main() in external rule: ExportBOM:line 60
at Autodesk.iLogic.Exec.AppDomExec.ExecRuleInAssembly(Assembly assem)
at iLogic.RuleEvalContainer.ExecRuleEval(String execRule)

Below is my full code.

Sub Main()
	'Declarations
    Dim oAssyDoc As AssemblyDocument
    oAssyDoc = ThisApplication.ActiveDocument

    ' Set a reference to the BOM
    Dim oAssyCompDef As AssemblyComponentDefinition
    oAssyCompDef = oAssyDoc.ComponentDefinition
    
	'Get Path of current document
	Dim sPath As String = ThisDoc.Path
	
	'Set master level Levelof Detail. Needs to be in Master LOD for BOM Manipulation
	Dim oLOD As LevelOfDetailRepresentation
	Try
		oLOD = oAssyCompDef.RepresentationsManager.LevelOfDetailrepresentations.Item("Master")
	Catch ex As System.ArgumentException
	Finally
		oLOD.Activate(True)
	End Try
	
	'Get BOM From Assembly
	Dim oBOM As BOM
	oBOM = oAssyCompDef.BOM
	Logger.Trace("oBOM is set")
	
		
	oBOM.StructuredViewFirstLevelOnly = False
		
	oBOM.StructuredViewEnabled = True
	'oBOM.PartsOnlyViewEnabled = True
			
    Dim oBOMView As BOMView
    oBOMView = oBOM.BOMViews.Item("Structured")
	Logger.Trace("oStructuredBOMView is set")
	
	
	
	
'BOM Export

	'Copy Job Information Spredshet Form
	Dim sJobBOMTempName As String = String.Concat(sPath, "\_BOM_Template.xlsx")
	Dim sJobBOMName As String = String.Concat(ThisDoc.PathAndFileName(False), "-BOM.xlsx")
	Logger.Trace("sJobBOMName: " & sJobBOMName)
	
	Try
		IO.File.Copy(sJobBOMTempName, sJobBOMName, True)
	Catch ex As Exception
		MessageBox.Show(ex.Message)
	End Try
	
	Dim sMySheet As String = "BOM"
	Static iCurrRow As Integer = 6
	
	GoExcel.DisplayAlerts = False
	GoExcel.Open(sJobBOMName, sMySheet)
	
'Write BOM Values To Excel
	Call QueryBOMRowProperties(oBOMView.BOMRows, sJobBOMName, iCurrRow)
	oAssyDoc.Save
	
'Save Excel
	GoExcel.Save
	GoExcel.Close
	
'Set to iLogic LevelOFDetail
	Try
		oLOD = oAssyCompDef.RepresentationsManager.LevelOfDetailRepresentations.Item("iLogic")
	Catch ex As System.ArgumentException
		oLOD = oAssyCompDef.RepresentationsManager.LevelOfDetailRepresentations.Add("iLogic")
	Finally
		oLOD.Activate(True)
	End Try
		
'Message Box
	Dim sMessage As String = "BOM Export Complete"
	Dim sCaption As String = "BOM Export Complete"
	Dim oButtons As MessageBoxButtons = MessageBoxButtons.OK
	Dim oIcons As MessageBoxIcon = MessageBoxIcon.Information
	
	Dim oResult As DialogResult
	
	' Display the Message Box
	oResult = MessageBox.Show(sMessage, sCaption, oButtons, oIcons)
	
End Sub

Public Sub QueryBOMRowProperties(oBOMRows As BOMRowsEnumerator, JobSpreadsheetName As String, iCurrRow As Integer)
	'Iterate through the contents of BOM rows
	Static CurrentRow As Integer = iCurrRow
	Dim i As Long
		
	For i = 1 To oBOMRows.Count
	
	'Get Current row
	Dim oRow As BOMRow
	oRow = oBOMRows.Item(i)
	
	'Set a reference to the primary ComponentDefinition of the row
	Dim oCompDef As ComponentDefinition
	oCompDef = oRow.ComponentDefinitions.Item(1)
	
	Dim oPropSets As PropertySets
	oPropSets = oCompDef.Document.PropertySets
	
	oDesignTrackingPropertySet = oPropSets.Item("Design Tracking Properties")
	oInventorSummaryPropertySet = oPropSets.Item("Inventor Summary Information")
	oCustomPropertySet = oPropSets.Item("Inventor User defined Properties")
	
	'Get the file properties that are required
	oItemNumberProperty = oRow.ItemNumber
	oDescripProperty = oDesignTrackingPropertySet.Item("Description")
	oPartNumberProperty = oDesignTrackingPropertySet.Item("Part Number")
	oRevNumberProperty = oInventorSummaryPropertySet.Item("Revision Number")
	oSource1Property = oCustomPropertySet.Item("SOURCE 1")
	
	Trace.WriteLine("oItemNumberProperty: " & oItemNumberProperty)
	Trace.WriteLine("oDescripProperty: " & oDescripProperty.Value)
	Trace.WriteLine("oPartNumberProperty: " & oPartNumberProperty.Value)
	Trace.WriteLine("oRevNumberProperty: " & oRevNumberProperty.Value)
	Trace.WriteLine("oSource1Property: " & oSource1Property.Value)
	
	
	GoExcel.CellValue("A"&CurrentRow) = oItemNumberProperty
	GoExcel.CellValue("B"&CurrentRow) = oDescripProperty.Value
	GoExcel.CellValue("C" & CurrentRow) = oPartNumberProperty.Value
	GoExcel.CellValue("D" & CurrentRow) = oRevNumberProperty.Value
	GoExcel.CellValue("E" & CurrentRow) = oSource1Property.Value
	
					
	'increment row by 1
	CurrentRow = CurrentRow + 1
	
	'Recursively itererate child rows if present
	If Not oRow.ChildRows Is Nothing Then
		Call QueryBOMRowProperties(oRow.ChildRows, JobSpreadsheetName, CurrentRow)
	Else
	End If
Next i
End Sub

Re: Export BOM to a excel Template

dardine4UZSF — Thu, 05 Sep 2024 20:46:18 GMT

Also having trouble using ilogic to export a custom iproperty to specific cell in excel. Worked for built-in properties but not working for the custom propertie "JOB".

Here is the line of code:

GoExcel.CellValue("I1") = iProperties.Value("Custom", "JOB")

Re: Export BOM to a excel Template

WCrihfield — Fri, 06 Sep 2024 12:42:09 GMT

Hi @dardine4UZSF. One of the most common issues when writing data from Inventor (or other source) into an Excel spreadsheet is data Type mismatch. This is especially the case when the destination Excel spreadsheet is pre-formatted for specific types of data in certain areas. The Property.Value property says its value Type is Object, but this us just because an iProperty can hold many different types of data, so it must not only specify one type as what it will return. Same goes for the GoExcel.CellValue function...it says its value is an Object, instead of any more specific data Type, because it can be many different types. However, on the Excel side, the cell (or range of cells, etc) can be formatted in a way where it will only accept specific data types. So, if you try to write a non-numeric String value from Inventor side into a cell in Excel that is formatted for Double or Date, it will throw an error. I always recommend getting the iProperty value to a variable first, then use that variable when writing the value to Excel. When you do it this way, you can check if the Value retrieved was Nothing or empty, and can 'Cast' it to the proper data Type ahead of time, to avoid any Type differences.

Re: Export BOM to a excel Template

dardine4UZSF — Fri, 06 Sep 2024 15:13:02 GMT

Got it to work. Come to find out I did not have these custom properties listed in my parts so it was toughing that error. Once I had the properties in the parts it worked out OK.

Re: Export BOM to a excel Template

dardine4UZSF — Fri, 06 Sep 2024 15:15:22 GMT

Yes, I change the code to write it as integer since my Job number is just a 5 digit number. Was able to push to the spreadsheet just fine then.

It still should have pushed as text though so I am still curious about that.