Export an Assembly using ilogic to designated excel template tabs

[mtoddHBKNF]

Hi There,

Once again I need some help. So I been trying to export a BOM from an assembly. I found quiet a few very helpful codes, but I need some extra tweaks.  

I am attaching a file that explains the final look if possible, of course. (item 3 is a plus, but I can work around it) 

Requirements:

1) Thumbnails to be shown for all parts.

2) Export both the Parts only and the Structured all levels to different tabs in the same Excel sheet. (tabs name will be named based on the type of BOM

3) Use a preformatted excel template. (Now this will be ideal, but I can use Macros in excel to do the filtering and format as a table if not possible)

The code below exports the Parts only to the specified Excel sheet tab, but I haven't been able to send the Structured Bom to a different tab

4) Include Assembly part number and date time  in exported excel file

'BOM Publisher
oDoc = ThisDoc.ModelDocument
If oDoc.DocumentType = kPartDocumentObject Then
MessageBox.Show("You need to be in an Assembly to Export a BOM", "Databar: iLogic - BOM Publisher")
Return
End If
oDoc = ThisApplication.ActiveDocument
Dim oBOM As BOM
oBOM = oDoc.ComponentDefinition.BOM
'Options.Value("Author") = iProperties.Value("Summary", "Author")
'==========================================================================================
'You can change the output path by editing oPATH below
oPATH = ("c:\temp\") 'If you change this, remember to keep a \ at the end
'==========================================================================================
'STRUCTURED BoM ===========================================================================
' the structured view to 'all levels'
'oBOM.StructuredViewFirstLevelOnly = False

'
'==========================================================================================
'PARTS ONLY BoM ===========================================================================
' Make sure that the parts only view Is enabled.
oBOM.PartsOnlyViewEnabled = True
Dim oPartsOnlyBOMView As BOMView
oPartsOnlyBOMView = oBOM.BOMViews.Item("Parts Only")
' Export the BOM view to an Excel file
'oPartsOnlyBOMView.Export (oPATH + "BOM3.xls", kMicrosoftExcelFormat)
oPartsOnlyBOMView.Export (oPATH + ThisDoc.FileName(False) + " BOM" + ".xls", kMicrosoftExcelFormat, "PARTS ONLY")
'==========================================================================================
i = MessageBox.Show("Preview the BOM?", "Databar: iLogic - BOM Publisher",MessageBoxButtons.YesNo)
If i = vbYes Then : launchviewer = 1 : Else : launchviewer = 0 : End If
If launchviewer = 1 Then ThisDoc.Launch(oPATH + ThisDoc.FileName(False) + " BOM" + ".xls")

[daltonNYAW9]

The easiest way to do this would be to create a 2nd excel book with the correct format & sheets and copy/paste the exported tables to that one.

I had to "place in cell" the images before copy/pasting the range. Try adding this to the end of your code (could use some cleaning up):

AddReference "Microsoft.Office.Interop.Excel.dll"
Imports Microsoft.Office.Interop
Imports Microsoft.Office.Interop.Excel

oPartsOnlyBOMView.Export(oPATH + ThisDoc.FileName(False) + " BOM" + ".xlsx", kMicrosoftExcelFormat, "PARTS ONLY")
oBOM.BOMViews.Item("Structured").Export(oPATH + ThisDoc.FileName(False) + " BOM Structured" + ".xlsx", kMicrosoftExcelFormat, "PARTS ONLY")
'==========================================================================================
'i = MessageBox.Show("Preview the BOM?", "Databar: iLogic - BOM Publisher",MessageBoxButtons.YesNo)
'If i = vbYes Then : launchviewer = 1 : Else : launchviewer = 0 : End If
'If launchviewer = 1 Then ThisDoc.Launch(oPATH + ThisDoc.FileName(False) + " BOM" + ".xls")

'excel
Dim excelApp As Excel.Application = Nothing
Dim excelWorkbook As Excel.Workbook = Nothing
Dim excelWorkbook2 As Excel.Workbook = Nothing
Dim excelWorksheet As Excel.Worksheet = Nothing
Dim excelWorksheet2 As Excel.Worksheet = Nothing

excelApp = New Excel.Application()
excelApp.Visible = False
excelWorkbook = excelApp.Workbooks.Add("C:\Temp\template.xlsx")

'parts only
excelWorkbook2 = excelApp.Workbooks.Open(oPATH + ThisDoc.FileName(False) + " BOM" + ".xlsx")
excelWorksheet2 = excelWorkbook2.Worksheets(1)
For Each oShape As Excel.Shape In excelWorksheet2.Shapes
	oShape.Select
	oShape.PlacePictureInCell
Next
excelWorksheet2.UsedRange.Copy

excelWorkbook.Worksheets("PARTS ONLY").Range("A3").PasteSpecial
excelApp.CutCopyMode = False
excelWorkbook2.Close(False)
'structured
excelWorkbook2 = excelApp.Workbooks.Open(oPATH + ThisDoc.FileName(False) + " BOM Structured" + ".xlsx")
excelWorksheet2 = excelWorkbook2.Worksheets(1)
For Each oShape As Excel.Shape In excelWorksheet2.Shapes
	oShape.Select
	oShape.PlacePictureInCell
Next
excelWorksheet2.UsedRange.Copy

excelWorkbook.Worksheets("Structured").Range("A3").PasteSpecial
excelApp.CutCopyMode = False
excelWorkbook2.Close(False)

excelApp.Visible = True
excelWorkbook.SaveAs(oPATH & oDoc.PropertySets(3)("Part Number").Value & " " & Strings.Replace(System.DateTime.Now.ToString("d"), "/", "-") & ".xlsx")