is there a way to make this run faster ?

[Darkforce_the_ilogic_guy]

I have this code it is look like it work .... but on big iam it is very slow.

is there a better and faster way to do the same 

Class BOMEXport
	Shared row As Integer = 2
Public Sub Main()

Dim oDoc As AssemblyDocument
oDoc = ThisApplication.ActiveDocument

Dim oBOM As BOM
oBOM = oDoc.ComponentDefinition.BOM

oBOM.StructuredViewEnabled = True
oBOM.StructuredViewFirstLevelOnly = False
oBOM.StructuredViewDelimiter = "."

oBOM.PartsOnlyViewEnabled = True

Dim oBOMView As BOMView
oBOMView = oBOM.BOMViews.Item("Structured")

Call ExportToExcel(oBOMView.BOMRows)
GoExcel.Save
GoExcel.Close
End Sub


Public Sub debug(txt As String)
	Trace.WriteLine("NTI : " & txt)
End Sub


Public Function ExportToExcel(bRows As BOMRowsEnumerator)

ExcelFile = "C:\Users\bt\Desktop\Prisberegner(Test version 5)i.xlsm"
ExcelSheet = "Rådata"
GoExcel.Open(ExcelFile, ExcelSheet)




Dim bRow As BOMRow
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")

Dim docPropertySet1 As PropertySet
docPropertySet1 = rDoc.PropertySets.Item("User Defined Properties")
'item , part number, qty, description, G_L (Custom), Raw material, Status (Custom)



Try
GoExcel.CellValue(ExcelFile, ExcelSheet, "A" & row) = bRow.ItemNumber
Catch
	
End Try
GoExcel.CellValue(ExcelFile, ExcelSheet, "B" & row) = docPropertySet.Item("Part Number").Value
Try
GoExcel.CellValue(ExcelFile, ExcelSheet, "C" & row) = bRow.ItemQuantity
Catch
	
End Try
Try
GoExcel.CellValue(ExcelFile, ExcelSheet, "D" & row) = docPropertySet.Item("Description").Value
Catch
	
End Try
Try
GoExcel.CellValue(ExcelFile, ExcelSheet, "E" & row) = docPropertySet1.Item("G_L").Value
Catch
	
End Try
Try
GoExcel.CellValue(ExcelFile, ExcelSheet, "F" & row) = docPropertySet1.Item("Raw Material").Value
Catch
	
End Try
Try
GoExcel.CellValue(ExcelFile, ExcelSheet, "G" & row) = docPropertySet1.Item("Status").Value
Catch
	
End Try



debug("Item nr. " & bRow.ItemNumber)' & "part number " &docPropertySet.Item("Part Number").Value & "qty " & bRow.ItemQuantity & "description " &  docPropertySet.Item("Description").Value & "Length " &  docPropertySet1.Item("G_L").Value & "Raw Material " & docPropertySet1.Item("Raw Material").Value & "Status " &docPropertySet1.Item("Status").Value)
 

row = row + 1

If Not bRow.ChildRows Is Nothing
	Call ExportToExcel(bRow.ChildRows)
End If
Next


End Function

End Class  

[A.Acheson]

@Darkforce_the_ilogic_guy 

How many items in the  assembly and what time is it taking for the process? I am guessing iterating through each line of the BOM adds to process time. Have you considered exporting the whole BOM then sorting/ deleting in excel? I use an export all then run macro filter in excel combination. I can check speeds later today. 

[Darkforce_the_ilogic_guy]

well I did that before manual .. but the problem is that I need the right properties in the right order.

and I need to add it into an already  exist excel what have advence VBA code and an sheet that have data form an large data base.

the second problem is that more people with that may not have so advence skill . So I need to to be as simple as possible to use .. with as few error as possible.

So what I trying to do is ... ckick an bottom ...

1. copy an standard templete(have not made this part jet)

2. add the Data form the IAM the right way to the files that you have just made form the templete

3. open the Excel files

4. Aktive first program ... so the programme add all know information form the database sheet

5. manualt add any missing data 

6. start second program that find with part of the machine you will have to make/buy

7. manualt change form buy to make if you disagree

8 Start 3.rd program that calulate the total price of your machine

more migth be add later

[NachoShaw]

Hey

I think the speed problem here he the connection to Excel as you will be limited to the speed of that. I had the same issue a few yesars ago when we made a reporting application using Excel. it would take about 10 mins to process the report into an Excel Doc for a small to medium size assembly. In the end we ditched excel and moved over to vb.net with a data report instead and that changed the 10 mins to about 10 seconds.

I wonder if writing to a text file from Inventor first then processing that into the excel document would improve speed? It may not and i havent tried it but seeing as the speed problem is an excel write for each BOM row, it might be worth looking at. In excel, you could have a function to call that would go thruogh the text file line by line and add the text to the required row.

just a thought

[A.Acheson]

@Darkforce_the_ilogic_guy 

Here is the attached workflow in zip folder. I have included instructions and an assembly file with a few items in it so you can see quickly the workflow. It is not using your code but it does allow a one click BOM creation. Any Questions, I am happy to help. 

Overall Workflow
1• Bom is exported to Temp macro enabled excel file called BOM unfiltered and Columns are sorted to match your xml file BOM_XML_TEMPLATE
2• Next macro is activated in BOM TEMPLATE and BOM unfiltered contents are copied to Macro Template
3• Unit string is removed and parts filtered for welding and Assembly BOM via the category iproperty and ADVANCED FILTER.
4• Any empty cells are highlighted
5• New excel file .xlsx is created called NEWBOM ready for import to MRP system

Steps To Setup:
1 Carry out an Export xml of Assy BOM Structured BOM to "C:\Temp" and save as name "BOM_XML_TEMPLATE". This will be used by the export in order to put the Columns in the correct order. Once complete the columns can be rearrange in the Assy Bom and this will not affect the excel columns. Alter as required.

2.Review the macros in the workbook in order to understand the functions. Excel file BOM Unsorted is opened and the contents copied to BOM TEMPLATE

2a• Search File Path "C:\Temp" in VBA editor of *Thisworkbook* in order to replace with one of your choice.
2b• Save BOM TEMPLATE file as this "C:\Temp\BOM TEMPLATE.xlsm"

[aronmatheus]

Hi @Darkforce_the_ilogic_guy,

Achieved we save as this excel file using the same name is contained the file in the project, in a different folder together with the idw and iam files?

'define the active document
oDoc = ThisDoc.Document

CurrentFileName = ThisDoc.PathAndFileName(False)

'create a file dialog box
Dim oFileDlg As Inventor.FileDialog = Nothing
InventorVb.Application.CreateFileDialog(oFileDlg)

Dim Filetype As String

'check file type and set dialog filter
If oDoc.DocumentType = kPartDocumentObject Then
oFileDlg.Filter = "Autodesk Inventor Part Files (*.ipt)|*.ipt"
Filetype = ".ipt"
ElseIf oDoc.DocumentType = kAssemblyDocumentObject Then
oFileDlg.Filter = "Autodesk Inventor Assembly Files (*.iam)|*.iam"
Filetype = ".iam"
ElseIf oDoc.DocumentType = kDrawingDocumentObject Then
oFileDlg.Filter = "Autodesk Inventor Drawing Files (*.idw)|*.idw"
Filetype = ".idw"
End If

'set the directory to open the dialog at
oFileDlg.InitialDirectory = ThisDoc.WorkspacePath()
'set the file name string to use in the input box
oFileDlg.FileName = ThisDoc.FileName(False) 'without extension

'work with an error created by the user backing out of the save
oFileDlg.CancelError = True
On Error Resume Next
'specify the file dialog as a save dialog (rather than a open dialog)
oFileDlg.ShowSave()

'catch an empty string in the imput
If Err.Number <> 0 Then
'MessageBox.Show("No File Saved.", "iLogic: Dialog Canceled")
ElseIf oFileDlg.FileName <> "" Then
MyFile = oFileDlg.FileName
'save the file
oDoc.SaveAs(MyFile, False) 'True = Save As Copy & False = Save As

NewDocPathName = ThisDoc.PathAndFileName(False)

'open original drawing
oDestinationDoc = ThisApplication.Documents.Open(CurrentFileName & ".idw")
oDestinationDoc.saveas(NewDocPathName & ".idw",False)
oDestinationDoc.Close

'open new drawing
oDestinationDoc = ThisApplication.Documents.Open(NewDocPathName & ".idw")
Dim oDocDescriptor As DocumentDescriptor
oDocDescriptor = oDestinationDoc.ReferencedDocumentDescriptors.Item(1)

Dim oFileDescriptor As FileDescriptor
oFileDescriptor = oDocDescriptor.ReferencedFileDescriptor

oFileDescriptor.ReplaceReference(NewDocPathName & Filetype)
oDestinationDoc.Update()
oDestinationDoc.Save
End If
InventorVb.DocumentUpdate()

oADoc = ThisApplication.ActiveDocument

Dim oLast3Chars As String
For Each oRefDoc As Document In oADoc.AllReferencedDocuments
ThisApplication.Documents.Open(oRefDoc.FullFileName,False)
oLast3Chars = Left(Right(oRefDoc.FullFileName, 7), 3)
If oRefDoc.DocumentType = DocumentTypeEnum.kPartDocumentObject Then
Dim oRefPDoc As PartDocument = oRefDoc
Dim oRefPDef As PartComponentDefinition = oRefPDoc.ComponentDefinition
If oRefPDef.IsContentMember = False Then
oRefDoc.SaveAs(Left(oADoc.FullFileName, Len(oADoc.FullFileName) -4) & oLast3Chars & ".ipt", False)
End If
End If
oRefDoc.Close
Next
Dim oOccDoc As Document
Dim oOccNewFileName As String
For Each oOcc As ComponentOccurrence In oADoc.ComponentDefinition.Occurrences
oOccDoc = oOcc.Definition.Document
oLast3Chars = Left(Right(oOccDoc.FullFileName, 7), 3)
If oOccDoc.DocumentType = DocumentTypeEnum.kPartDocumentObject Then
Dim oOccPDef As PartComponentDefinition = oOcc.Definition
If oOccPDef.IsContentMember = False Then
oOccNewFileName = Left(oADoc.FullFileName, Len(oADoc.FullFileName) -4) & oLast3Chars & ".ipt"
End If
End If
oOcc.Replace(oOccNewFileName, False)
Next

[TONELLAL]

I had this problem too, but in VBA not iLogic. I solved it using an array instead of writing each cell in Excel.

For example, if you need to write in the area [A1-G50] in Excel, you create an array [1-7,1-50], you work in your array, then you paste it in Excel in one go.

I use this an large assemblies, it takes seconds instead of minutes !