iLogic Rule to export to Excel File Template

[ABoaro]

I am in need of some help with an iLogic rule that will allow me to write / export the BOM to a template excel file. 

To clarify, when exporting the BOM, it creates a default .xls sheet with the BOM information. I would like to have the export process either be to a default template excel file or a way to take the excel file information and write it to a template file.

The default template file for excel is crucial as it contains macros and VBA automation for some third level processes. 

Cheers,

Sandro

[JoãoASilva]

Hello @ABoaro !

I have almost the same procedure, but I export the Parts List from the drawing, not the BOM from the assembly.

Could this be a temporary solution?

'get the path and name of the drawing file
path_and_name = ThisDoc.PathAndFileName(False) ' without extension

'get PDF target folder path
ooFolder = ThisDoc.PathAndFileName

'Check for the PDF folder and create it if it does not exist
If Not System.IO.Directory.Exists(ooFolder) Then
    System.IO.Directory.CreateDirectory(ooFolder)
End If

'define oDoc
oDoc = ThisDoc.Document

'specify the drawing sheet
'oSheet = oDoc.Sheets("Sheet:1") ' sheet by name
oSheet = oDoc.Sheets(1) ' first sheet

 ' say there is a Partslist on the sheet.
oPartslist = oSheet.PartsLists(1)
     
' create a new NameValueMap object
oOptions = ThisApplication.TransientObjects.CreateNameValueMap

'specify an existing template file
'to use For formatting colors, fonts, etc
oOptions.Value("Template") = "C:\....\TemplatePartsList.xlsm" '<---- CHANGE THIS
 
'specify the columns to export         
oOptions.Value("ExportedColumns") = "QTY;DESCRIPTION;VENDOR;PART NUMBER;PART NUMBER;PART NUMBER" '<---- CHANGE THIS
 
'specify the start cell
oOptions.Value("StartingCell") = "A1" '<---- CHANGE THIS
 
'specify the XLS tab name
'here the file name is used
oOptions.Value("PARTS LIST") = ThisDoc.FileName(False) 'without extension

'choose to include the parts list title row
oOptions.Value("IncludeTitle") = False         

'choose to autofit the column width in the xls file
oOptions.Value("AutoFitColumnWidth") = False

If Dir(ooFolder & ".xlsm") <> "" Then
Kill (ooFolder & ".xlsm")
End If

'get drawing name
oName = ThisDoc.FileName

' export the Partslist to Excel with options
oPartslist.Export(ooFolder & "\" & oName & ".xlsm",PartsListFileFormatEnum.kMicrosoftExcel, oOptions)

[A.Acheson]

@ABoaro 

I use a BOM export ilogic code from the model to excel macro template. Macro template  does the removal  of units from qty, filtering of data using category to separate sheets e.g welding, assembly and acing to .xlsx file.  An XML template is used to keep any columns in a static order to suit macro template. Macro template is activated by populating a cell through ilogic. I couldn’t figure out how to trigger the template Macro from ilogic Perhaps there is a better way?. 

Is that something that could work for you?

I can share these file if you would like. 

[ABoaro]

That would be great! I agree with you at least on the challenge of activating a macro through iLogic.

The need would be hopefully not to have to loop through a row / column through iLogic to fill the excel sheet but I'd love to see your take on it. 

Thanks!

[A.Acheson]

@ABoaro 

Message 5 in the post below. Is the method I spoke about, if you need any help let me know. Everything should be there in the zip folder for a test run, you just need to put the contents into the file path described.

Above that is a method for partial bom export but because it is a line for line write to excel linking can be slow.

https://forums.autodesk.com/t5/inventor-customization/is-there-a-way-to-make-this-run-faster/td-p/99...

Partial Export: 

message 13 in post below. Exports only a portion of the BOM, A bit harder to understand and the excel processing is mixed in with the API code so a different level of skill required.

https://forums.autodesk.com/t5/inventor-customization/possible-to-export-a-particular-bom-format-for...