Jump to content

Worksheet of Classes in a File

Pat Stanford

Recommended Posts

Pat Stanford

Pat Stanford

Posted
Posted

The attached script will create a new worksheet listing the used and unused classes in a file.

Copy the lines from Procedure to Run (inclusive) and paste them into a new blank Vectorscript (created from the Resource Browser). Open the script palette and double click on the script.

The script automatically places the worksheet at the origin of the drawing.

Use as you see fit.

Pat

Procedure Classes_to_Worksheet;

{Make a worksheet listing all of the classes in a VW file}

{Lists both used and unused classes}

{The worksheet is named "Classes:"with and appended date}

{October 14, 2008}

{? 2008, Coviana, Inc - Pat Stanford pat@coviana.com}

{Licensed under the GNU Lesser General Public License}

var H1: Handle;

N1: LongInt;

Begin

H1:=CreateWS(Concat('Classes:',date(2,1)),Classnum+2,2);

For N1:= 1 to Classnum do

SetWSCellFormula(H1,N1+1,1,N1+1,1,ClassList(N1));

SetWSCellFormula(H1,1,1,1,1,'Classes in File');

End;

Run(Classes_to_Worksheet);

Link to comment
  • 1 year later...
Pat Stanford

Pat Stanford

Posted
  • Author
Posted

Here is an updated version that sorts the classes alphabetically before creating the worksheet. As of VW2011 I don't think there is a way to get the display order of the classes in the Navigation Palette.

Procedure Classes_to_Worksheet;
{Make a worksheet listing all of the classes in a VW file}
{Lists both used and unused classes}
{The worksheet is named "Classes:"with and appended date}
{October 13, 2010}
{Updated to sort classes in alphabetical order prior to storing in worksheet}
{As of VW2011, there is not way to get the display order of the classes in the Nav Palette}
{October 14, 2008}
{? 2010, 2008, Coviana, Inc - Pat Stanford pat@coviana.com}
{Licensed under the GNU Lesser General Public License}

var	 H1:	Handle;
N1:	LongInt;
ClassSort : Array[1..1024] of string;

Begin
H1:=CreateWS(Concat('Classes:',date(2,1)),Classnum+2,2);

For N1:= 1 to Classnum do ClassSort[N1]:=ClassList(N1);

SortArray(ClassSort,ClassNum,1);

For N1:= 1 to Classnum do SetWSCellFormula(H1,N1+1,1,N1+1,1,ClassSort[N1]);	

SetWSCellFormula(H1,1,1,1,1,'Classes in File');


End;
Run(Classes_to_Worksheet);

Link to comment
  • 4 years later...
TimG

TimG

Posted
Posted (edited)

Nevermind... i didn't see any other reply's due to my forum settings, I see you updated the script for alphabetical... I'll give that a try. sorry

I got this script to work, Thanks...

However, is there a way I can get it to list in a worksheet alphabetically?

When I run the script as us above, I think it maybe listing them in the worksheet by the order they were created in?

Edited by TimG
Link to comment
  • 4 months later...
Pat Stanford

Pat Stanford

Posted
  • Author
Posted

OK, I updated this again. This one is kind of nifty.

It goes through and makes a list of all the Classes (used and unused) in a file along with the number of objects in that class. Just like before, but with better formatting.

The Nifty part is that I have added some database integration. After you run the script to create the worksheet, if you change any of the yellow cells in column A from blank to anything (try typing an X), a related database row at the bottom of the worksheet will open showing subrows for every object in the class. Each subrow will show the Class, Layer and Object type. Like with any other database, you can right-click on the sub-row row header (i.e. 40.7) and SELECT that item in the drawing and change views so it is visible.

Please note that this is a fairly slow script and worksheet. It takes about 25 seconds on my 2008 MacBook Pro to run on a file with about 50 classes and 1300 objects. It has to make a separate database for each class and every object in the drawing will be included in one of those databases.

Use at your own risk.

Procedure Classes_to_Worksheet;
{Make a worksheet listing all of the classes in a VW file}
{Lists both used and unused classes}
{The worksheet is named "Classes:"with and appended date}

{April 10, 2015}
{Updated for formatting and to add database integration}
{Set any of the yellow cells in column 1 to non-blank and the related}
{database row at the bottom of the worksheet will show subrows for each item in that class}
{Set the yellow cell back to blank (select, delete, enter/return, and the database subrows}
{will disapper.  The database rows show the Class, Layer and Object type for the related object}

{October 13, 2010}
{Updated to sort classes in alphabetical order prior to storing in worksheet}
{As of VW2011, there is not way to get the display order of the classes in the Nav Palette}
{October 14, 2008}
{? 2010, 2008, Coviana, Inc - Pat Stanford pat@coviana.com}
{Licensed under the GNU Lesser General Public License}

var	 H1, H2:	Handle;
N1:	LongInt;
ClassSort : Array[1..1024] of string;
Formula : String;
Result : Boolean;
Font,Size,Style:Integer;

Begin
SetCursor(WatchC);

H1:=CreateWS(Concat('Classes:',date(2,1)),(Classnum+2)*2,4);
SetWSCellAlignment(H1,1,1,ClassNum+2,2,5);
SetWSColumnWidth(H1,1,1,20);
SetWSColumnWidth(H1,2,2,200);
SetWSColumnWidth(H1,3,3,200);
SetWSColumnWidth(H1,4,4,200);

Result := WorksheetMergeCells(H1,1,1,1,3);

GetWSCellTextFormat(H1,1,1,Font,Size,Style);

SetWSCellTextFormat(H1,1,1,(ClassNum+2)*2,4,Font,14,Style);
SetWSCellTextFormat(H1,1,1,1,1,Font,18,1);
SetWSCellTextFormat(H1,2,1,2,4,Font,14,1);
SetWSCellTextFormat(H1,Classnum+4,1,ClassNum+4,4,Font,14,1);

SetWSCellBorders(H1,3,1,ClassNum+2,3,True,True,True,True,7);
SetWSCellOutlineBorder(H1,3,1,ClassNum+2,3,2,25,1);
SetWSCellBorders(H1,Classnum+5,1,(ClassNum+2)*2,4,True,True,True,True,7);
SetWSCellOutlineBorder(H1,Classnum+5,1,(ClassNum+2)*2,4,2,25,1);

SetWSCellFill(H1,3,1,ClassNum+2,1,1,5,0,1);
SetWSCellFill(H1,ClassNum+5,1,(ClassNum+2)*2,1,1,1,0,1);


For N1:= 1 to Classnum do ClassSort[N1]:=ClassList(N1);

SortArray(ClassSort,ClassNum,1);

For N1:= 1 to Classnum do 
Begin
	SetWSCellNumberFormat(H1,N1+2,2,N1+2,2,13,0,'','');
	SetWSCellFormula(H1,N1+2,2,N1+2,2,Concat(ClassSort[N1]));	

	Formula:=Concat('=Count(C=',CHR(39),Classsort[N1],CHR(39),')');

	SetWSCellFormula(H1,N1+2,3,N1+2,3,Formula);

	Formula:=Concat('=Database(C=IF(A',Num2Str(0,N1+2),'<>',CHR(39),CHR(39),', B',Num2Str(0,N1+2),',',CHR(39),'PTS!@#$%^&',CHR(39),'))');

	SetWSCellFormula(H1,ClassNum+4,2,ClassNum+4,2,'Class');
	SetWSCellFormula(H1,ClassNum+4,3,ClassNum+4,3,'Layer');
	SetWSCellFormula(H1,ClassNum+4,4,ClassNum+4,4,'Object Type');

	SetWSCellFormula(H1,N1+ClassNum+4,0,N1+ClassNum+4,0,Formula);
	SetWSCellFormula(H1,N1+ClassNum+4,2,N1+ClassNum+4,2,'=C');
	SetWSCellFormula(H1,N1+ClassNum+4,3,N1+ClassNum+4,3,'=L');
	SetWSCellFormula(H1,N1+ClassNum+4,4,N1+ClassNum+4,4,'=T');

	Message('Number of Classes Processes: ',N1);
	Wait(1);
End;

SetWSCellFormula(H1,1,1,1,1,Concat('Classes in File:',date(2,1)));
SetWSCellFormula(H1,2,2,2,2,'Class Name');
SetWSCellFormula(H1,2,3,2,3,'# of Objects in Class');

RecalculateWS(H1);
SetCursor(ArrowC);

ClrMessage;
ShowWS(H1,True);
End;
Run(Classes_to_Worksheet);

  • Like 2
Link to comment
michaelk

michaelk

Posted
Posted

Every time I take over a drawing from someone else I have a similar worksheet called "Find Crazy Things" that I modify manually to track down objects in a '0' class or find all the windows and doors that are in a DTM-Modifer class.

I'm going to have to take your script apart. I think I get how you did it. Except for PTS!@#$%^&. What's up with that?

mk

Link to comment
Pat Stanford

Pat Stanford

Posted
  • Author
Posted

It is probably easier to figure out how this all works from the worksheet than from the script. In the worksheet I just used the script to create a single database line for each class using a formula similar to:

=database(If(A5<>'',C=B5,C='Name never to be used by a real class'))

The trick in the script is to use CHR(39) to generate the single quotes needed as delimiters in the formula.

Link to comment
  • 1 year later...
  • 3 years later...
  • 2 years later...
Elite Exhibits

Elite Exhibits

Posted
Posted
On 4/10/2015 at 10:18 PM, Pat Stanford said:

OK, I updated this again. This one is kind of nifty.

It goes through and makes a list of all the Classes (used and unused) in a file along with the number of objects in that class. Just like before, but with better formatting.

The Nifty part is that I have added some database integration. After you run the script to create the worksheet, if you change any of the yellow cells in column A from blank to anything (try typing an X), a related database row at the bottom of the worksheet will open showing subrows for every object in the class. Each subrow will show the Class, Layer and Object type. Like with any other database, you can right-click on the sub-row row header (i.e. 40.7) and SELECT that item in the drawing and change views so it is visible.

Please note that this is a fairly slow script and worksheet. It takes about 25 seconds on my 2008 MacBook Pro to run on a file with about 50 classes and 1300 objects. It has to make a separate database for each class and every object in the drawing will be included in one of those databases.

Use at your own risk.

  

Procedure Classes_to_Worksheet;
{Make a worksheet listing all of the classes in a VW file}
{Lists both used and unused classes}
{The worksheet is named "Classes:"with and appended date}

{April 10, 2015}
{Updated for formatting and to add database integration}
{Set any of the yellow cells in column 1 to non-blank and the related}
{database row at the bottom of the worksheet will show subrows for each item in that class}
{Set the yellow cell back to blank (select, delete, enter/return, and the database subrows}
{will disapper.  The database rows show the Class, Layer and Object type for the related object}

{October 13, 2010}
{Updated to sort classes in alphabetical order prior to storing in worksheet}
{As of VW2011, there is not way to get the display order of the classes in the Nav Palette}
{October 14, 2008}
{? 2010, 2008, Coviana, Inc - Pat Stanford pat@coviana.com}
{Licensed under the GNU Lesser General Public License}

var	 H1, H2:	Handle;
N1:	LongInt;
ClassSort : Array[1..1024] of string;
Formula : String;
Result : Boolean;
Font,Size,Style:Integer;

Begin
SetCursor(WatchC);

H1:=CreateWS(Concat('Classes:',date(2,1)),(Classnum+2)*2,4);
SetWSCellAlignment(H1,1,1,ClassNum+2,2,5);
SetWSColumnWidth(H1,1,1,20);
SetWSColumnWidth(H1,2,2,200);
SetWSColumnWidth(H1,3,3,200);
SetWSColumnWidth(H1,4,4,200);

Result := WorksheetMergeCells(H1,1,1,1,3);

GetWSCellTextFormat(H1,1,1,Font,Size,Style);

SetWSCellTextFormat(H1,1,1,(ClassNum+2)*2,4,Font,14,Style);
SetWSCellTextFormat(H1,1,1,1,1,Font,18,1);
SetWSCellTextFormat(H1,2,1,2,4,Font,14,1);
SetWSCellTextFormat(H1,Classnum+4,1,ClassNum+4,4,Font,14,1);

SetWSCellBorders(H1,3,1,ClassNum+2,3,True,True,True,True,7);
SetWSCellOutlineBorder(H1,3,1,ClassNum+2,3,2,25,1);
SetWSCellBorders(H1,Classnum+5,1,(ClassNum+2)*2,4,True,True,True,True,7);
SetWSCellOutlineBorder(H1,Classnum+5,1,(ClassNum+2)*2,4,2,25,1);

SetWSCellFill(H1,3,1,ClassNum+2,1,1,5,0,1);
SetWSCellFill(H1,ClassNum+5,1,(ClassNum+2)*2,1,1,1,0,1);


For N1:= 1 to Classnum do ClassSort[N1]:=ClassList(N1);

SortArray(ClassSort,ClassNum,1);

For N1:= 1 to Classnum do 
Begin
	SetWSCellNumberFormat(H1,N1+2,2,N1+2,2,13,0,'','');
	SetWSCellFormula(H1,N1+2,2,N1+2,2,Concat(ClassSort[N1]));	

	Formula:=Concat('=Count(C=',CHR(39),Classsort[N1],CHR(39),')');

	SetWSCellFormula(H1,N1+2,3,N1+2,3,Formula);

	Formula:=Concat('=Database(C=IF(A',Num2Str(0,N1+2),'<>',CHR(39),CHR(39),', B',Num2Str(0,N1+2),',',CHR(39),'PTS!@#$%^&',CHR(39),'))');

	SetWSCellFormula(H1,ClassNum+4,2,ClassNum+4,2,'Class');
	SetWSCellFormula(H1,ClassNum+4,3,ClassNum+4,3,'Layer');
	SetWSCellFormula(H1,ClassNum+4,4,ClassNum+4,4,'Object Type');

	SetWSCellFormula(H1,N1+ClassNum+4,0,N1+ClassNum+4,0,Formula);
	SetWSCellFormula(H1,N1+ClassNum+4,2,N1+ClassNum+4,2,'=C');
	SetWSCellFormula(H1,N1+ClassNum+4,3,N1+ClassNum+4,3,'=L');
	SetWSCellFormula(H1,N1+ClassNum+4,4,N1+ClassNum+4,4,'=T');

	Message('Number of Classes Processes: ',N1);
	Wait(1);
End;

SetWSCellFormula(H1,1,1,1,1,Concat('Classes in File:',date(2,1)));
SetWSCellFormula(H1,2,2,2,2,'Class Name');
SetWSCellFormula(H1,2,3,2,3,'# of Objects in Class');

RecalculateWS(H1);
SetCursor(ArrowC);

ClrMessage;
ShowWS(H1,True);
End;
Run(Classes_to_Worksheet);
 

 

Pat

 

Is there a version of the script that works in 2020 - 2023 ?

 

Appreciated

 

Peter

Link to comment
Pat Stanford

Pat Stanford

Posted
  • Author
Posted

I thought I had already posted this, but here is the version that was modified for VW2019. In a very quick test it appeared to work fine in VW2023.

 

HTH.

  

Procedure Classes_to_Worksheet;
{Make a worksheet listing all of the classes in a VW file}
{Lists both used and unused classes}
{The worksheet is named "Classes:"with and appended date}

{April 12, 2019}
{Modified to work around obsolete VS functions for formatting Worksheets}
{Yellow check mark cells to select classes to detail are now formatted as text so data will execute.}
{Datbase Headers are now turned off this can be changed in the SetObjectVariable Boolean (82) line.}

{January 26, 2017}
{Added code to display the Class Fill color in the ClassName column for the Class}

{April 10, 2015}
{Updated for formatting and to add database integration}
{Set any of the yellow cells in column 1 to non-blank and the related}
{database row at the bottom of the worksheet will show subrows for each item in that class}
{Set the yellow cell back to blank (select, delete, enter/return, and the database subrows}
{will disapper.  The database rows show the Class, Layer and Object type for the related object}

{October 13, 2010}
{Updated to sort classes in alphabetical order prior to storing in worksheet}
{As of VW2011, there is not way to get the display order of the classes in the Nav Palette}
{October 14, 2008}
{© 2019, 2017, 2010, 2008, Coviana, Inc - Pat Stanford pat@coviana.com}
{Licensed under the GNU Lesser General Public License}

var	 H1, H2:	Handle;
N1:	LongInt;
ClassSort : Array[1..1024] of string;
Formula : String;
Result : Boolean;
Font,Size,Style:Integer;
R1,G1,B1,BGColor,FGColor: LongInt;
FillPattern: Integer;

Begin
SetCursor(WatchC);

H1:=CreateWS(Concat('Classes:',date(2,1)),(Classnum+2)*2,4);
SetWSCellAlignment(H1,1,1,ClassNum+2,2,5);
SetWSColumnWidth(H1,1,1,20);
SetWSColumnWidth(H1,2,2,200);
SetWSColumnWidth(H1,3,3,200);
SetWSColumnWidth(H1,4,4,200);

Result := WorksheetMergeCells(H1,1,1,1,3);

GetWSCellTextFormat(H1,1,1,Font,Size,Style);

SetWSCellTextFormat(H1,1,1,(ClassNum+2)*2,4,Font,14,Style);
SetWSCellTextFormat(H1,1,1,1,1,Font,18,1);
SetWSCellTextFormat(H1,2,1,2,4,Font,14,1);
SetWSCellTextFormat(H1,Classnum+4,1,ClassNum+4,4,Font,14,1);

SetWSCellBorders(H1,3,1,ClassNum+2,3,True,True,True,True,7);
{SetWSCellOutlineBorder(H1,3,1,ClassNum+2,3,2,25,1);  (*Commented out 4/12/19 Decprecated*)
}SetWSCellBorders(H1,Classnum+5,1,(ClassNum+2)*2,4,True,True,True,True,7);
{SetWSCellOutlineBorder(H1,Classnum+5,1,(ClassNum+2)*2,4,2,25,1);	(*Commented out 4/12/19 Decprecated*)
}
SetWSCellFill(H1,3,1,ClassNum+2,1,1,5,0,1);
SetWSCellFill(H1,ClassNum+5,1,(ClassNum+2)*2,1,1,1,0,1);


For N1:= 1 to Classnum do ClassSort[N1]:=ClassList(N1);

SortArray(ClassSort,ClassNum,1);

For N1:= 1 to Classnum do 
	Begin

{*****************************}
{Code in this block is used to set the WS Cell Format to match the Class Fill Color and Pattern}
		GetClFillFore(ClassSort[N1],R1,G1,B1);
		RGBToColorIndex(R1,G1,B1,FGColor);
		GetClFillBack(ClassSort[N1],R1,G1,B1);
		RGBToColorIndex(R1,G1,B1,BGColor);
		FillPattern:=GetClFPat(ClassSort[N1]);
		SetWSCellFill(H1,N1+2,2,N1+2,2,1,BGColor,FGColor,FillPattern);
		SetWSCellNumberFormat(H1,N1+2,1,N1+2,1,13,0,'','');  {Added 4/12/2019 Format cells as text}
		
		
{This code can be deleted or commented out without effecting other script functionality}
{*****************************}	
		
		SetWSCellNumberFormat(H1,N1+2,2,N1+2,2,13,0,'','');
		SetWSCellFormula(H1,N1+2,2,N1+2,2,Concat(ClassSort[N1]));	
	
		Formula:=Concat('=Count(C=',CHR(39),Classsort[N1],CHR(39),')');

		SetWSCellFormula(H1,N1+2,3,N1+2,3,Formula);

		Formula:=Concat('=Database(C=(IF(A',Num2Str(0,N1+2),'<>',CHR(39),CHR(39),
				', B',Num2Str(0,N1+2),',',CHR(39),'PTS!@#$%^&',CHR(39),')))');

		SetWSCellFormula(H1,ClassNum+4,2,ClassNum+4,2,'Class');
		SetWSCellFormula(H1,ClassNum+4,3,ClassNum+4,3,'Layer');
		SetWSCellFormula(H1,ClassNum+4,4,ClassNum+4,4,'Object Type');

		SetWSCellFormula(H1,N1+ClassNum+4,0,N1+ClassNum+4,0,Formula);
		SetWSCellFormula(H1,N1+ClassNum+4,2,N1+ClassNum+4,2,'=C');
		SetWSCellFormula(H1,N1+ClassNum+4,3,N1+ClassNum+4,3,'=L');
		SetWSCellFormula(H1,N1+ClassNum+4,4,N1+ClassNum+4,4,'=T');
		SetWSCellNumberFormat(H1,N1+2,2,N1+2,2,13,0,'','');  {Added 4/12/2019 Format cells as text}


	End;

SetWSCellFormula(H1,1,1,1,1,Concat('Classes in File:',date(2,1)));
SetWSCellFormula(H1,2,2,2,2,'Class Name');
SetWSCellFormula(H1,2,3,2,3,'# of Objects in Class');


RecalculateWS(H1);
SetCursor(ArrowC);

ClrMessage;
ShowWS(H1,True);
SetObjectVariableBoolean(H1,82,False);	{Added 4/12/19 to hide database header rows.}
End;

Run(Classes_to_Worksheet);

 

  • Like 2
Link to comment
Elite Exhibits

Elite Exhibits

Posted
Posted
On 4/10/2015 at 10:18 PM, Pat Stanford said:

The Nifty part is that I have added some database integration. After you run the script to create the worksheet, if you change any of the yellow cells in column A from blank to anything (try typing an X), a related database row at the bottom of the worksheet will open showing subrows for every object in the class. Each subrow will show the Class, Layer and Object type. Like with any other database, you can right-click on the sub-row row header (i.e. 40.7) and SELECT that item in the drawing and change views so it is visible.

 

 

Pat

 

¿ You are reference the database option ?

 

Let Me Know ...

 

Peter

Link to comment
  • 1 year later...

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.
Note: Your post will require moderator approval before it will be visible.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
Go to topic listing
×
×
  • Create New...