Hello,
I want to export the filename to an excel sheet.
Unfortunately the propertysets doesn't contain the filename.
We now use the partnumber property but we see it can be overwritten and with copy designs/ mirrors the partnumber sometimes doesn't get updated.
Now i use the set function to set a property.
For example:
Set oPartNumProperty = oCompDef.Document.PropertySets _
.Item("Design Tracking Properties").Item("Part Number")
Now i want to do this with filename of the document.
I tried to do it like this but it doesn't work.
Set oPartNumProperty = oCompDef.Document.FullFileName
Is it possible to set this property with the filename?
I'm assuming that the variable you are using in those examples is defined as (supposed to be representing) an iProperty (Inventor.Property) object itself, not just the Value of an iProperty, so the line in your first example would be OK, but the second example is trying to assign a Value (String) to that variable, which won't work. It would have to be formatted more like this:
oPartNumProperty.Value = oCompDef.Document.FullFileName
(...after you have already defined the oPartNumProperty variable, and have already set it to represent that specific property object.)
If this solved your problem, or answered your question, please click ACCEPT SOLUTION.
Or, if this helped you, please click (LIKE or KUDOS) 👍.
If you want and have time, I would appreciate your Vote(s) for My IDEAS :light_bulb: or you can Explore My CONTRIBUTIONS
Wesley Crihfield
(Not an Autodesk Employee)
This doesn't do the trick unfortunately.
This is the complete code.
Public Sub BOM_Export()
' Set a reference to the assembly document.
' This assumes an assembly document is active.
Dim oDoc As AssemblyDocument
Set oDoc = ThisApplication.ActiveDocument
Dim oTemplate As String: oTemplate = "I:\Engineering\Formulieren en lijsten\Spare & wear parts lijst\MAXXXXXX-MAA.0000XXXXX-ORDERNO_YYYYMMDD.xltx"
' Set a reference to the BOM
Dim oBOM As BOM
Set oBOM = oDoc.ComponentDefinition.BOM
' Set whether first level only or all levels.
oBOM.StructuredViewFirstLevelOnly = False
' Make sure that the structured view is enabled.
oBOM.StructuredViewEnabled = True
'Set a reference to the "Structured" BOMView
Dim oBOMView As BOMView
Set oBOMView = ThisApplication.ActiveDocument.ComponentDefinition.BOM.BOMViews.Item("Structured")
' Dim oBOMView As BOMView
Set oBOMView = oBOM.BOMViews.Item("Structured")
Call oBOMView.Sort("Item", True)
Dim oPartNumProperty As String
oPartNumProperty = oDoc.ComponentDefinition.Document.PropertySets( _
"Design Tracking Properties")("Part Number").Value
Dim oPartRevNum As String
oPartRevNum = oDoc.ComponentDefinition.Document.PropertySets( _
"Inventor Summary Information")("Revision Number").Value
Dim oPartTitle As String
oPartTitle = oDoc.ComponentDefinition.Document.PropertySets( _
"Inventor Summary Information")("Title").Value
' set excel app and add worksheet
Dim xlApp As Object
Dim xlwb As Object
Dim xlws As Object
Set xlApp = CreateObject("Excel.Application")
Set xlwb = xlApp.Workbooks.Open(oTemplate)
'Set xlwb = xlApp.workbooks.Add
Set xlws = xlwb.Worksheets(1)
xlApp.Visible = True
' write more stuff
' xlws.Cells(1, 2) = oPartTitle
' xlws.Cells(1, 3) = oPartNumProperty
' xlws.Cells(1, 4) = "Rev: " & oPartRevNum
xlws.Name = "Export list " & oPartNumProperty
'Initialize the tab for ItemNumber
Dim ItemTab As Long
ItemTab = -1
Dim oStartRow As Integer: oStartRow = 2
Call QueryBOMRowProperties(oBOMView.BOMRows, ItemTab, xlApp, xlwb, xlws, oStartRow)
End Sub
Private Sub QueryBOMRowProperties(oBOMRows As BOMRowsEnumerator, ItemTab As Long, ByVal xlApp As Object, ByVal xlwb As Object, ByVal xlws As Object, oStartRow As Integer)
ItemTab = ItemTab + 1
' Iterate through the contents of the BOM Rows.
Dim i As Long
For i = 1 To oBOMRows.Count
' Get the current row.
Dim oRow As BOMRow
Set oRow = oBOMRows.Item(i)
'Set a reference to the primary ComponentDefinition of the row
Dim oCompDef As ComponentDefinition
Set oCompDef = oRow.ComponentDefinitions.Item(1)
Dim oPartNumProperty As Property
Dim oPartTitle As Property
Dim oPartDescription As Property
Dim oPartMaterial As Property
Dim oPartVendor As Property
Dim oPartStatus As Property
Dim oPartFile As Property
Dim oPartArticleNo As Property
Dim oPartSurface As Property
Dim oPartSurfaceValue As Property
Dim oPartSpare As Property
Dim oPartWeld As Property
If TypeOf oCompDef Is VirtualComponentDefinition Then
Set oPartNumProperty = oCompDef.Document.PropertySets _
.Item("Design Tracking Properties").Item("Part Number")
Set oPartTitle = oCompDef.Document.PropertySets _
.Item("Inventor Summary Information").Item("Title")
Set oPartDescription = oCompDef.Document.PropertySets _
.Item("Design Tracking Properties").Item("Description")
Set oPartMaterial = oCompDef.Document.PropertySets _
.Item("Design Tracking Properties").Item("Material")
Set oPartVendor = oCompDef.Document.PropertySets _
.Item("Design Tracking Properties").Item("Vendor")
Set oPartStatus = oCompDef.Document.PropertySets _
.Item("Design Tracking Properties").Item("User Status")
'Set oPartFile = oCompDef.Document.Property.Name(FileName)
On Error Resume Next
Set oPartArticleNo = Nothing
Set oPartArticleNo = oCompDef.Document.PropertySets _
.Item("Inventor User Defined Properties").Item("Article No")
Set oPartSurface = Nothing
Set oPartSurface = oCompDef.Document.PropertySets _
.Item("Inventor User Defined Properties").Item("Surface Treatment")
Set oPartSurfaceValue = Nothing
Set oPartSurfaceValue = oCompDef.Document.PropertySets _
.Item("Inventor User Defined Properties").Item("Surface Treatment Value")
Set oPartSpare = Nothing
Set oPartSpare = oCompDef.Document.PropertySets _
.Item("Inventor User Defined Properties").Item("SpareWear")
Set oPartWeld = Nothing
Set oPartWeld = oCompDef.Document.PropertySets _
.Item("Inventor User Defined Properties").Item("Weld Assembly")
xlws.Cells(oStartRow + b, 1) = oRow.ItemNumber
xlws.Cells(oStartRow + b, 2) = oPartNumProperty.Value
xlws.Cells(oStartRow + b, 3) = oRow.ItemQuantity
xlws.Cells(oStartRow + b, 4) = oPartArticleNo.Value
xlws.Cells(oStartRow + b, 5) = oPartTitle.Value
xlws.Cells(oStartRow + b, 6) = oPartDescription.Value
xlws.Cells(oStartRow + b, 7) = oPartMaterial.Value
xlws.Cells(oStartRow + b, 😎 = oPartSurface.Value
xlws.Cells(oStartRow + b, 9) = oPartSurfaceValue.Value
xlws.Cells(oStartRow + b, 10) = oPartVendor.Value
xlws.Cells(oStartRow + b, 11) = oPartSpare.Value
xlws.Cells(oStartRow + b, 12) = oPartStatus.Value
xlws.Cells(oStartRow + b, 13) = oPartWeld.Value
xlws.Cells(oStartRow + b, 14) = oPartFile.Value
oStartRow = oStartRow + 1
Else
'Get the file property that contains the "Part Number"
'The file property is obtained from the parent
'document of the associated ComponentDefinition.
' write more stuff
Set oPartNumProperty = oCompDef.Document.PropertySets _
.Item("Design Tracking Properties").Item("Part Number")
Set oPartTitle = oCompDef.Document.PropertySets _
.Item("Inventor Summary Information").Item("Title")
Set oPartDescription = oCompDef.Document.PropertySets _
.Item("Design Tracking Properties").Item("Description")
Set oPartMaterial = oCompDef.Document.PropertySets _
.Item("Design Tracking Properties").Item("Material")
Set oPartVendor = oCompDef.Document.PropertySets _
.Item("Design Tracking Properties").Item("Vendor")
Set oPartStatus = oCompDef.Document.PropertySets _
.Item("Design Tracking Properties").Item("User Status")
'Set oPartFile = oCompDef.Document.Property.Name(FileName)
On Error Resume Next
Set oPartArticleNo = Nothing
Set oPartArticleNo = oCompDef.Document.PropertySets _
.Item("Inventor User Defined Properties").Item("Article No")
Set oPartSurface = Nothing
Set oPartSurface = oCompDef.Document.PropertySets _
.Item("Inventor User Defined Properties").Item("Surface Treatment")
Set oPartSurfaceValue = Nothing
Set oPartSurfaceValue = oCompDef.Document.PropertySets _
.Item("Inventor User Defined Properties").Item("Surface Treatment Value")
Set oPartSpare = Nothing
Set oPartSpare = oCompDef.Document.PropertySets _
.Item("Inventor User Defined Properties").Item("SpareWear")
Set oPartWeld = Nothing
Set oPartWeld = oCompDef.Document.PropertySets _
.Item("Inventor User Defined Properties").Item("Weld Assembly")
xlws.Cells(oStartRow + b, 1) = oRow.ItemNumber
xlws.Cells(oStartRow + b, 2) = oPartNumProperty.Value
xlws.Cells(oStartRow + b, 3) = oRow.ItemQuantity
xlws.Cells(oStartRow + b, 4) = oPartArticleNo.Value
xlws.Cells(oStartRow + b, 5) = oPartTitle.Value
xlws.Cells(oStartRow + b, 6) = oPartDescription.Value
xlws.Cells(oStartRow + b, 7) = oPartMaterial.Value
xlws.Cells(oStartRow + b, 😎 = oPartSurface.Value
xlws.Cells(oStartRow + b, 9) = oPartSurfaceValue.Value
xlws.Cells(oStartRow + b, 10) = oPartVendor.Value
xlws.Cells(oStartRow + b, 11) = oPartSpare.Value
xlws.Cells(oStartRow + b, 12) = oPartStatus.Value
xlws.Cells(oStartRow + b, 13) = oPartWeld.Value
xlws.Cells(oStartRow + b, 14) = oPartFile.Value
oStartRow = oStartRow + 1
Debug.Print Tab(ItemTab); oRow.ItemNumber; Tab(17); oRow.ItemQuantity; Tab(30); _
oPartNumProperty.Value; Tab(70); oDescripProperty.Value
'Recursively iterate child rows if present.
If Not oRow.ChildRows Is Nothing Then
Call QueryBOMRowProperties(oRow.ChildRows, ItemTab, xlApp, xlwb, xlws, oStartRow)
End If
End If
Next
ItemTab = ItemTab - 1
End Sub
The macro wil export our BOM list to a excel sheet.
In this macro we export te Partnumber.
The following rule wil do this trick.
Set oPartNumProperty = oCompDef.Document.PropertySets _
.Item("Design Tracking Properties").Item("Part Number")
What would i have to do to export de filename of each BOM list rule instead of the part Number.
Any suggestions?
This is confusing because the same variable is being used for two different types of objects in the same macro. In the BOM_Export routine it is defined as a String object, and correctly doesn't use the term 'Set' when setting its value, but in the QueryBOMRowProperties routine it is defined as a Property and correctly uses the 'Set' term to set its value. In your original post I had no idea what Type you had defined that variable as. The Object/Data Type of the Value you assign to a variable must match the Type of the variable. So if you want to set a document's FullFileName to a variable, that variable must have either been pre-defined as a String, or not been pre-defined yet at all. I personally wouldn't use the a variable named "oPartNumProperty" to store the FullFileName, because it doesn't make sense to me, but it's not my code, so as long as it all makes sense to you, that's all that matters. Maybe just create an additional String type variable, just for storing the FullFileName, then have one or more alternate lines of code that are commented out, so you can switch back and forth between using Part Number and FullFileName in your process.
Something like this:
oFullFileName = oCompDef.Document.FullFileName
where the variable "oFullFileName" either has not been created/defined yet, or has been created/defined as a String.
I did notice though that one of your lines of code that is writing data to Excel (same in both Subs) appears to be missing some data.
This line:
xlws.Cells(oStartRow + b, = oPartSurface.Value
seems like it should be like this:
xlws.Cells(oStartRow + b, = oPartSurface.Value
I guess it won't even let me fix this or type it correctly, because it keeps converting the text into an emoji of a face with sunglasses in place of the digit 8 followed by the ) symbol.
Wesley Crihfield
(Not an Autodesk Employee)
Thanks that does the trick!
I only have one problem now, i thought i needed the full file name.
I didn't realized this includes the complete path.
When i tried FileName instead of FullFileName i get a "Object doesn't support this property or method"
I can't find the right name to get only the file name.
Is it possible to get only the FileName?
The 8 ) that was missing was deleted when i copied it. In the macro it was still there.
The complet code looks now like this:
Public Sub BOM_Export()
' Set a reference to the assembly document.
' This assumes an assembly document is active.
Dim oDoc As AssemblyDocument
Set oDoc = ThisApplication.ActiveDocument
Dim oTemplate As String: oTemplate = "I:\Engineering\Formulieren en lijsten\Spare & wear parts lijst\MAXXXXXX-MAA.0000XXXXX-ORDERNO_YYYYMMDD.xltm"
' Set a reference to the BOM
Dim oBOM As BOM
Set oBOM = oDoc.ComponentDefinition.BOM
' Set whether first level only or all levels.
oBOM.StructuredViewFirstLevelOnly = False
' Make sure that the structured view is enabled.
oBOM.StructuredViewEnabled = True
'Set a reference to the "Structured" BOMView
Dim oBOMView As BOMView
Set oBOMView = ThisApplication.ActiveDocument.ComponentDefinition.BOM.BOMViews.Item("Structured")
' Dim oBOMView As BOMView
Set oBOMView = oBOM.BOMViews.Item("Structured")
Call oBOMView.Sort("Item", True)
Dim oPartNumProperty As String
oPartNumProperty = oDoc.ComponentDefinition.Document.PropertySets( _
"Design Tracking Properties")("Part Number").Value
Dim oPartRevNum As String
oPartRevNum = oDoc.ComponentDefinition.Document.PropertySets( _
"Inventor Summary Information")("Revision Number").Value
Dim oPartTitle As String
oPartTitle = oDoc.ComponentDefinition.Document.PropertySets( _
"Inventor Summary Information")("Title").Value
' set excel app and add worksheet
Dim xlApp As Object
Dim xlwb As Object
Dim xlws As Object
Set xlApp = CreateObject("Excel.Application")
Set xlwb = xlApp.Workbooks.Open(oTemplate)
'Set xlwb = xlApp.workbooks.Add
Set xlws = xlwb.Worksheets(1)
xlApp.Visible = True
' write more stuff
' xlws.Cells(1, 2) = oPartTitle
' xlws.Cells(1, 3) = oPartNumProperty
' xlws.Cells(1, 4) = "Rev: " & oPartRevNum
xlws.Name = "Export list " & oPartNumProperty
'Initialize the tab for ItemNumber
Dim ItemTab As Long
ItemTab = 2
Dim oStartRow As Integer: oStartRow = 2
Call QueryBOMRowProperties(oBOMView.BOMRows, ItemTab, xlApp, xlwb, xlws, oStartRow)
End Sub
Private Sub QueryBOMRowProperties(oBOMRows As BOMRowsEnumerator, ItemTab As Long, ByVal xlApp As Object, ByVal xlwb As Object, ByVal xlws As Object, oStartRow As Integer)
ItemTab = ItemTab + 1
' Iterate through the contents of the BOM Rows.
Dim i As Long
For i = 1 To oBOMRows.Count
' Get the current row.
Dim oRow As BOMRow
Set oRow = oBOMRows.Item(i)
'Set a reference to the primary ComponentDefinition of the row
Dim oCompDef As ComponentDefinition
Set oCompDef = oRow.ComponentDefinitions.Item(1)
Dim oFileNameProperty As String
Dim oPartTitle As Property
Dim oPartDescription As Property
Dim oPartMaterial As Property
Dim oPartVendor As Property
Dim oPartStatus As Property
Dim oPartFile As Property
Dim oPartArticleNo As Property
Dim oPartSurface As Property
Dim oPartSurfaceValue As Property
Dim oPartSpare As Property
Dim oPartWeld As Property
If TypeOf oCompDef Is VirtualComponentDefinition Then
oFileNameProperty = oCompDef.Document.FullFileName
Set oPartTitle = oCompDef.Document.PropertySets _
.Item("Inventor Summary Information").Item("Title")
Set oPartDescription = oCompDef.Document.PropertySets _
.Item("Design Tracking Properties").Item("Description")
Set oPartMaterial = oCompDef.Document.PropertySets _
.Item("Design Tracking Properties").Item("Material")
Set oPartVendor = oCompDef.Document.PropertySets _
.Item("Design Tracking Properties").Item("Vendor")
Set oPartStatus = oCompDef.Document.PropertySets _
.Item("Design Tracking Properties").Item("User Status")
On Error Resume Next
Set oPartArticleNo = Nothing
Set oPartArticleNo = oCompDef.Document.PropertySets _
.Item("Inventor User Defined Properties").Item("Article No")
Set oPartSurface = Nothing
Set oPartSurface = oCompDef.Document.PropertySets _
.Item("Inventor User Defined Properties").Item("Surface Treatment")
Set oPartSurfaceValue = Nothing
Set oPartSurfaceValue = oCompDef.Document.PropertySets _
.Item("Inventor User Defined Properties").Item("Surface Treatment Value")
Set oPartSpare = Nothing
Set oPartSpare = oCompDef.Document.PropertySets _
.Item("Inventor User Defined Properties").Item("SpareWear")
Set oPartWeld = Nothing
Set oPartWeld = oCompDef.Document.PropertySets _
.Item("Inventor User Defined Properties").Item("Weld Assembly")
xlws.Cells(oStartRow + b, 1) = oRow.ItemNumber
xlws.Cells(oStartRow + b, 2) = oFileNameProperty
xlws.Cells(oStartRow + b, 3) = oRow.ItemQuantity
xlws.Cells(oStartRow + b, 4) = oPartArticleNo.Value
xlws.Cells(oStartRow + b, 5) = oPartTitle.Value
xlws.Cells(oStartRow + b, 6) = oPartDescription.Value
xlws.Cells(oStartRow + b, 7) = oPartMaterial.Value
xlws.Cells(oStartRow + b, 😎 = oPartSurface.Value
xlws.Cells(oStartRow + b, 9) = oPartSurfaceValue.Value
xlws.Cells(oStartRow + b, 10) = oPartVendor.Value
xlws.Cells(oStartRow + b, 11) = oPartSpare.Value
xlws.Cells(oStartRow + b, 12) = oPartStatus.Value
xlws.Cells(oStartRow + b, 13) = oPartWeld.Value
oStartRow = oStartRow + 1
Else
'Get the file property that contains the "Part Number"
'The file property is obtained from the parent
'document of the associated ComponentDefinition.
' write more stuff
oFileNameProperty = oCompDef.Document.FullFileName
Set oPartTitle = oCompDef.Document.PropertySets _
.Item("Inventor Summary Information").Item("Title")
Set oPartDescription = oCompDef.Document.PropertySets _
.Item("Design Tracking Properties").Item("Description")
Set oPartMaterial = oCompDef.Document.PropertySets _
.Item("Design Tracking Properties").Item("Material")
Set oPartVendor = oCompDef.Document.PropertySets _
.Item("Design Tracking Properties").Item("Vendor")
Set oPartStatus = oCompDef.Document.PropertySets _
.Item("Design Tracking Properties").Item("User Status")
On Error Resume Next
Set oPartArticleNo = Nothing
Set oPartArticleNo = oCompDef.Document.PropertySets _
.Item("Inventor User Defined Properties").Item("Article No")
Set oPartSurface = Nothing
Set oPartSurface = oCompDef.Document.PropertySets _
.Item("Inventor User Defined Properties").Item("Surface Treatment")
Set oPartSurfaceValue = Nothing
Set oPartSurfaceValue = oCompDef.Document.PropertySets _
.Item("Inventor User Defined Properties").Item("Surface Treatment Value")
Set oPartSpare = Nothing
Set oPartSpare = oCompDef.Document.PropertySets _
.Item("Inventor User Defined Properties").Item("SpareWear")
Set oPartWeld = Nothing
Set oPartWeld = oCompDef.Document.PropertySets _
.Item("Inventor User Defined Properties").Item("Weld Assembly")
xlws.Cells(oStartRow + b, 1) = oRow.ItemNumber
xlws.Cells(oStartRow + b, 2) = oFileNameProperty
xlws.Cells(oStartRow + b, 3) = oRow.ItemQuantity
xlws.Cells(oStartRow + b, 4) = oPartArticleNo.Value
xlws.Cells(oStartRow + b, 5) = oPartTitle.Value
xlws.Cells(oStartRow + b, 6) = oPartDescription.Value
xlws.Cells(oStartRow + b, 7) = oPartMaterial.Value
xlws.Cells(oStartRow + b, 😎 = oPartSurface.Value
xlws.Cells(oStartRow + b, 9) = oPartSurfaceValue.Value
xlws.Cells(oStartRow + b, 10) = oPartVendor.Value
xlws.Cells(oStartRow + b, 11) = oPartSpare.Value
xlws.Cells(oStartRow + b, 12) = oPartStatus.Value
xlws.Cells(oStartRow + b, 13) = oPartWeld.Value
oStartRow = oStartRow + 1
Debug.Print Tab(ItemTab); oRow.ItemNumber; Tab(17); oRow.ItemQuantity; Tab(30); _
oPartNumProperty.Value; Tab(70); oDescripProperty.Value
'Recursively iterate child rows if present.
If Not oRow.ChildRows Is Nothing Then
Call QueryBOMRowProperties(oRow.ChildRows, ItemTab, xlApp, xlwb, xlws, oStartRow)
End If
End If
Next
ItemTab = ItemTab - 1
End Sub
this iLogic rule is an example of getting just the filename (without extension)
Dim doc As AssemblyDocument = ThisDoc.Document Dim oBOM As BOM = doc.ComponentDefinition.BOM oBOM.StructuredViewFirstLevelOnly = False oBOM.StructuredViewEnabled = True Dim oBOMView As BOMView = oBOM.BOMViews.Item("Structured")
oBOMView.Sort("Item", True) For Each oRow As BOMRow In oBOMView.BOMRows Dim oCompDef As ComponentDefinition = oRow.ComponentDefinitions.Item(1)
' options for getting the filename Dim fullFileName As String = oCompDef.Document.FullFileName Dim fileName = IO.Path.GetFileName(fullFileName) Dim FileNameWithoutExtension = IO.Path.GetFileNameWithoutExtension(fullFileName) Logger.Info(fullFileName) Logger.Info(fileName) Logger.Info(FileNameWithoutExtension) Logger.Info("-------------------------------") Next
Jelte de Jong
Did you find this post helpful? Feel free to Like this post.
Did your question get successfully answered? Then click on the ACCEPT SOLUTION button.
Blog: hjalte.nl - github.com
Can't find what you're looking for? Ask the community or share your knowledge.