ilogic to specific column in excell termplate

[ChristinaForest]

hi!

i want to export my pârt list with ilogic in a excell template but dont want all the part list column and dont erase the column i skip in excell, please help me???

Sorry for my poor english!

[Daren.Lawrence]

There’s a few ways to go about this with iLogic depending on your comfort level with VB and the size of the task: First, there’s a snippet under “BOM” in iLogic that uses ThisBom.export that allows export to many formats including Excel – see attached image. There’s also a great ‘catch all’ VB macro on our being Inventive blog that Bob Van der Donck wrote that will also handle all levels including iAssemblies that may be subassemblies in your top-level assembly at this location:

http://beinginventive.typepad.com/being-inventive/2011/11/export-parts-list-to-excel-in-vba.html

It contains the following Macro for Visual Basic:

Dim oDoc As AssemblyDocument

oDoc = ThisApplication.ActiveDocument

' Set a reference to the BOM

Dim oBOM As BOM

oBOM = oDoc.ComponentDefinition.BOM

' Set the structured view to 'all levels'

oBOM.StructuredViewFirstLevelOnly = False

' Make sure that the structured view is enabled.

oBOM.StructuredViewEnabled = True

' Set a reference to the "Structured" BOMView

Dim oStructuredBOMView As BOMView

oStructuredBOMView = oBOM.BOMViews.Item("Structured")

'Export the BOM view to an Excel file

oStructuredBOMView.Export ThisApplication.FileLocations.Workspace + "\BOM.xls", kMicrosoftExcelFormat

We can change the syntax slightly to make it consumable for iLogic (taking out the “set”s and putting the argument in the last line in parenthesis), so it looks like this:

' Set a reference to the assembly document.

' This assumes an assembly document is active.

Dim oDoc As AssemblyDocument

oDoc = ThisApplication.ActiveDocument

' Set a reference to the BOM

Dim oBOM As BOM

oBOM = oDoc.ComponentDefinition.BOM

' Set the structured view to 'all levels'

oBOM.StructuredViewFirstLevelOnly = False

' Make sure that the structured view is enabled.

oBOM.StructuredViewEnabled = True

' Set a reference to the "Structured" BOMView

Dim oStructuredBOMView As BOMView

oStructuredBOMView = oBOM.BOMViews.Item("Structured")

' Export the BOM view to an Excel file

oStructuredBOMView.Export (ThisApplication.FileLocations.Workspace + "\BOM.xls", kMicrosoftExcelFormat)

Then there’s the matter of not displaying the column headers as you want it to appear without deleting them from the drawing’s BOM itself and this, again, can be done based on your comfort level with programming and the need. For instance you could read all lines in from the xls file produced, put it into a two-dimensional array, do a for/next loop or a do while loop to or some other method to get rid of the unwanted cells or I’m quite sure there’s a command in Excel to delete an entire row – something like EntireRow.delete, but in this case where we’ve got very few cells that we need to delete the data from, it may be just as quick (and easier) to simply write a line (using the GoExcel.CellValue snippet in iLogic under “Excel Data Links”) and set the values to “” (nothing - or empty string) and then copy and paste that line, changing the cell from “A1” to “B1” to “C1”,  etc. and save the Excel file and close Excel. So that part might look like this:

GoExcel.CellValue(ThisApplication.FileLocations.Workspace + "\BOM.xls", "Sheet1", "A1") = ""

GoExcel.CellValue(ThisApplication.FileLocations.Workspace + "\BOM.xls", "Sheet1", "B1") = ""

GoExcel.CellValue(ThisApplication.FileLocations.Workspace + "\BOM.xls", "Sheet1", "C1") = ""

GoExcel.CellValue(ThisApplication.FileLocations.Workspace + "\BOM.xls", "Sheet1", "D1") = ""

GoExcel.CellValue(ThisApplication.FileLocations.Workspace + "\BOM.xls", "Sheet1", "E1") = ""

GoExcel.CellValue(ThisApplication.FileLocations.Workspace + "\BOM.xls", "Sheet1", "F1") = ""

GoExcel.CellValue(ThisApplication.FileLocations.Workspace + "\BOM.xls", "Sheet1", "G1") = ""

GoExcel.CellValue(ThisApplication.FileLocations.Workspace + "\BOM.xls", "Sheet1", "H1") = ""

GoExcel.Save

GoExcel.Close

And then the whole thing looks like this in iLogic:

' Set a reference to the assembly document.

' This assumes an assembly document is active.

Dim oDoc As AssemblyDocument

oDoc = ThisApplication.ActiveDocument

' Set a reference to the BOM

Dim oBOM As BOM

oBOM = oDoc.ComponentDefinition.BOM

' Set the structured view to 'all levels'

oBOM.StructuredViewFirstLevelOnly = False

' Make sure that the structured view is enabled.

oBOM.StructuredViewEnabled = True

' Set a reference to the "Structured" BOMView

Dim oStructuredBOMView As BOMView

oStructuredBOMView = oBOM.BOMViews.Item("Structured")

' Export the BOM view to an Excel file

oStructuredBOMView.Export (ThisApplication.FileLocations.Workspace + "\BOM.xls", kMicrosoftExcelFormat)

GoExcel.CellValue(ThisApplication.FileLocations.Workspace + "\BOM.xls", "Sheet1", "A1") = ""

GoExcel.CellValue(ThisApplication.FileLocations.Workspace + "\BOM.xls", "Sheet1", "B1") = ""

GoExcel.CellValue(ThisApplication.FileLocations.Workspace + "\BOM.xls", "Sheet1", "C1") = ""

GoExcel.CellValue(ThisApplication.FileLocations.Workspace + "\BOM.xls", "Sheet1", "D1") = ""

GoExcel.CellValue(ThisApplication.FileLocations.Workspace + "\BOM.xls", "Sheet1", "E1") = ""

GoExcel.CellValue(ThisApplication.FileLocations.Workspace + "\BOM.xls", "Sheet1", "F1") = ""

GoExcel.CellValue(ThisApplication.FileLocations.Workspace + "\BOM.xls", "Sheet1", "G1") = ""

GoExcel.CellValue(ThisApplication.FileLocations.Workspace + "\BOM.xls", "Sheet1", "H1") = ""

GoExcel.Save

GoExcel.Close

[ChristinaForest]

THANK YOU VERY MUCH FOR YOUR REPLY, I TRY THAT WHEN MY RUSH JOB IS FINISH 🙂

[pecmichal]

How can I import BOM on specified place (for example first cell is B3).

Can I somehow export with BOM the STAMP of inventor sheet? I would like to add some iProperties to the head of BOM in Excel file. 

[Cj71968]

Hi,

I used the inventor 2014 and the vba code doesn't work.

First think I have always error when I used this commmand: oBOM.StructuredViewFirstLevelOnly = False. I think I now Obselete...

Other think.

I used same code as others to export LOD"s Bom to Excel. It does not work in 2014 inventor. For fun I tried in master LOD and I have no error. I check 

the count bom view with oNumberView = oBOM.BOMViews.count and value = 3 Normal. But In ILogic LOD the count is 1 with unknow as name....

IF I put iasm to master LOD and run the code, the Ilogic lLOD was activated this parts works good.

Somebody can help me

Sorry for my bad English.

Here the code :

oDoc = ThisApplication.ActiveDocument

 ' If the current non-master LOD document is dirty you should save it before you can use the master-LOD document background
    If oDoc.Dirty Then
       oDoc.Save
    End If
    
    Dim sFileName As String
    sFileName = oDoc.FullFileName
'Dim oRepMgr As RepresentationsManager  '*****activate the ilogic lod
    oRepMgr = oDoc.ComponentDefinition.RepresentationsManager

Dim oIlogicLOD As LevelOfDetailRepresentation
oIlogicLOD = oRepMgr.LevelOfDetailRepresentations.Item("iLogic")
oIlogicLOD.Activate()

    Dim oBOM As BOM
    
        'set a reference to the BOM
    Dim oAsmCompDef As AssemblyComponentDefinition
    oAsmCompDef = ThisDoc.Document.ComponentDefinition
   oBOM = oDoc.ComponentDefinition.BOM

    oBom.StructuredViewEnabled=True

Dim oStructuredBOMView As BOMView

'oStructuredBOMView = oBOM.BOMViews.Item("Structured")
oNumberView = oBOM.BOMViews.count
MessageBox.Show(oname, "CountBomViews")