auto fit columns in parts list

[cadman777]

Hello All,

I just got through searching this forum for some code that 'auto fits columns in the drawing parts list'.

I found 2 threads, but they're very old and don't have what I need.

Does anybody have any code they can share w/me?
My idea, based on what I found, is to count all the characters in all the lines in each column, then widen that column by the longest string + whatever additional characters the user specifies in the code.

Does that sound like it's reasonable and do-able?

Thanx ...

[bradeneuropeArthur]

Hi,

You could already start creating a new Idea in the Idea Station for this.

Regards,

[marcin_otręba]

i would go troug rows and check its height first to get all rows that are heiger tha should be, then from row you can reach each cell ant its value (string), than get corresponding column and its width, then you need only to compare length of cell.cvalue and column width. If you want to auto fit also columns wchich are to wide than omit row height check.

second solution - set up maximum char number for each property and do not allow to be more.

[ckeveryga]

The code from this post works well, just needed to adjust one line. I also made some scaling adjustments due to longer strings causing issues. 

Public Function PartsListFitColumns(oPartsList As PartsList) As PartsList

If oPartsList Is Nothing Then Exit Function

' get font info
Dim dWidth As Double
dWidth = oPartsList.DataTextStyle.FontSize * oPartsList.DataTextStyle.WidthScale


' find longest string in each column and resize column
Dim oRow As PartsListRow
Dim sData As String
Dim iCol As Integer
Dim iCols As Integer

iCols = oPartsList.PartsListColumns.Count

' set up array to hold column widths
Dim iLen() As Integer
ReDim iLen(iCols)

' initialize to header lengths
For iCol = 1 To iCols
	iLen(iCol) = Len(oPartsList.PartsListColumns(iCol).Title)
Next iCol

' loop thru each row
For Each oRow In oPartsList.PartsListRows
	If oRow.Visible = True Then
		For iCol = 1 To iCols
			sData = oRow.Item(iCol).Value ' get the data from the cell
			
			If Len(sData) > iLen(iCol) Then
				iLen(iCol) = Len(sData)
			End If
		Next iCol
	End If
Next oRow

' resize the columns (note add extra 2 character width for padding)
For iCol = 1 To iCols
	oPartsList.PartsListColumns(iCol).Width = dWidth * (iLen(iCol) + 2) / 1.3
Next iCol

Return oPartsList

End Function

[cadman777]

Marcin,

Thanx for the overview.

That's exactly what I had in mind.

Only you added the Row Height too, which I didn't think of b/c it's not much of an issue.

Nice!

[cadman777]

ck,

That's the post I found too (curiously, it's the only post on the subject in this entire forum!).

But since I don't know VBA, I couldn't figure out how to get it to work.

Let me try what you did to see if it works.

Thanx for your input!

[ckeveryga]

The code I posted is using iLogic. You will just need to get the parts list object then pass that to the function. It will be different depending on if you have a drawing open or if you are creating one. 

[cadman777]

OK, thanx.

I tried running it by first manually selecting the PartsList in an open drawing file and then RMB on the macro and pick RUN

But it gives this error:

"All other Sub's or Function's must be after Sub Main()"

Not sure what that means.

[ckeveryga · Accepted solution]

I converted the code from the original post to iLogic. This rule requires you to select the parts list then run the rule and it will resize it. 

Sub Main()
	If Not ThisApplication.ActiveDocument.DocumentType = kDrawingDocumentObject Then
		MessageBox.Show("Can only run rule on drawing documents.")
		Exit Sub
	End If
	
	Dim oDrawDoc As DrawingDocument
	oDrawDoc = ThisApplication.ActiveDocument
	
	Dim oSheet As Sheet
	oSheet = oDrawDoc.Sheets.Item(1)
	
'	For Each oPL As PartsList In oSheet.partslists
		
'	Next
	
	Dim oSelectSet As SelectSet
	oSelectSet = oDrawDoc.SelectSet
	
	If oSelectSet.Count = 0 Then Exit Sub
	If oSelectSet.Item(1).Type <> kPartsListObject Then Exit Sub
		
	Dim oPartslist As PartsList
	oPartslist = oSelectSet.Item(1)
	
	PartsListFitColumns(opartslist)
	
End Sub

Public Function PartsListFitColumns(oPartsList As PartsList) As PartsList

	If oPartsList Is Nothing Then Exit Function

	' get font info
	Dim dWidth As Double
	dWidth = oPartsList.DataTextStyle.FontSize * oPartsList.DataTextStyle.WidthScale


	' find longest string in each column and resize column
	Dim oRow As PartsListRow
	Dim sData As String
	Dim iCol As Integer
	Dim iCols As Integer

	iCols = oPartsList.PartsListColumns.Count

	' set up array to hold column widths
	Dim iLen() As Integer
	ReDim iLen(iCols)

	' initialize to header lengths
	For iCol = 1 To iCols
		iLen(iCol) = Len(oPartsList.PartsListColumns(iCol).Title)
	Next iCol

	' loop thru each row
	For Each oRow In oPartsList.PartsListRows
		If oRow.Visible = True Then
			For iCol = 1 To iCols
				sData = oRow.Item(iCol).Value ' get the data from the cell
				
				If Len(sData) > iLen(iCol) Then
					iLen(iCol) = Len(sData)
				End If
			Next iCol
		End If
	Next oRow

	' resize the columns (note add extra 2 character width for padding)
	For iCol = 1 To iCols
		oPartsList.PartsListColumns(iCol).Width = dWidth * (iLen(iCol) + 2) / 1.3
	Next iCol

	Return oPartsList
End Function

[cadman777]

Great, let me try it!

Now I see how you 'get' the parts list.

You have to tell the macro to find it in the drawing.

I'm not good enough at this iLogic hacking to figure that out yet ...

[cadman777]

OK, It runs w/no errors, but nothing happens to the PartsList.

I forget how to use MessageBox.Show to step through the macro.
Can you send me a link on how to use it?

The ilogic/VBA learning curve is very steep for me b/c all the other stuff in life keeps crowding out the stuff I learned months before!

Thanx for helping ...

[ckeveryga]

You need to make sure the parts list is selected before running the rule. 

To troubleshoot with a messagebox, place one after the exit sub lines to ensure that you are getting past the if statements. It just shows how far the code goes before throwing an error or exiting. 

[cadman777]

Thanx.

Yes, I tried it by first selecting the PartsLilst and nothing happens.

So I found this online and tried it out:

https://www.cadlinecommunity.co.uk/hc/en-us/articles/201769302-Trouble-Shooting-Inventor-iLogic-Erro...

This is the result:

And this:

[cadman777]

ck, did you get this to work on your computer on an Inventor drawing PartsList?

[ckeveryga]

Yes, it works well on my computer. If you were getting no errors maybe try and expand a few columns and see if you need to adjust scaling. Also, I have Inventor 2019 so maybe there is something different for the iLogic code between 2010 and 2019. 

[cadman777]

OK, will do.

Comments:

If the macro works when I 'rescale', then isn't something wrong w/the code which doesn't account for that condition?

Reason I say that is, b/c I snatched a snippet off the forum for SORTING PartsLists, and it worked fine. Then I added a few other controls, which I found in the Inventor VBA Object Library, and they worked too. But when I looked for auto-resizing of Columns, I couldn't find any Objects. All I found were the elements you can use to count records (rows) and columns, and also count line lengths. So I was stuck trying to make my own macro from scratch, which at this point is impossible. But if the code doesn't work, I wonder if there's a limitation in the way it's written OR in the way the Objects work?

Any ideas?

[ckeveryga]

I would say that it is most likely exiting early for some reason. Maybe the select tool is not picking up on the selection. 

Try placing a message box at the end of the rule and see if is in fact running through the entire rule.

You can also try to set a column width the same way you've implemented other methods you mentioned (sorting, counting), because that's the only part list command you should need. 

oPartsList.PartsListColumns(1).Width = 6

[cadman777]

OK, I'll try it.

Thanx!

[cadman777]

Just using the line of code you said in my simple PartsList sort macro, it sizes only the first column.

I figured the sizing units are cm ("1"=1cm)

So it seems the macro needs to process each column, one at a time, counting all the characters in each row, finding the longest row in that column, and then adding a certain number of characters to it to give it some padding (or scale it by % if that's how it works), then move on to the next column and do the same thing, until it's sized all the columns in the PartsList.

Does that sound reasonable to you?

If so, HA! Like I can figure out how to do THAT!

[cadman777]

I just spent the last hour carefully trying to understand your code.

It seems you are doing what I think should be done, but I have some questions.

And I can't quite follow the jumps you make.

Is there any way to test it line by line to see how it runs and where it might fail?

I already did the message box thing and it doesn't returns this error (I inserted it after the EndSub and ran the macro, then after the EndFunction and ran the macro, same result):
"Error on line 37: Declaration expected."

Line 37 is the line that MessageBox.Show is on.

Not sure what that means?