Community
Inventor Forum
Welcome to Autodesk’s Inventor Forums. Share your knowledge, ask questions, and explore popular Inventor topics.
cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 

Populating an existing spreadsheet with BOM data using iLogic

7 REPLIES 7
SOLVED
Reply
Message 1 of 8
Herve_l
1180 Views, 7 Replies

Populating an existing spreadsheet with BOM data using iLogic

I already have a cut list  template in Excel that calculates my stock material,  volume,  etc when I populate the appropriate fields. It is also connected to an access database that has various information that automatically generates the final price of my product based on various suppliers prices and data with whom I do business with and so on.

 

I would like to know if there was a way with iLogic to make Inventor populate specific cells with information found in the BOM such as: description; Qty and custom iProperties value like dimensions, etc?

 

The reason why I'm making a new topic out of this is that I have read previous post about the issue, but they were regarding "exporting the BOM to an excel". I, on the other hand, would like to populate an already existing spreadsheet with data from the BOM

After exploring the iLogic snippets, I've only found the "export" option. It referenced the API snippets, but with limited programming knowledge there is not much I can understand.

 

 

Thank you in advance. 

 

7 REPLIES 7
Message 2 of 8
MjDeck
in reply to: Herve_l

You should be able to use code from this sample rule:

 


Dim bomFileName As String = ThisDoc.ChangeExtension(".xls")
ThisBOM.Export("Parts Only", bomFileName, kMicrosoftExcelFormat)

GoExcel.Open(bomFileName, "Sheet1")
Dim excelSheet = GoExcel.Application.ActiveWorkbook.ActiveSheet
Dim valArray As Array = CType(excelSheet.UsedRange.Value, Array)
GoExcel.Close()

Dim outputFileName As String = IO.Path.Combine(ThisDoc.Path, "CopiedBom.xls")

GoExcel.Open(outputFileName, "Sheet1")
For row As Integer = 1 To valArray.GetUpperBound(0)
If (valArray(row,1) = Nothing) Then Exit For
For columnIndex As Integer = 1 To valArray.GetUpperBound(1)
Dim column As String = Chr(Asc("A"c) + columnIndex-1)
'Trace.WriteLine("column = " & column)
GoExcel.CellValue(outputFileName, "Sheet1", column & row) = valArray(row, columnIndex)
Next
Next
GoExcel.Save()
GoExcel.Close()

This will export the BOM and then read all the data and copy it to another Excel file (CopiedBom.xls, which must already exist).  You can adapt it to extract the data you want (from valArray) and copy it to specific cells in the output sheet.

 


Mike Deck
Software Developer
Autodesk, Inc.

Message 3 of 8
Herve_l
in reply to: MjDeck

Thank you.

I understand the general process now

Message 4 of 8
MarkStorm2
in reply to: MjDeck

I would like to do exactly the same, but as i'm a complete newby in writing code, and so i have problems with adjusting the code to my preferances.

 

I need to be able to write from the BOM-list only the 'Partnumbers' from 'assembly A' to 'row 4, column E' on 'Sheet1', and 'Assembly B' to the same cells, only on 'Sheet2'.

 

I tried multiple times to adjust this myself, but the lack of experiance gets me nowhere right now.

 

Could you point me in the right direction?

 

 

Message 5 of 8
intern2UN3U3
in reply to: MarkStorm2

 Hello,

I am new to iLogic and would like if someone could comment/explain the For loop and array that is being used

Tags (1)
Message 6 of 8
intern2UN3U3
in reply to: MjDeck

Could you please explain the loop and array you are using to transfer the data from one excel to the next. 

I am new to using ilogic and it would be a great help. I have a template in excel that I want to transfer in BOM details. I have code that generates the Bom from exporting and then the separate excel template that I need the data to go. I didn't see anything to export the Bom to that excel so I think is the way to do it. I just need a little help 

Message 7 of 8
MjDeck
in reply to: intern2UN3U3

Hi @intern2UN3U3,

Here's what the rule is doing:
- export the BOM to a file
- read all the cells from that file into a 2-dimensional array (using excelSheet.UsedRange.Value)

- loop over the rows and columns of that 2D array, and write each value one at a time to another Excel spreadsheet.

 

What does your Excel template look like? How do you want the BOM data to fit into it?


Mike Deck
Software Developer
Autodesk, Inc.

Message 8 of 8

Maybe you were asking for a step-by-step walkthrough. If you're new to programming, or maybe just arrays, here's a quick commented version that might give a little insight. 

 

'all my comments look like this 🙂

 

'This first bit exports out the existing Parts Only BOM 
'to an Excel File named the same thing As the iam File
Dim bomFileName As String = ThisDoc.ChangeExtension(".xls")
ThisBOM.Export("Parts Only", bomFileName, kMicrosoftExcelFormat)

'next, open that excel file back up
GoExcel.Open(bomFileName, "Sheet1")
Dim excelSheet = GoExcel.Application.ActiveWorkbook.ActiveSheet

'and create a 2-dimensional array based on the entries in Excel. 
'Arrays are 0-based, which means 
'valArray(0) is the first dimension, and will contain row data
'valArray(1) is the second dimension, and will contain data in the columns
Dim valArray As Array = CType(excelSheet.UsedRange.Value, Array)
'and close Excel again
GoExcel.Close()

'now open up a second Excel file 'CopiedBom.xls' to write data to
Dim outputFileName As String = IO.Path.Combine(ThisDoc.Path, "CopiedBom.xls")
GoExcel.Open(outputFileName, "Sheet1")

'here's where you work through each row....
'the GetUpperBound(0) gives you the number of entries in the first dimension of the array
'i.e. the number of rows
' so the loop is 'For Row = 1 to whatever number of rows there are
For row As Integer = 1 To valArray.GetUpperBound(0)
  
  'the first thing to do is check and see if there's any info in this row
  'so valArray(row,1) will be looking at the first column of this row
  'if there's nothing there, then we can bail out of the loop
  If (valArray(row, 1) = Nothing) Then Exit For
  
  'now, do the same for each column in this row
  'valArray.GetUpperBound(1) will give you the number of columns in the the array
  'so the loop is working through the columns from left to right
  For columnIndex As Integer = 1 To valArray.GetUpperBound(1)
    
	'in order to write to the correct cell in Excel, we'll need to feed Excel the correct 
	'alphanumeric string to identify the column
	'this just creates a string variable that looks at whichever column we're working on, 
	'and translates it to an alpha string
	Dim column As String = Chr(Asc("A"c) + columnIndex - 1)
    
	'This would be uncommented to use debugging trace calls
	'while you're making sure you have the above translation working
	'i.e. you're actually telling Excel to write to the right column
	'Trace.WriteLine("column = " & column)

	'This is the actual workhorse statement.
	'Tell Excel to put the value in valArray(row,columnIndex) into the right cell in the spreadsheet
    GoExcel.CellValue(outputFileName, "Sheet1", column & row) = valArray(row, columnIndex)
  
  'now, go ahead and move to the next column
  Next
  
'and when you're done working all through the columns in one row, then work on the next row  
Next

'tidy up so you don't leave Excel running. 
GoExcel.Save()
GoExcel.Close()

 

 


Todd
Product Design Collection (Inventor Pro, 3DSMax, HSMWorks)
Fusion 360 / Fusion Team

Can't find what you're looking for? Ask the community or share your knowledge.

Post to forums  

Autodesk Design & Make Report