export two part lists table to excel on two tabs using template

[GosponZ]

I'm looking for ilogic code to export 2 parts list tables into excel and to be exported on two tabs in excel.

Thanks for any help

[chandra.shekar.g]

Hi MisterZS,

Please find a sample code in the following. Which exports BOM to 2 excels of "Structured" BOMviews and "Parts Only" BOMViews. Then copies one excel data to another excel. The code can be modified as per requirement.

    ' Set a reference to the assembly document.
    ' This assumes an assembly document is active.
    Dim oDoc As AssemblyDocument
    oDoc = ThisApplication.ActiveDocument
    
    Dim oBOM As BOM
    oBOM = oDoc.ComponentDefinition.BOM
    
    ' Set the structured view to 'all levels'
    oBOM.StructuredViewFirstLevelOnly = False

    ' Make sure that the structured view is enabled.
    oBOM.StructuredViewEnabled = True

    ' Set a reference to the "Structured" BOMView
    Dim oStructuredBOMView As BOMView
    oStructuredBOMView = oBOM.BOMViews.Item("Structured")
    
    ' Export the BOM view to an Excel file
    oStructuredBOMView.Export("C:\temp\BOM-StructuredAllLevels.xls", kMicrosoftExcelFormat)
	
	' Make sure that the parts only view is enabled.
    oBOM.PartsOnlyViewEnabled = True

    ' Set a reference to the "Parts Only" BOMView
    Dim oPartsOnlyBOMView As BOMView
    oPartsOnlyBOMView = oBOM.BOMViews.Item("Parts Only")

    ' Export the BOM view to an Excel file
    oPartsOnlyBOMView.Export("C:\temp\BOM-PartsOnly.xls", kMicrosoftExcelFormat)
	
	GoExcel.Open("C:\temp\BOM-PartsOnly.xls")
	
	wb = GoExcel.Application.ActiveWorkbook
	
	ws = wb.WorkSheets.Add()
	
	GoExcel.Save()
	
	GoExcel.Close()
	
	GoExcel.CellValues("C:\temp\BOM-PartsOnly.xls", "Sheet2", "A1", "") = GoExcel.CellValues("C:\temp\BOM-StructuredAllLevels.xls", "Sheet1", "A1", "")
	
	GoExcel.Save()
	
	GoExcel.CellValues("C:\temp\BOM-PartsOnly.xls", "Sheet2", "B1", "") = GoExcel.CellValues("C:\temp\BOM-StructuredAllLevels.xls", "Sheet1", "B1", "")
	
	GoExcel.Save()
	
	GoExcel.CellValues("C:\temp\BOM-PartsOnly.xls", "Sheet2", "C1", "") = GoExcel.CellValues("C:\temp\BOM-StructuredAllLevels.xls", "Sheet1", "C1", "")
	
	GoExcel.Save()
	
	GoExcel.CellValues("C:\temp\BOM-PartsOnly.xls", "Sheet2", "D1", "") = GoExcel.CellValues("C:\temp\BOM-StructuredAllLevels.xls", "Sheet1", "D1", "")
	
	GoExcel.Save()
	
	GoExcel.CellValues("C:\temp\BOM-PartsOnly.xls", "Sheet2", "E1", "") = GoExcel.CellValues("C:\temp\BOM-StructuredAllLevels.xls", "Sheet1", "E1", "")
	
	GoExcel.Save()
	
	GoExcel.CellValues("C:\temp\BOM-PartsOnly.xls", "Sheet2", "F1", "") = GoExcel.CellValues("C:\temp\BOM-StructuredAllLevels.xls", "Sheet1", "F1", "")
	
	GoExcel.Save()
	
	GoExcel.CellValues("C:\temp\BOM-PartsOnly.xls", "Sheet2", "G1", "") = GoExcel.CellValues("C:\temp\BOM-StructuredAllLevels.xls", "Sheet1", "G1", "")
	
	GoExcel.Save()
	
	GoExcel.CellValues("C:\temp\BOM-PartsOnly.xls", "Sheet2", "H1", "") = GoExcel.CellValues("C:\temp\BOM-StructuredAllLevels.xls", "Sheet1", "H1", "")
	
	GoExcel.Save()
	
	GoExcel.CellValues("C:\temp\BOM-PartsOnly.xls", "Sheet2", "I1", "") = GoExcel.CellValues("C:\temp\BOM-StructuredAllLevels.xls", "Sheet1", "I1", "")
		
	GoExcel.Save()

Thanks and regards,

[GosponZ]

Thank you for your answer and sample code. I need to be in dwg and to  have 2 custom parts list table. I need export  2 tables from drawing in excel but on 2 tabs.

Thanks again

[chandra.shekar.g]

Hi MisterZS,

Please try the sample code to export PartsList into a excel in 2 tabs for Drawing document.

 	Dim oDOc As DrawingDocument
    	oDOc = ThisApplication.ActiveDocument

	Dim oSheet As Sheet
 	oSheet = oDOc.Sheets.Item(1)

	Dim oPartList As PartsList
 	oPartList = oSheet.PartsLists.Item(1)

	Call oPartList.Export("C:\Temp\1.xls", PartsListFileFormatEnum.kMicrosoftExcel)
	
	Dim oPartList1 As PartsList
 	oPartList1 = oSheet.PartsLists.Item(2)

	Call oPartList1.Export("C:\Temp\1.xls", PartsListFileFormatEnum.kMicrosoftExcel)

Please feel free to contact if there is any doubt.

If solves your question, please "Accept as Solution" / give a kudo.

[GosponZ]

Hi Chandra,

this codes working perfect, but before close this case can you tell me if you can add codes to pull excel template first and then export as it is.

Thank you

[chandra.shekar.g]

Hi MisterZS,

Please find the following sample code with excel template path.

	Dim oDOc As DrawingDocument
    	oDOc = ThisApplication.ActiveDocument

	Dim oSheet As Sheet
 	oSheet = oDOc.Sheets.Item(1)

	Dim oPartList As PartsList
 	oPartList = oSheet.PartsLists.Item(1)

	Dim oOptions As NameValueMap
	oOptions = ThisApplication.TransientObjects.CreateNameValueMap

	oOptions.Value("Template") = "C:\Temp\2.xls"

	Call oPartList.Export("C:\Temp\3.xls", PartsListFileFormatEnum.kMicrosoftExcel, oOptions)

	Dim oPartList1 As PartsList
        oPartList1 = oSheet.PartsLists.Item(2)
 
        Call oPartList1.Export("C:\Temp\3.xls", PartsListFileFormatEnum.kMicrosoftExcel)
	
	

Please feel free to contact if there is any doubt.

If solves your question, please "Accept as Solution" / give a kudo.

[GosponZ]

Hi Chandra,

it looks like it does partial good job. It overwrite template. I need template to be clean and reusable and if possible to save file as current dwg file

Thanks again

[chandra.shekar.g · Accepted solution]

Hi MisterZS,

Please find the sample code to export partlist to excel and save file as current dwg file.

    Dim oDOc As DrawingDocument
    oDOc = ThisApplication.ActiveDocument

	Dim oSheet As Sheet
 	oSheet = oDOc.Sheets.Item(1)

	Dim oPartList As PartsList
 	oPartList = oSheet.PartsLists.Item(1)

	Dim oOptions As NameValueMap
	oOptions = ThisApplication.TransientObjects.CreateNameValueMap

	oOptions.Value("Template") = "C:\Temp\Template.xls"
	
	Dim name As String = oDOc.DisplayName.Replace(".idw", "")

	Call oPartList.Export("C:\Temp\" & name & ".xls", PartsListFileFormatEnum.kMicrosoftExcel, oOptions)

	Dim oPartList1 As PartsList
        oPartList1 = oSheet.PartsLists.Item(2)
 
    Call oPartList1.Export("C:\Temp\" & name & ".xls", PartsListFileFormatEnum.kMicrosoftExcel)
	
	

 Pleas find the attached template file also.

Thanks and regards,

[GosponZ]

Perfect.

Thanks a lot

[Anonymous]

Hi Guys, 

I have a similar problem I need to solve. I'd like to export the BOM (parts only view) to existing Excell file template. I would like to keep the original 

cells format of this template and fill only specified range of cells. I'm using custom iProperities vaule and custom BOM table configuration. I'm able to export it through a simple iLogic rule to the Excell file also. But the problem is how to export the BOM to specified range of cells in the exisitng Excell template. 

Thanks in advance for help 

Tomek

[MechMachineMan]

Tom, you cannot export the BOM easily to a certain range of cells and keep the formatting. 

Exporting BOM (from assembly) overrwrites the excel file and creates a brand new one.

PartsList exporting (from Drawings) allows you to use a template, which is what you need to try, and is what is shown in the solution code.

[Anonymous]

Hi, Thanks for reply, 

Where I can find this solution code, You have mantioned about?

[MechMachineMan]

The code contained within the highlighted green post in this thread, that Chandra had posted.... ?

[Anonymous]

I've found some solution how to solve the problem directly from an assembly. But I don't know how to get number of parts for each position in the BOM. 

Is there some iLogic function whitch allows to do this? 

[MechMachineMan]

What do you mean "number of parts for each position" in the BOM?

Are you talking about having different position reps???

Are you unfamiliar with Parts Only parts list, and that's what you are talking about?

We are not mind readers here...

[GosponZ]

I'm wandering if is somehow possible to insert in excel template file name cell A1 and value that will be read from iprop in cell B1.

Eng in cell A2 and value in B2 etc..and that to be only on first sheet or first tab in excel before code above from Mr. Chandra.

Thank you

[chandra.shekar.g]

@GosponZ,

I am not clear about requirement. Can you please provide some screen shots to explain?

Thanks and regards,

[GosponZ]

I would like to use code from above, but need some upgrade on code.

First sheet in Excel to looks like this picture.

iProperties to fill empty cell.

2 tables would be exported into 2 tabs in excel same as accepted code above.

In first sheet would be what is in this picture.

[MechMachineMan]

Here is a sample that should get you started. Note that I have partially encapsulated it; tweaking the section that Chandra originally wrote so it's more "reusable". I left the rest without so it would be easier for you to add iProps/cells.

Best way to find more about what you can do with the excel commands is to google "msdn + excel + vba + [your question here]".

Sample result: https://msdn.microsoft.com/en-us/vba/excel-vba/articles/xlhalign-enumeration-excel

Can add remove iProps/cells/static data/new rows using the same format/pattern as outlined in the section.

*You may need to tweak sheet name to make the excel portion work properly

Sub Main()
    Dim oDwgDoc As DrawingDocument
    oDwgDoc = ThisDoc.Document
    
    Dim exceloutputname as String
    exceloutputname = "C:\Temp\" & oDOc.DisplayName.Replace(".idw", "") & ".xlsx"

    Call ExportSheetPartsLists(oDwgDoc.Sheets.Item(1), oOutputExcelName)

 Dim xlApp As Object 
 xlApp = CreateObject("Excel.Application") 
 xlApp.Visible = True
 xlwb = xlApp.Workbooks.Open(oOutputExcelName
 xlws = xlwb.Worksheets("Sheet1")
 xlws.Range("A1").EntireRow.Insert
 xlws.Range("A1").EntireRow.Insert

 xlws.Range("B1") = "Product #:"
 xlws.Range("B1").Font.Bold = True
 xlws.Range("B1").HorizontalAlignment = -4152
 xlws.Range("B2") = iProperties.Value("Project", "Part Number")
 xlws.Range("D1") = "Description:"
 xlws.Range("E1") = iProperties.Value("Project", "Description")
 xlws.Range("H1") = "Customer"

 xlwb.Save
 'xlwb.Close
 'xlApp.Quit

 xlws = Nothing
 xlwb = Nothing
 xlApp = Nothing

End Sub

Sub ExportSheetPartsLists(oSheet As Sheet, OutputName as String)
     Dim oPartList As PartsList

     Dim oOptions As NameValueMap 
     oOptions = ThisApplication.TransientObjects.CreateNameValueMap
     oOptions.Value("Template") = "C:\Temp\Template.xls"

     For Each oPartsList in oSheet.PartsLists
        If oFirstPL = True Then
            Call oPartList.Export(OutputName, PartsListFileFormatEnum.kMicrosoftExcel, oOptions)
            oFirstPL = False
        Else
            Call oPartList1.Export(OutputName, PartsListFileFormatEnum.kMicrosoftExcel)
        End if
     Next 
End Sub