Export iProperties to Excel

[Anonymous]

Is it possible to export specific iProperties to excel?

I have a full assembly and would like to go part to part and export the specific I properties to on excel file.

[MechMachineMan]

Something like this would work.

Sub Main()
	Dim oNextOpenArrRow As Integer = 0

	Dim oiPropArr(,) As Object
	ReDim Preserve oiPropArr(0 To 1, 0)
	
	For Each oSubDoc In ThisApplication.ActiveDocument.AllReferencedDocuments
		oiPropArr(0, oNextOpenArrRow) = oSubDoc.FullFileName
		oiPropArr(1, oNextOpenArrRow) = oSubDoc.PropertySets("Design Tracking Properties")("Part Number").Value
		oNextOpenArrRow = oNextOpenArrRow + 1
		ReDim Preserve oiPropArr(0 To 1, 0 To oNextOpenArrRow)
	Next
		
	ReDim Preserve oiPropArr(0 To 1, 0 To UBound(oiPropArr,2) - 1)
	Call ExportArrToExcel(oiPropArr)
End Sub

Sub ExportArrToExcel(oArr As Object)
	Dim xlApp As Object
	Dim xlwb As Object
	Dim xlws As Object
    xlApp = CreateObject("Excel.Application")
    xlwb = xlApp.Workbooks.Add()
    xlws = xlwb.Worksheets(1)
    	
    xlApp.Visible = True    
			   
	'For Each arr row...
	For iRow = LBound(oArr,2) To UBound(oArr,2)
		'For each entry
		For iColumn = LBound(oArr,1) To UBound(oArr,1)
			xlws.Cells(iRow+1, iColumn+1) = oArr(iColumn,iRow)
		Next
	Next

    xlws = Nothing
    xlwb = Nothing
    xlApp = Nothing
End Sub

[Anonymous]

This works great!! The only thing I am having trouble with is that I have never worked with property sets.

I am wanting to grab certain iProperties like Comments, Stock Number, Cut Length (G_L), Item QTY, etc. Is that possible with property sets or should I go a different dirction?

[MechMachineMan]

For PARAMETERS, like "G_L", you need to ensure they are marked for export so they get pushed to a custom iProperty.

For PROPERTIES, use google and learn how on your own.

ex blog you will find from the google machine: 

http://modthemachine.typepad.com/my_weblog/2010/02/accessing-iproperties.html

[Anonymous]

Been mucking around with this trying to get it to run.   I can't define the size of the array in my first "dim" as it complains that it's already been defined when I ReDim, however an empty bracket set should define a flexible array.   There was a comma in MMM's original code, my compiler doesn't like that.

In the sub ExportArrtoExcel, , I don't see why I'm not using the oiPropArr as the argument rather than oArr, which isn't defined.   I get an argument type mismatch in the last line of the main sub (renamed ExportProperties,) which leads me to believe my code hasn't generated an array for export.  Missing the comma!

So far in my journey into customization I've been fed everything I've needed, and I've read a lot of howtos, but I think I'm going back to basics and visiting the library to get more background on coding.

Sub ExportProperties()
    Dim oNextOpenArrRow As Integer
        oNextOpenArrRow = 0
    Dim oiPropArr() As Object
          
    
    ReDim Preserve oiPropArr(0 To 2, 0 To 2)
    
    For Each oSubDoc In ThisApplication.ActiveDocument.AllReferencedDocuments
        oiPropArr(0, oNextOpenArrRow) = oSubDoc.FullFileName
        oiPropArr(1, oNextOpenArrRow) = oSubDoc.PropertySets("Design Tracking Properties")("Part Number").Value
        oNextOpenArrRow = oNextOpenArrRow + 1
        ReDim Preserve oiPropArr(0 To 2, 0 To oNextOpenArrRow)
    Next
        
    ReDim Preserve oiPropArr(0 To 2, 0 To UBound(oiPropArr, 2) - 1)
    Call ExportArrToExcel(oiPropArr)
    
End Sub

Private Sub ShowArray()
'ignore...
'For each  "Part Number" in oiPropArr


End Sub

Sub ExportArrToExcel(oArr As Object)
    Dim xlApp As Object
    Dim xlwb As Object
    Dim xlws As Object
    xlApp = CreateObject("Excel.Application")
    xlwb = xlApp.Workbooks.Add()
    xlws = xlwb.Worksheets(1)
        
    xlApp.Visible = True
               
    'For Each arr row...
    For iRow = LBound(oArr, 2) To UBound(oArr, 2)
        'For each entry
        For iColumn = LBound(oArr, 1) To UBound(oArr, 1)
            xlws.Cells(iRow + 1, iColumn + 1) = oArr(iColumn, iRow)
        Next
    Next

    xlws = Nothing
    xlwb = Nothing
    xlApp = Nothing
End Sub

[MechMachineMan]

Are you trying to use this in VBA?

As the code was originally written, it was intended to be plopped into the iLogic environment. Converting to vba will definitely have some things to change as the 2 languages handle arrays a little different (IIRC).

[Anonymous]

Huh!  Really no way to easily tell - I haven't worked with arrays in VBA, either, otherwise I may have noticed.  Now that I look again all of the excel object stuff looks wrong.

I have this drive to keep things consistent and use VBA for everything, esp. since I want to integrate older stuff into this routine.  I'll figure out how to make these arrays in VBA.  It's not a huge chunk to learn.  Thanks for the help!

[MechMachineMan]

Pretty hard to tell with that chunk of code, but 1 of the hints that reminded me was the

xlws = Nothing

line as in vba it should be 

set xlws = Nothing

There is also the use of declaring objects as Object instead of Variant which is a hint.

As well as the lack of using "Call" everywhere as vba is picky about the bracket syntax when using parameters to methods. (<--- definitely add this one to the reading material)

[b_sharanraj]

In Master Assembly BOM add whatever Custom Properties & iProperties of Parts / Sub Assemblies you want to export into Excel.

Add a New rule in Master Assembly with the below code

ThisBOM.Export("Parts Only", "fileName", kMicrosoftExcelFormat)

Excel file will be generated with all the Customer Properties & iProperties in the Same Path of Master Assembly in "fileName".xls 🙂