Ilogic code to export BOM to excel file overwrites data in excel file

[Anonymous]

I am trying to create Ilogic code that opens an excel template file (with existing data in multiple tabs), performs a save as (Ilogic handles the naming schema), then exports the structured BOM to an existing tab in the new file. The open and save as perform without issue, but I can't get the BOM to export without one of the following issues occurring.

1. The BOM is written to the new excel file but all tabs and pre-existing data are lost.

Or....

2. When exporting the BOM I am prompted that the file I am writing to is read only, and asked to do a save as. If I save as, all the data is retained and a new tab is created containing the BOM. This is not ideal because I am prompted for a file name during the save as and the BOM is not saved to the tab I need it to be.

Any help is appreciated!

[ShayaGhanbar]

Make sure that the excel file is closed while running the iLogic. By default, Inventor iLogic dumps your data to the designated excel file without sorting the data based on your preference.

You need to do some VBA programming in order to define the exact location in the spreadsheet where your data goes to. 

This will give you an idea on how to export a parts list:

http://inventortrenches.blogspot.ca/2012/06/create-new-excel-file-with-ilogic.html

Did you find this reply helpful ? If so please use the Accept as Solution or Kudos button below.

[Anonymous]

Shaya,

Thanks for the help. I am actually using that code to open and perform a save as of my template file. Since the code is writing data to specific cells I couldn't figure out how to export my BOM with that code, so I decided to try and write another rule to export the BOM.

Any idea of how to use that code to export the BOM?

[Curtis_Waguespack]

Hi jeff.Aguilera,

You can search and ask programming questions of this type on the Inventor Customization forum too:
http://forums.autodesk.com/t5/Autodesk-Inventor-Customization/bd-p/120

Here are several related links:

https://forums.autodesk.com/t5/forums/searchpage/tab/message?advanced=false&allow_punctuation=false&...

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

[ShayaGhanbar]

Hey Jeff,

This is a VB that I created:

Public Sub Main()

Dim oDoc As AssemblyDocument
Set oDoc = ThisApplication.ActiveDocument

Dim oBOM As BOM
Set oBOM = oDoc.ComponentDefinition.BOM

oBOM.PartsOnlyViewEnabled = True

Dim oBOMView As BOMView
Set oBOMView = oBOM.BOMViews.Item("Parts Only")

Call ExportToExcel(oBOMView.bomRows)
End Sub

Public Function ExportToExcel(bRows As BOMRowsEnumerator)
Dim xlApp As Excel.Application
Set xlApp = GetObject("", "Excel.Application.15")

Dim xlWorkbook As Workbook
Set xlWorkbook = xlApp.Workbooks.Open("C:\SolidCAD\Inventor Training Files\iLOGIC\Beam\Beam Assembly - BOM.xls")

Dim xlWorksheet As WorkSheet
Set xlWorksheet = xlWorkbook.Worksheets.Item("TEST")

Dim row As Integer
row = 5

xlWorksheet.Range("B4").Value = "ITEM"
xlWorksheet.Range("C4").Value = "QTY"
xlWorksheet.Range("D4").Value = "DESC"
xlWorksheet.Range("E4").Value = "Part Number"

Dim bRow As bomRow
For Each bRow In bRows
Dim rDoc As Document
Set rDoc = bRow.ComponentDefinitions.Item(1).Document

Dim docPropertySet As PropertySet
Set docPropertySet = rDoc.PropertySets.Item("Design Tracking Properties")


xlWorksheet.Range("B" & row).Value = bRow.ItemNumber
xlWorksheet.Range("C" & row).Value = bRow.ItemQuantity
xlWorksheet.Range("D" & row).Value = docPropertySet.Item("Description").Value
xlWorksheet.Range("E" & row).Value = docPropertySet.Item("Part Number").Value

row = row + 1
Next

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

End Function

This should give you a good idea of what's involved. 

Did you find this reply helpful ? If so please use the Accept as Solution or Kudos button below.

[Anonymous]

Shaya,

I'm getting the following errors when I tried to run this code.

Line 16: Type 'Excel.Application' is not defined

Line 18: Type 'Workbook' is not defined

Line 21: Type 'Worksheet' is not defined

[Anonymous]

Curtis,

Thanks for the response, I've been thru those forums as well as your blog. I can get the BOM to export, it's just either the pre-existing excel data gets lost or the BOM gets inserted into a new tab that wasn't originally in the excel template I'm using.

I can't seem to find an example where someone needs the BOM to go to specific tab in an excel file with existing data.

[ShayaGhanbar]

You need to change this: Set xlWorkbook = xlApp.Workbooks.Open("C:\SolidCAD\Inventor Training Files\iLOGIC\Beam\Beam Assembly - BOM.xls") to another location on your computer where there is an excel file with a tab called TEST.

[Anonymous]

Shaya,

I had done that prior to running the rule for the first time....

[Curtis_Waguespack]

---

@Anonymous wrote:

2. When exporting the BOM I am prompted that the file I am writing to is read only, and asked to do a save as. If I save as, all the data is retained and a new tab is created containing the BOM. This is not ideal because I am prompted for a file name during the save as and the BOM is not saved to the tab I need it to be.

---

Hi jeff.Aguilera,

Case 2 might occur because your early attempts have opened the Excel invisibly and the code failed without closing it, leaving it open and read only. CTRL + ALT Delete and check your Task Manager for Excel Processes and kill them, if this is the case.

Then try this modified version of ShayaGhan's code, that I tweaked to make it more iLogic friendly. It also makes the Excel file visible which is handy during testing.

• But as I understand it, you want to use a template, not an existing file, correct? ShayaGhan's example is using an existing file.
• And you want to do this from an assembly, so you must use the BOM, rather than from the Drawing where you could use the Parts List, correct?

I'm short on time, but might be able to get back to this later.

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

Dim oDoc As AssemblyDocument
oDoc = ThisApplication.ActiveDocument

Dim oBOM As BOM
oBOM = oDoc.ComponentDefinition.BOM

oBOM.PartsOnlyViewEnabled = True

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

xlApp = CreateObject("Excel.Application")

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

xlWorkbook = xlApp.Workbooks.Open("C:\Temp\Test.xlsx")

xlWorksheet = xlWorkbook.Worksheets.Item("Sheet1")

Dim row As Integer
row = 5

xlWorksheet.Range("B4").Value = "ITEM"
xlWorksheet.Range("C4").Value = "QTY"
xlWorksheet.Range("D4").Value = "DESC"
xlWorksheet.Range("E4").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("B" & row).Value = bRow.ItemNumber
	xlWorksheet.Range("C" & row).Value = bRow.ItemQuantity
	xlWorksheet.Range("D" & row).Value = docPropertySet.Item("Description").Value
	xlWorksheet.Range("E" & row).Value = docPropertySet.Item("Part Number").Value
	
	row = row + 1
Next

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

[Curtis_Waguespack]

update:

I realized after I posted that maybe you wanted to use an existing file (not template) and just do a save as, if so something like this...

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

Dim oDoc As AssemblyDocument
oDoc = ThisApplication.ActiveDocument

Dim oBOM As BOM
oBOM = oDoc.ComponentDefinition.BOM

oBOM.PartsOnlyViewEnabled = True

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

xlApp = CreateObject("Excel.Application")

'comment out or change to false 
'in order to not show Excel
xlApp.Visible = True 
xlWorkbook = xlApp.Workbooks.Open("C:\Temp\Test.xlsx")

xlWorksheet = xlWorkbook.Worksheets.Item("Sheet1")

Dim row As Integer
row = 5

xlWorksheet.Range("B4").Value = "ITEM"
xlWorksheet.Range("C4").Value = "QTY"
xlWorksheet.Range("D4").Value = "DESC"
xlWorksheet.Range("E4").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("B" & row).Value = bRow.ItemNumber
	xlWorksheet.Range("C" & row).Value = bRow.ItemQuantity
	xlWorksheet.Range("D" & row).Value = docPropertySet.Item("Description").Value
	xlWorksheet.Range("E" & row).Value = docPropertySet.Item("Part Number").Value
	
	row = row + 1
Next

oNow = DateString & "_" & TimeString
oNow = oNow.Replace("/","_")
oNow = oNow.Replace(":","_")
xlWorkBook.SaveAs(Filename:="C:\Temp\SampleNew_" & oNow & ".xlsx")

xlWorkbook.Close (False)
xlApp.Quit

[Anonymous]

Curtis,

Thanks! This works great.

Is there a reference for finding the proper format for calling the below properties? I was able to get the vendor data to populate by following the docPropertySet format but I get an error when trying to populate the revision and eng. release date.

bRow.ItemNumber
bRow.ItemQuantity
docPropertySet.Item("Description").Value
docPropertySet.Item("Part Number").Value

And yes you are correct, I am trying to copy an excel template file, populate with BOM data from the .iam file, and perform a save as. The reason is we just started using Fusion Lifecycle. BOM data has to be imported into Lifecycle and we have been told by our re-seller and Autodesk that this is a manual process and has not been automated. So I'm trying to use Ilogic to automate this process, but I don't necessarily need to stick with the method I've been working on.

Initially I was using Ilogic to import an xml file (to get the BOM columns I need), sorting and renumbering the BOM, and copying an excel template file (this would format the BOM data for Lifecycle). The next step was to import the BOM data into this spreadsheet, but as you can see this is where I was getting stuck. But with your code I should be able to achieve all of this in one rule and eliminate the need to import an xml file.

[Curtis_Waguespack]

---

@Anonymous wrote:

Is there a reference for finding the proper format for calling the below properties?...

---

Hi jeff.aguilera,

I think those properties are in a different property set, see this link for a reference:

http://download.autodesk.com/us/community/mfg/Part_4.pdf

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

[Anonymous]

hello , i have seen you code,and it works well, but now i want export another properties like material, mass,keywords etc. how to make it?  thanks forward!!

	xlWorksheet.Range("B" & row).Value = bRow.ItemNumber
	xlWorksheet.Range("C" & row).Value = bRow.ItemQuantity
	xlWorksheet.Range("D" & row).Value = docPropertySet.Item("Description").Value
	xlWorksheet.Range("E" & row).Value = docPropertySet.Item("Part Number").Value

[MechMachineMan]

Here is a potential solution for just directly exporting the BOM.

Is currently excluding error checking, so if there is issues

a) exporting the BOM from inventor,

b)opening excel,

c) grabbing the workbooks or

d) renaming the sheet,

it will cause the program to exit.

It also only works for a structuredBOM with full levels, and will move it to the last sheet, renamed to the 2nd arguement in the calling method.

Sub Main()
    
    ExportBOMToExistingExcel("C:\Users\JRK\Desktop\HCD BOM.xlsx", "$BOM")

End Sub

Sub ExportBOMToExistingExcel(oxlName As String, oxlSheet As String)
    'ExportBOMToExistingExcel - by MechMachineMan on Inventor Customization Forums
    'Purpose: To export a BOM to an existing excel file as a workaround to inventor's native behaviour of
    '          wiping the destination file of the export.
    'Method:  This sub will simply create a temporary excel file, and then copy the sheet to the destination document.
  
    'Change this temp file to something that works for you better, if desired. 
    'Is currently a Generic location To make it portable.
    oTemporaryExcelFile = "C:\Users\Public\Documents\iLogic Buffer File.xlsx"
    
    Dim xlApp As Object
    Dim workingwb As Object
    
    Try    
        ExportBOMToTemp(oTemporaryExcelFile)
    
        xlApp = CreateObject("Excel.Application")
    
        workingwb = xlApp.Workbooks.Open(oxlName)
    
        sourcewb = xlApp.Workbooks.Open(oTemporaryExcelFile)
        sourcews = sourcewb.Sheets(1)
    
        sourcews.Copy(After:=workingwb.Sheets(workingwb.Sheets.Count))
        workingws = workingwb.Sheets(workingwb.Sheets.Count)
        workingws.Name = oxlSheet   
        
        sourcewb.Close()
    Catch
        MsgBox("Error During Export Encountered!")
    
    End Try
    
    Try
        xlApp.Visible = True    
        workingwb = Nothing
        xlApp = Nothing
    Catch
    End Try
    
    releaseObject(workingws)
    releaseObject(sourcewb)
    releaseObject(workingwb)
    releaseObject(xlApp)
    
End Sub

Sub ExportBOMToTemp(oTempFile As String)
    'Do not mod where tempfile appears; only mod to get parts only if desired.
    Dim oDoc As AssemblyDocument
    oDoc = ThisDoc.Document

    Dim oBOM As BOM
    oBOM = oDoc.ComponentDefinition.BOM
    
    oBOM.StructuredViewFirstLevelOnly = False
    oBOM.StructuredViewEnabled = True

    Dim oStructuredBOMView As BOMView
    oStructuredBOMView = oBOM.BOMViews.Item("Structured")
    
    oStructuredBOMView.Export(oTempFile, kMicrosoftExcelFormat)
End Sub

Sub releaseObject(ByVal obj As Object)
    Try
        System.Runtime.InteropServices.Marshal.ReleaseComObject(obj)
        obj = Nothing
    Catch ex As Exception
        obj = Nothing
    Finally
        GC.Collect()
    End Try
End Sub

[idealogicERZYZ]

Hi, 

The script does not want to export purchased and apparent parts.

Any idea ....

[MechMachineMan]

The code is supposed to export the ENTIRE structured BOM tab. Please verify the virtual components you are wanting to export are showing up in your STRUCTURED BOM View... Perhaps they are set to reference somehow...

[idealogicERZYZ]

So they appear.
With the Export icon - the structure is exported.
Code rule no longer exports.

[Anonymous]

Hi Curtis

I'm currently trayng to adapt Your's code for creating parts list. What i would like to do is to write list to a embedded 3rdt Party xls file in a assembly so i could create a template of these assembly. In company that I work we have to make for every project 3 lists: Structured without connectors screws, nuts and so on; parts only list without  connectors screws, nuts and so on; and finaly only connectors list. Could it be done?