iLogic to Export BOM from Assembly to an excel template

[Anonymous]

Hi Guys, 

I've a folliwing problem to solve. 

I have a working iLogic rule which export BOM (properly modified part only view) to an excell file and then starts a macro inside this file. 

The rule is checking for purchased parts and skips them and then export data row by row. 

All is working quite good but there is one issue I'd like to solve somehow. 

I'd like to sort parts in BOM part view first and then export them to excell maintaining proper order. 

Below You can see code snippet: 

SyntaxEditor Code Snippet

If ThisApplication.ActiveDocument.DocumentType <> kAssemblyDocumentObject Then

UserChoice1 = MessageBox.Show ( "Regu�a generuje Kart� Ci�cia dla Z�o�e�!" & vbLf & "" & vbLf & "Czy chcesz wygenerowa� Kart� Ci�cia dla pojedy�czej cz�ci?", "iLogic",MessageBoxButtons.YesNo)

    If UserChoice1 = vbYes Then

    iLogicVb.RunExternalRule("C:\Users\t.palasz\Desktop\INVENTOR iLOGIC\Karta Ci�cia Cz��.txt")
    Exit Sub

    Else
    Exit Sub
    End If

End If    

FilePath = ThisDoc.Path

    If FilePath = "" Then
    
    MessageBox.Show("Plik z�o�enia nie zosta� zapisany!" & vbLf & " " & vbLf & "Zapisz plik, nast�pnie uruchom regu�� ponownie.", "iLogic")
    
    Exit Sub
    
    End If

UserChoice2 = MessageBox.Show ( "Czy chcesz wygenerowa� Kart� Ci�cia?" & vbLf & " " & vbLf & "Upewnij si� �e wszystkie niezb�dne dane zosta�y uzupe�nione.", "iLogic",MessageBoxButtons.YesNo)

    If UserChoice2 = vbNo Then
    
    Exit Sub
    
    End If

Dim oAssyDef As AssemblyComponentDefinition

oAssyDef = ThisApplication.ActiveDocument.ComponentDefinition

Dim oBOM As BOM

oBOM = oAssyDef.BOM

oBOM.PartsOnlyViewEnabled = True

Dim oBOMView As BOMView

oBOMView = oBOM.BOMViews.Item("Tylko cz�ci")

Dim oBOMRow As BOMRow

'Kopiowanie danych zawartych w li�cie BOM do pliku Exel'

GoExcel.Open("C:\Users\t.palasz\Desktop\Karta Ci�cia Wz�r.xlsm","Arkusz2")

i=3

For Each oBOMRow In oBOMView.BOMRows

    If oBOMRow.BOMStructure <> 51973 Then

    NumerPozycji=oBOMRow.ItemNumber
 
    Dim oCompDef As ComponentDefinition
    
    oCompDef = oBOMRow.ComponentDefinitions.Item(1)
    
    FullFileName  = oCompDef.Document.FullFileName
    
    Pozycja=InStrRev(FullFileName,"\", -1)
    
    NazwaModelu=Right(FullFileName,Len(FullFileName)-Pozycja)
 
    NumerCzesci=iProperties.Value(NazwaModelu, "Project", "Part Number")
    
    Opis=iProperties.Value(NazwaModelu, "Project", "Description")
    
    Try
    Nazwa=iProperties.Value(NazwaModelu, "Custom", "Nazwa")
    Catch
    End Try
    
    Try
    Rodzaj=iProperties.Material(NazwaModelu)
    Catch
    End Try
    
    Try
    Wymiar=iProperties.Value(NazwaModelu, "Custom", "Wymiar")
    Catch
    End Try

    Try
    Uwagi=iProperties.Value(NazwaModelu, "Custom", "Uwagi")
    Catch
    End Try
    
    Ilosc=oBOMRow.Totalquantity
    
    GoExcel.CellValue("A" & i)=NumerPozycji
    
    GoExcel.CellValue("B" & i)=Opis
    
    GoExcel.CellValue("C" & i)=NumerCzesci
    
    GoExcel.CellValue("D" & i)=Nazwa
    
    GoExcel.CellValue("E" & i)=Rodzaj
    
    GoExcel.CellValue("F" & i)=Wymiar
    
    GoExcel.CellValue("H" & i)=Uwagi
    
    GoExcel.CellValue("G" & i)=Ilosc
    
    i=i+1
    
    End If
    
Next

    Select Case i
    
    Case 1 To 12
    
    Iloscstr = 1
    
    Case 13 To 24
    
    Iloscstr = 2
    
    Case 25 To 36
    
    Iloscstr = 3
    
    Case 37 To 48
    
    Iloscstr = 4
    
    Case 49 To 60
    
    Iloscstr = 5
    
    End Select

    Try
    Artykul = iProperties.Value("Project", "Stock Number")
    Catch
    End Try    
    
    GoExcel.CellValue("A1")=iProperties.Value("Project", "Part Number")
    
    GoExcel.CellValue("B1")= Iloscstr
    
    GoExcel.CellValue("A2")= "Art. Nr" & " " & Artykul

    GoExcel.CellValue("D1")=iProperties.Value("Project", "Description")

    GoExcel.DisplayAlerts = False
    
GoExcel.Save
GoExcel.Close

'Uruchomienie Makra zaimplementowanego w akruszu Excel'

Dim ExApp As Object
        
        ExApp = CreateObject("Excel.Application")
        
        ExApp.workbooks.Open ("C:\Users\t.palasz\Desktop\Karta Ci�cia Wz�r.xlsm")
        
        ExApp.Application.Run ("KartaCieciaWzor")
        
        ExApp.Visible = False
      
        ExApp = Nothing

MessageBox.Show("Karta ci�cia zosta�a wygenerowana pomy�lnie", "Gratulacje")

Thanks in Advance for any help

Tomek

[mcgyvr]

See API/Programming help for

BOMView.Sort

BOMView.Sort Method BOMView.Sort( PrimaryColumnTitle As String, [PrimaryColumnAscending] As Boolean, [SecondaryColumnTitle] As String, [SecondaryColumnAscending] As Boolean, [TertiaryColumnTitle] As String, [TertiaryColumnAscending] As Boolean )

[Anonymous]

I've tried this. Indeed BOM gets sorted as it should. But then rule starts to export data row by row to the excell file with primary order, it looks it doesn't see that sorting was made. I don't know why. 

Regards 

Tomek

[Curtis_Waguespack]

Hi @Anonymous,

Rather than using the GOExcel ilogic functions, try using the API call for exporting the BOMView. This allows you to export the columns in a specified order using the Options variant.

This link has a similar example using the parts list rather than the BOMView:

http://inventortrenches.blogspot.com/2011/06/ilogic-export-parts-list-with-options.html

This links has a BOMView export example, but it is not using options, but again specifying the options would be much like the previous example for parts list:

http://help.autodesk.com/view/INVNTOR/2018/ENU/?guid=BOMView_Export_Sample

Here is the BOMView export API help page:

http://help.autodesk.com/view/INVNTOR/2018/ENU/?guid=GUID-C689C910-D089-4CAA-9513-01CB946D1661

Note too that the Inventor Customization forum is likely the better place for questions about iLogic and the programming issues:
http://forums.autodesk.com/t5/Autodesk-Inventor-Customization/bd-p/120

I hope this helps.
Best of luck to you in all of your Inventor pursuits,
Curtis
http://inventortrenches.blogspot.com

[Anonymous]

Hi Curtis, 

Thanks for fast reply and some tips. 

Due to part list export - I want to aviod it because I need to export some custom iProperties which I don't want to be visible in the drawing table. Also I'm filling some of those iProperties directly through BOM in the assembly file. So it's much faster to triger the rule directly inside the assembly file instead to oppenning the drawing file.

The reson I don't use an export option is that it takes all parts from the BOM view - I want to exclude purchased parts to avoid manual deleting rows inside an excell file.

If there is a simple way to solve both described problems - I'm open for any ideas. 

I'll try to take better attantion to proper post location next time. I'm a rookie right now so I need some time to get use to this forum. 

Greetings 

Tomek