Add a drawing property to a BOM

[dypro]

Hello everyone, I need your help, I've been trying to make a custom BOM in inventor, and I have a problem that I don't know how to solve. (First attempt of iLogic)

My goal is to make a BOM that can be exported to an excel (so far so good, here comes the tricky part) so that the administration people have the BOM and inside the BOM spreadsheet there are three cells, in which there are written some custom iproperties OF THE DRAWING, not the models.
Basically, the name of the drawing file without the ".dwg" in a cell, and two custom Iproperties of the drawings titleblock called "FIRST ROW" & "SECOND ROW" in another two cells.

Any ideas on how to accomplish that? Thanks in advance

[Andrii_Humeniuk]

Hello. Watch this video , it should help you solve this problem.

[dypro]

Hello, thanks for trying to help, first of all.
That video is way to complicated for me right now, i just see a bunch of code that says "api simple export" that doesn't seem simple for me, besides the code is nowhere to be found in the video, I saw the bit where he adds info to the spreadsheet that seemed promising, but it's assembly info, not drawing info.

[Michael.Navara]

You can use standard export method and later on add some additional values to the excel sheet

Dim drawing As DrawingDocument = ThisDoc.Document
Dim partsList As PartsList = drawing.ActiveSheet.PartsLists(1)

Dim bomFileName = "C:\Temp\BOM.xlsx"

'Options
'Name				Value Type	Valid For export formats
'-------------------------------------------------------------
'TableName			String		kMicrosoftExcel, kMicrosoftAccess 
'ExportedColumns	String		containing semicolon separated column titles All 
'IncludeTitle		Boolean		kMicrosoftExcel, kTextFileCommaDelimited, kTextFileTabDelimited, kUnicodeTextFileCommaDelimited, kUnicodeTextFileTabDelimited 
'StartingCell		String		kMicrosoftExcel 
'Template			String		kMicrosoftExcel 
'AutoFitColumnWidth	Boolean		kMicrosoftExcel 

Dim options As NameValueMap = ThisApplication.TransientObjects.CreateNameValueMap()
options.Value("TableName") = "BOM"
options.Value("StartingCell") = "A3"

partsList.Export(bomFileName, PartsListFileFormatEnum.kMicrosoftExcel, options)

'Add additional info
Dim fileName = ThisDoc.FileName(False)
GoExcel.Open(bomFileName, "BOM")
GoExcel.CellValue("A1") = "fileName"
GoExcel.CellValue("B1") = fileName
GoExcel.Save
GoExcel.Close

[dypro]

Hello again, thanks for the answer.

I tried using the code, and it did one of the things I meant it to do (name of the file in the excel), but then, (knowing nothing about ILogic) I tried to modify it in order to fullfill my second requirement.

I tried this one and several other things that came to mind, but without success, maybe one of you sees how to make it work?

I have the "PRIMERA LINEA" and the  "SEGUNDA LINEA" as custom iproperties (I wrote two lines at the top of the code to make them show as parameters as well, but if that can be avoided would be even better).

Other than that, it's the 32th line the one that has the problem (or at least it says that in my computer when running the code)

Please help

PRIMERA_LINEA = ThisDoc.FileName(False) 'without extension
iProperties.Value("Custom", "PRIMERA LINEA") = PRIMERA_LINEA


Dim drawing As DrawingDocument = ThisDoc.Document
Dim partsList As PartsList = drawing.ActiveSheet.PartsLists(1)

Dim bomFileName = "C:\Temp\BOM.xlsx"

'Options
'Name				Value Type	Valid For export formats
'-------------------------------------------------------------
'TableName			String		kMicrosoftExcel, kMicrosoftAccess 
'ExportedColumns	String		containing semicolon separated column titles All 
'IncludeTitle		Boolean		kMicrosoftExcel, kTextFileCommaDelimited, kTextFileTabDelimited, kUnicodeTextFileCommaDelimited, kUnicodeTextFileTabDelimited 
'StartingCell		String		kMicrosoftExcel 
'Template			String		kMicrosoftExcel 
'AutoFitColumnWidth	Boolean		kMicrosoftExcel 

Dim options As NameValueMap = ThisApplication.TransientObjects.CreateNameValueMap()
options.Value("TableName") = "BOM"
options.Value("StartingCell") = "A3"

partsList.Export(bomFileName, PartsListFileFormatEnum.kMicrosoftExcel, options)

'Add additional info
Dim fileName = ThisDoc.FileName(False)
GoExcel.Open(bomFileName, "BOM")
GoExcel.CellValue("A1") = "fileName"
GoExcel.CellValue("B1") = fileName

Dim PrimeraLinea = InventorVb.checkParameters ("SEGUNDA_LINEA")
GoExcel.Open(bomFileName, "BOM")
GoExcel.CellValue("C1") = "segunda linea"
GoExcel.CellValue("D1") = PrimeraLinea

GoExcel.Save
GoExcel.Close

[dypro]

By the way, is there a way to make the name of the .xlsx the same one that the one of the drawing?
and that it saves in the save folder as the drawing as well?

[dypro]

Hello everyone, once again

I've made a remix of the video, and the code that you people sent to me, somehow I ended up with this:

iProperties.Value("Custom", "PRIMERA LINEA") = PRIMERA_LINEA
iProperties.Value("Custom", "SEGUNDA LINEA") = SEGUNDA_LINEA

Dim drawing As DrawingDocument = ThisDoc.Document
Dim partsList As PartsList = drawing.ActiveSheet.PartsLists(1)

'CSV filename uses this documents path and filename with a .csv extention
Dim oFile As System.IO.File
Dim oWrite As System.IO.StreamWriter
csv_filename = ThisDoc.PathAndFileName(False) & ".csv"

oWrite = oFile.CreateText(csv_filename) 'name and path of csv file to create/overwrite

'Dim bomFileName = "C:\Temp\BOM.xlsx"

'Options
'Name				Value Type	Valid For export formats
'-------------------------------------------------------------
'TableName			String		kMicrosoftExcel, kMicrosoftAccess 
'ExportedColumns	String		containing semicolon separated column titles All 
'IncludeTitle		Boolean		kMicrosoftExcel, kTextFileCommaDelimited, kTextFileTabDelimited, kUnicodeTextFileCommaDelimited, kUnicodeTextFileTabDelimited 
'StartingCell		String		kMicrosoftExcel 
'Template			String		kMicrosoftExcel 
'AutoFitColumnWidth	Boolean		kMicrosoftExcel 

Dim options As NameValueMap = ThisApplication.TransientObjects.CreateNameValueMap()
'options.Value("TableName") = "BOM"
options.Value("TableName") = csv_filename
options.Value("StartingCell") = "A6"

partsList.Export(bomFileName, PartsListFileFormatEnum.kMicrosoftExcel, options)

'Add additional info
'Dim fileName = ThisDoc.FileName(False)
'GoExcel.Open = fileName
'GoExcel.CellValue("E1") = "fileName"
GoExcel.CellValue("F1") = fileName
'GoExcel.Save
'GoExcel.Close

'Output parameters names and values to a comma delimited CSV file

'fileheader = ThisDoc.PathAndFileName(False) 'first line header of csv file
'description = "Main Parameters and their values" 'use this variable in the body of the html
TableMembers = New String () {"PRIMERA_LINEA", "SEGUNDA_LINEA", "NOMBRE_ARCHIVO"} 'parameters (names) to include in table
'delimiter = "," 'character to separate the names and values on each line

'CSV filename uses this documents path and filename with a .csv extention
'Dim oFile As System.IO.File
'Dim oWrite As System.IO.StreamWriter
'csv_filename = ThisDoc.PathAndFileName(False) & ".csv"

'oWrite = oFile.CreateText(csv_filename) 'name and path of csv file to create/overwrite
'oWrite.WriteLine(fileheader) 'include the fileheader on the first line
'oWrite.WriteLine(description) 'include the description on the second line
'oWrite.WriteLine(NOW()) 'include the current date and time on the third line
'oWrite.WriteLine() 'blank line

'write the parameter name , value for each parameter in the TableMembers Arraylist
For Each pName As String In TableMembers
'oWrite.WriteLine(pName & delimiter & Parameter(pName)) 
oWrite.WriteLine(Parameter(pName))
Next

oWrite.Close()

'option to view report
go = MessageBox.Show("¿Quierer abrir el archivo?", "iLogic CSV Output", MessageBoxButtons.YesNo)
If go = 6 Then ThisDoc.Launch(csv_filename)

It does not work as it is, but I feel like I'm getting close to what I need, and that it is a syntax thing (or maybe i'm wrong and there's no chance of this thing to work)

If anyone can help... thanks

(there is a lot of code in "comentary mode" but it was to make sure not to erase something important and not being able to get it back)

[Michael.Navara]

Do you want to export BOM to native Excel file (.xlsx) or to CSV text file?

[dypro]

Hi, to excel

[dypro]

Someone here at the office said that xlsx was not compatible and to use csv but for me it's better xlsx or xls.

I've done some cleaning and this is what I've got, It does not work, the 16th line says that "the reference to a non shared member requires an object reference" whatever that means.

iProperties.Value("Custom", "PRIMERA LINEA") = PRIMERA_LINEA
iProperties.Value("Custom", "SEGUNDA LINEA") = SEGUNDA_LINEA

Dim drawing As DrawingDocument = ThisDoc.Document

Dim oFile As System.IO.File
Dim oWrite As System.IO.StreamWriter
xls_filename = ThisDoc.PathAndFileName(False) & ".xls"

oWrite = oFile.CreateText(xls_filename) 'name and path of csv file to create/overwrite

Dim options As NameValueMap = ThisApplication.TransientObjects.CreateNameValueMap()
options.Value("AutoFitColumnWidth") = yes
options.Value("StartingCell") = "A6"

PartsList.Export(ThisDoc.FileName(True), kMicrosoftExcelFormat, options)

GoExcel.CellValue("F1") = fileName

TableMembers = New String () {"PRIMERA_LINEA", "SEGUNDA_LINEA", "NOMBRE_ARCHIVO"} 'parameters (names) to include in table

For Each pName As String In TableMembers
oWrite.WriteLine(Parameter(pName))
Next

oWrite.Close()

go = MessageBox.Show("¿Quierer abrir el archivo?", "iLogic CSV Output", MessageBoxButtons.YesNo)
If go = 6 Then ThisDoc.Launch(xls_filename)

---

[A.Acheson]

Hi @dypro 

A few things I have spotted while looking through the code you are missing the partslist object from which to launch the export method. See Michael's initial post here

and also the API partslist object here.

In addition online 16 in the partslist export method you are supplying only a filename with extension which looks like this "Filename.iam"

What you need to supply here is a filepath and excel extension like you have allready created using this String variable name xls_filename.