Sheet Number in Parts List

[cadman777]

Hi All,

Does anybody have an iLogic rule or VBA macro that adds the sheet numbers (separated by a comma) to the PartsList for every part or assembly in a multi-sheet drawing?

I'd be very grateful if someone has something like that they'd be willing to share with me.

[Ralf_Krieg]

Hello

Something like this?  The Partslist column must be named "Sheetnumber" or modify code.

Option Explicit

Private Sub SheetNumber()

    Dim oApp As Inventor.Application
    Set oApp = ThisApplication
    
    Dim oDrawDoc As DrawingDocument
    Set oDrawDoc = oApp.ActiveDocument
    
    Dim oSheet As Sheet
    Set oSheet = oDrawDoc.ActiveSheet
    
    Dim oPartsList As PartsList
    Set oPartsList = oSheet.PartsLists(1)
    
    Dim iColNumber As Integer
    For iColNumber = 1 To oPartsList.PartsListColumns.Count
        If oPartsList.PartsListColumns(iColNumber).Title = "Sheetnumber" Then Exit For
    Next
    
    Dim oPLRow As PartsListRow
    Dim srefedfile As String
    Dim sSheetNumber As String
    For Each oPLRow In oPartsList.PartsListRows
        srefedfile = oPLRow.ReferencedFiles(1).FullFileName
        sSheetNumber = GetSheetnumber(oDrawDoc.Sheets, srefedfile)
        oPLRow.Item(iColNumber).Value = sSheetNumber
    Next

End Sub

Private Function GetSheetnumber(ByVal oSheets As Sheets, ByVal srefedfile As String) As String
    Dim i As Integer
    Dim oSheet As Sheet
    
    For Each oSheet In oSheets
        If oSheet.ExcludeFromCount = False Then
            i = i + 1
            If oSheet.DrawingViews(1).ReferencedDocumentDescriptor.ReferencedFileDescriptor.FullFileName = srefedfile Then
                If GetSheetnumber = "" Then
                    GetSheetnumber = i
                Else
                    GetSheetnumber = GetSheetnumber & ", " & i
                End If
            End If
        End If
    Next
    
End Function

[dalton98]

Ill go ahead and post this since ive been working on it an hour lol. First you have to make a custom column called sheet number. parts list>column chooser>new property> "Sheet Number"

Dim oDoc As DrawingDocument
oDoc = ThisApplication.ActiveDocument

Dim oPartsList As PartsList
oPartsList = oDoc.ActiveSheet.PartsLists.Item(1)

Dim oPartsListColumn As PartsListColumn
oPartsListColumn = oPartsList.PartsListColumns.Add(kFilenamePartsListProperty)

sheetNum = 1
Dim oSheet As Sheet
For Each oSheet In oDoc.Sheets
	Dim oView As DrawingView
	oView = oSheet.DrawingViews.Item(1)
	oDocName = oView.ReferencedDocumentDescriptor.DisplayName
	
	i = 0
	While i < oPartsList.PartsListRows.Count
		i = i + 1
	If oDocName = oPartsList.PartsListRows.Item(i).Item("FILE NAME").Value
		If oPartsList.PartsListRows.Item(i).Item("Sheet Number").Value = ""
		oSheetNumber = oPartsList.PartsListRows.Item(i).Item("Sheet Number").Value
		oSheetNumber = oSheetNumber & sheetNum
		oPartsList.PartsListRows.Item(i).Item("Sheet Number").Value = oSheetNumber
		Else
		oSheetNumber = oPartsList.PartsListRows.Item(i).Item("Sheet Number").Value
		oSheetNumber = oSheetNumber & ", " & sheetNum
		oPartsList.PartsListRows.Item(i).Item("Sheet Number").Value = oSheetNumber
		End If
	End If
	End While

sheetNum = sheetNum + 1
Next

oPartsList.PartsListColumns.Item("FILE NAME").Remove

[cadman777]

Thanx!

Let me give it a try and get back to you.

[cadman777]

Thanx!

Let me give it a try and see which one works best.

[cadman777]

OK, I tried your rule but it didn't work.

This is where it appears to be failing, if you don't mind explaining what it's doing:

If oDocName = oPartsList.PartsListRows.Item(i).Item("FILE NAME").Value

Thanx...

[cadman777]

@Ralf_Krieg 

I ran your macro and it errored out at this line:

oPLRow.Item(iColNumber).Value = sSheetNumber

I can't quite read your code, so can't figure out why it's erroring out.

Would you be able to shed some light on what your code is doing?

Thanx...

[Ralf_Krieg]

Hello

I think there is not a column "Sheet number" in your partslist. I've added additional lines to check and add the column if not exist. The name of the column is defined on top, so you can easily edit if your name differs.

Are you using a localised (not english) version of Inventor? The code of @dalton98 assumes the title of the filename column is "FILE NAME", which is only correct in the english version.

Option Explicit

Private Sub SheetNumber()

    Dim sSheetNumberColumn As String
    sSheetNumberColumn = "Sheet Number" '<---- Name of the partslist column to save the sheet numbers to
    
    Dim oApp As Inventor.Application
    Set oApp = ThisApplication
    
    Dim oDrawDoc As DrawingDocument
    Set oDrawDoc = oApp.ActiveDocument
    
    Dim oSheet As Sheet
    Set oSheet = oDrawDoc.ActiveSheet
    
    Dim oPartsList As PartsList
    Set oPartsList = oSheet.PartsLists(1)
    
    Dim oPLCol As PartsListColumn
    For Each oPLCol In oPartsList.PartsListColumns
        If oPLCol.Title = sSheetNumberColumn Then Exit For
    Next
    
    If oPLCol Is Nothing Then
        Set oPLCol = oPartsList.PartsListColumns.Add(kCustomProperty, , sSheetNumberColumn)
    End If
    
    Dim iColNumber As Integer
    For iColNumber = 1 To oPartsList.PartsListColumns.Count
        If oPartsList.PartsListColumns(iColNumber).Title = sSheetNumberColumn Then Exit For
    Next
    
    Dim oPLRow As PartsListRow
    Dim srefedfile As String
    Dim sSheetNumber As String
    For Each oPLRow In oPartsList.PartsListRows
        srefedfile = oPLRow.ReferencedFiles(1).FullFileName
        sSheetNumber = GetSheetnumber(oDrawDoc.Sheets, srefedfile)
        oPLRow.item(iColNumber).Value = sSheetNumber
    Next

End Sub

Private Function GetSheetnumber(ByVal oSheets As Sheets, ByVal srefedfile As String) As String
    Dim i As Integer
    Dim oSheet As Sheet
    
    For Each oSheet In oSheets
        If oSheet.ExcludeFromCount = False Then
            i = i + 1
            If oSheet.DrawingViews(1).ReferencedDocumentDescriptor.ReferencedFileDescriptor.FullFileName = srefedfile Then
                If GetSheetnumber = "" Then
                    GetSheetnumber = i
                Else
                    GetSheetnumber = GetSheetnumber & ", " & i
                End If
            End If
        End If
    Next
    
End Function

[cadman777]

Thanx for this update.

Something you did made the macro run and populate a couple assemblies on the sheet I ran it.

But it didn't work for most of the assemblies or any of the parts in the drawing.

Also, I was hoping to learn what 'FILE NAME' was used for in @dalton98's rule.

I don't have a column named 'FILE NAME' in any of my PartsLists, which is why I wanted to know.

Un-Annotated rules and macros are very difficult to understand for people like me who are novices at iLogic/VBA.

No matter how much time I spend trying to learn this stuff, once I leave it and go back to the driving forces of daily life, I quickly forget most of what I learned. It's a constant uphill climb trying to re-learn what I already know but can't remember! 😋

[Ralf_Krieg]

Hello

The partslist column "FILE NAME" is a predefined parts list column which always displays the filename of the referenced document of each partslistrow. "FILE NAME" is just the standard title of this column in the english version. If you use a german localised version, the title of the same column is "DATEINAME". If the code searches for the column title "FILE NAME", it will never find it in a localised version.

OK, we can write a comment in every line, but you have to wait 2 days longer to get your answer. The next user who ask for help doesn't need any comments, cause he knows it all and needs only a hint. The third one don't know anything about coding ... and don't want to learn anything about. He just wants to use it. How should we know how much info is requested?

Ok, next try. I've seen that in my code only the first drawingview on every sheet was checked. So a drawingview of a second, third, ... part/assembly was ignored. My fault.

Option Explicit

Private Sub SheetNumber()

    Dim sSheetNumberColumn As String
    sSheetNumberColumn = "Sheet Number" '<------ Name of the parts list column to save the sheet numbers to
    
    Dim oApp As Inventor.Application
    Set oApp = ThisApplication
    
    Dim oDrawDoc As DrawingDocument
    Set oDrawDoc = oApp.ActiveDocument
    
    Dim oSheet As Sheet
    Set oSheet = oDrawDoc.ActiveSheet
    
    Dim oPartsList As PartsList
    Set oPartsList = oSheet.PartsLists(1)
    
    Dim oPLCol As PartsListColumn
    For Each oPLCol In oPartsList.PartsListColumns
        If oPLCol.Title = sSheetNumberColumn Then Exit For
    Next
    
    If oPLCol Is Nothing Then
        Set oPLCol = oPartsList.PartsListColumns.Add(kCustomProperty, , sSheetNumberColumn)
    End If
    
    Dim iColNumber As Integer
    For iColNumber = 1 To oPartsList.PartsListColumns.Count
        If oPartsList.PartsListColumns(iColNumber).Title = sSheetNumberColumn Then Exit For
    Next
    
    Dim oPLRow As PartsListRow
    Dim srefedfile As String
    Dim sSheetNumber As String
    For Each oPLRow In oPartsList.PartsListRows
        srefedfile = oPLRow.ReferencedFiles(1).FullFileName
        sSheetNumber = GetSheetnumber(oDrawDoc.Sheets, srefedfile)
        oPLRow.item(iColNumber).Value = sSheetNumber
    Next

End Sub

Private Function GetSheetnumber(ByVal oSheets As Sheets, ByVal srefedfile As String) As String
    Dim i As Integer
    Dim oSheet As Sheet
    Dim oDrawView As DrawingView
    
    For Each oSheet In oSheets
        If oSheet.ExcludeFromCount = False Then
            i = i + 1
            For Each oDrawView In oSheet.DrawingViews
                If oDrawView.ReferencedDocumentDescriptor.ReferencedFileDescriptor.FullFileName = srefedfile Then
                    If GetSheetnumber = "" Then
                        GetSheetnumber = i
                    Else
                        GetSheetnumber = GetSheetnumber & ", " & i
                    End If
                End If
            Next
        End If
    Next
    
End Function

[cadman777]

Thanx for the update.

1. No worries for not annotating. I understand. A short note here and there would go a long way.

2. Where do I find "FILE NAME" in the VBA ObjectBrowser?

3. I ran the macro and same thing happens...only 2 assemblies get sheet numbers in the "SHEET" column (I changed the PartsList column title to "SHEET").

4. Are you saying that you use the the drawing view to identify the part or assembly on the sheet?

I've seen that in my code only the first drawingview on every sheet was checked. So a drawingview of a second, third, ... part/assembly was ignored. 

Thanx...

[Ralf_Krieg]

Hello

1. I'll try to add more 😇

2. You'll not find "FILE NAME" itself. The following line adds the column. The column title is somewhere hard coded in Inventor itself or the language packs.

Dim oPartsListColumn As PartsListColumn
oPartsListColumn = oPartsList.PartsListColumns.Add(kFilenamePartsListProperty)

 This would do the same as if you were open the parts list edit dialog, open the column chooser dialog and select the file name option on the left side to add to your parts list.

3. Maybe you should post your test drawing with all the referenced files, cause I can not reproduce it.

4. I try to describe the complete function. Maybe there is a missunderstanding.

- define the title of the sheet number column in partslist in "sSheetNumberColumn"

- starting from an open drawing document

- get the first partslist on the active sheet

- traverse all partslist columns and search for a column named like defined "sSheetNumberColumn"

- if column is not found, add it

- traverse all partslist columns again to get the columnindex number of the sheet number column

- traverse all partslist rows

    - get the referenced file (the fullfilename)

    - call function GetSheetNumber

        - traversing all sheets in the drawing document

        - on each sheet traverse all drawingviews

        - compare the referenced document (FullFileName) of the drawingview with the referenced file (FullFilName) of the partslist row

        - if FullFileNames match, add the sheetnumber to the string GetSheetNumber

        - if all drawingviews on all sheets are processed, return the GetSheetNumber String

- add the GetSheetNumber string to the cell in partslist row

- continue with next partslist row

I number all sheets in the order they are listed in browser and exclude all sheets which are set to "Exclude from count". Otherwise the automatic sheet number in titleblock would differ.

[dalton98]

I'm assuming ur using the my code which doesn't use vba?

"FILE NAME" is just a sloppy way to check if the part and the parts list row match and @Ralf_Krieg looks like he found a solution to it if you want to use that. I've updated the code to where it checks each view and ignores repeats. Just change "FILE NAME" to the correct column title.

Dim oDoc As DrawingDocument
oDoc = ThisApplication.ActiveDocument

Dim oPartsList As PartsList
oPartsList = oDoc.ActiveSheet.PartsLists.Item(1)

Dim oPartsListColumn As PartsListColumn
oPartsListColumn = oPartsList.PartsListColumns.Add(kFilenamePartsListProperty)

sheetNum = 1
Dim oSheet As Sheet
For Each oSheet In oDoc.Sheets
	Dim oView As DrawingView
	Dim oDocNamePrevious As String
	For Each oView In oSheet.DrawingViews
	
	oDocName = oView.ReferencedDocumentDescriptor.DisplayName
	If Not oDocName = oDocNamePrevious
	i = 0
	While i < oPartsList.PartsListRows.Count
		i = i + 1
	If oDocName = oPartsList.PartsListRows.Item(i).Item("FILE NAME").Value
		If oPartsList.PartsListRows.Item(i).Item("Sheet Number").Value = ""
		oSheetNumber = oPartsList.PartsListRows.Item(i).Item("Sheet Number").Value
		oSheetNumber = oSheetNumber & sheetNum
		oPartsList.PartsListRows.Item(i).Item("Sheet Number").Value = oSheetNumber
		Else
		oSheetNumber = oPartsList.PartsListRows.Item(i).Item("Sheet Number").Value
		oSheetNumber = oSheetNumber & ", " & sheetNum
		oPartsList.PartsListRows.Item(i).Item("Sheet Number").Value = oSheetNumber
		End If
	End If
	End While
	oDocNamePrevious = oView.ReferencedDocumentDescriptor.DisplayName
	End if
Next
sheetNum = sheetNum + 1
Next

oPartsList.PartsListColumns.Item("FILE NAME").Remove

[cadman777]

1. OK, thanx for the explanations!

2. Now I get it ("FILE NAME"). Since I never used that iProp in the past (for over 20 years of Inventor!), I never noticed it. Your code example makes sense now.

3. Sorry, but I can't post the drgs since they are NDA privacy data.

4. OK, now I see.  This is the part of the code I wasn't getting, but now it's clear:

- traverse all partslist columns again to get the columnindex number of the sheet number column
- traverse all partslist rows
    - get the referenced file (the fullfilename)
    - call function GetSheetNumber
        - traversing all sheets in the drawing document
        - on each sheet traverse all drawingviews
        - compare the referenced document (FullFileName) of the drawingview with the referenced file (FullFilName) of the partslist row
        - if FullFileNames match, add the sheetnumber to the string GetSheetNumber
        - if all drawingviews on all sheets are processed, return the GetSheetNumber String

You just have to meander your way though the parts list and drawing sheets and compile all the sheet numbers.

I'll try to figure out why it's not working and see if I can fix it.

Will get back when a result happens.

But this work takes me many hours when it takes you only minutes.

Thanx for helping!

[cadman777]

Thanx for revising your rule!

Yes, I realize it's iLogic.

But it's harder to figure out what's wrong in iLogic more than VBA, so I first have to understand what's going on and then try to figure out how to make it work.

I assumed your rule will check for a "Sheet Number" column and create it if it doesn't exist.

Is that correct or do I have to add the "Sheet Number" column before running your rule?

Anyway, I ran your rule and it failed again the same way with this error:

I must admit that those error messages make absolutely no sense to me!

Thanx for helping!

[dalton98]

Oh I didn't add that. Do this at the start

Dim oDoc As DrawingDocument
oDoc = ThisApplication.ActiveDocument

Dim oPartsList As PartsList
oPartsList = oDoc.ActiveSheet.PartsLists.Item(1)

Dim oPartsListColumn As PartsListColumn
Try
oPartsList.PartsListColumns.Add(PropertyTypeEnum.kCustomProperty,, "Sheet Number")  
Catch
End Try

[Ralf_Krieg]

Hello

Found there's missing the check for duplicate sheet number entries. I understand that non public information can not be offered in a forum. You can try to comment out the marked two lines to see if the activated "do not count" option is the reason. The sheetnumbers may then show wrong values.

Option Explicit

Private Sub SheetNumber()

    Dim sSheetNumberColumn As String
    sSheetNumberColumn = "Sheet Number" '<------ Name of the parts list column to save the sheet numbers to
    
    Dim oApp As Inventor.Application
    Set oApp = ThisApplication
    
    Dim oDrawDoc As DrawingDocument
    Set oDrawDoc = oApp.ActiveDocument
    
    Dim oSheet As Sheet
    Set oSheet = oDrawDoc.ActiveSheet
    
    Dim oPartsList As PartsList
    Set oPartsList = oSheet.PartsLists(1)
    
    Dim oPLCol As PartsListColumn
    For Each oPLCol In oPartsList.PartsListColumns
        If oPLCol.Title = sSheetNumberColumn Then Exit For
    Next
    
    If oPLCol Is Nothing Then
        Set oPLCol = oPartsList.PartsListColumns.Add(kCustomProperty, , sSheetNumberColumn)
    End If
    
    Dim iColNumber As Integer
    For iColNumber = 1 To oPartsList.PartsListColumns.Count
        If oPartsList.PartsListColumns(iColNumber).Title = sSheetNumberColumn Then Exit For
    Next
    
    Dim oPLRow As PartsListRow
    Dim srefedfile As String
    Dim sSheetNumber As String
    For Each oPLRow In oPartsList.PartsListRows
        srefedfile = oPLRow.ReferencedFiles(1).FullFileName
        sSheetNumber = GetSheetnumber(oDrawDoc.Sheets, srefedfile)
        oPLRow.Item(iColNumber).Value = sSheetNumber
    Next

End Sub

Private Function GetSheetnumber(ByVal oSheets As Sheets, ByVal srefedfile As String) As String
    Dim i As Integer
    Dim oSheet As Sheet
    Dim oDrawView As DrawingView
    Dim oSheetsList  As Object
    Set oSheetsList = CreateObject("System.Collections.ArrayList")

    For Each oSheet In oSheets
        If oSheet.ExcludeFromCount = False Then '<----- Comment out to include every sheet
            i = i + 1
            For Each oDrawView In oSheet.DrawingViews
                If oDrawView.ReferencedDocumentDescriptor.ReferencedFileDescriptor.FullFileName = srefedfile Then
                    If Not oSheetsList.Contains(i) Then
                        oSheetsList.Add (i)
                    End If
                End If
            Next
        End If                                  '<----- Comment out to include every sheet
    Next
    
    oSheetsList.Sort
    
    Dim Item As Variant
    For Each Item In oSheetsList
        If GetSheetnumber = "" Then
            GetSheetnumber = Item
        Else
            GetSheetnumber = GetSheetnumber & ", " & Item
        End If
    Next
                    
End Function

[cadman777]

Hey thanx for the updated code, the addition works great!

Only thing is, I'm still getting the same error.

Used 'MessageBox.Show()' to see what's going on in the rule, and this is what I found:

'oDocName' is always the assembly file name on every sheet, even when the assembly file is not in any of the views.

The rule fails to run after the oDocName line of code.

What's your code supposed to do?

If you tell me that then maybe I can figure it out.

Thanx!

[cadman777]

Thanx for the updated macro!

Nice! Amazing! It works most of the way when I comment out the 2 lines you identified.

Interesting note: I never have any 'non-excluded sheets' in my drawings.

I don't even know how to do that, even after all these years!

The thing I noticed is that if there is more than one view on a sheet (each view having its own PartsList), then the macro runs on only one view on the entire sheet. Is it designed to do that? Does the macro needs a 'For Each oPartsList...' code.

What does the (1) do in this line?

Set oPartsList = oSheet.PartsLists(1)

I dug around a little in the ObjectBrowser and found in the Help file some code that matches yours, except for what you do at the end. Unfortunately I don't know how to modify it to look at every PartsList on the sheet!

You code guys have quite a useful talent!

[dalton98]

@cadman777 I would just use @Ralf_Krieg 's rule it works much better. I got it to work using ilogic

Sub Main SheetNumber()

    Dim sSheetNumberColumn As String
    sSheetNumberColumn = "Sheet Number" '<------ Name of the parts list column to save the sheet numbers to
    
    Dim oApp As Inventor.Application
    oApp = ThisApplication
    
    Dim oDrawDoc As DrawingDocument
    oDrawDoc = oApp.ActiveDocument
    
    Dim oSheet As Sheet
    oSheet = oDrawDoc.ActiveSheet
    
    Dim oPartsList As PartsList
    oPartsList = oSheet.PartsLists(1)
    
'    Dim oPLCol As PartsListColumn
'    For Each oPLCol In oPartsList.PartsListColumns
'        If oPLCol.Title = sSheetNumberColumn Then Exit For
'    Next
    
'    If oPLCol Is Nothing Then
		Try
        oPLCol = oPartsList.PartsListColumns.Add(kCustomProperty, , sSheetNumberColumn)
		Catch
		End Try
'    End If
    
    Dim iColNumber As Integer
    For iColNumber = 1 To oPartsList.PartsListColumns.Count
        If oPartsList.PartsListColumns(iColNumber).Title = sSheetNumberColumn Then Exit For
    Next
    
    Dim oPLRow As PartsListRow
    Dim srefedfile As String
    Dim sSheetNumber As String
    For Each oPLRow In oPartsList.PartsListRows
        srefedfile = oPLRow.ReferencedFiles(1).FullFileName
        sSheetNumber = GetSheetnumber(oDrawDoc.Sheets, srefedfile)
        oPLRow.Item(iColNumber).Value = sSheetNumber
    Next

End Sub

Private Function GetSheetnumber(ByVal oSheets As Sheets, ByVal srefedfile As String) As String
    Dim i As Integer
    Dim oSheet As Sheet
    Dim oDrawView As DrawingView
    Dim oSheetsList  As Object
    oSheetsList = CreateObject("System.Collections.ArrayList")

    For Each oSheet In oSheets
        If oSheet.ExcludeFromCount = False Then '<----- Comment out to include every sheet
            i = i + 1
            For Each oDrawView In oSheet.DrawingViews
                If oDrawView.ReferencedDocumentDescriptor.ReferencedFileDescriptor.FullFileName = srefedfile Then
                    If Not oSheetsList.Contains(i) Then
                        oSheetsList.Add (i)
                    End If
                End If
            Next
        End If                                  '<----- Comment out to include every sheet
    Next
    
    oSheetsList.Sort
    
    Dim Item As Object
    For Each Item In oSheetsList
        If GetSheetnumber = "" Then
            GetSheetnumber = Item
        Else
            GetSheetnumber = GetSheetnumber & ", " & Item
        End If
    Next
                    
End Function