Split option for worksheets in sheet layers

[Marco Zavagno]

We work a lot with worksheets and sometimes they turn out to be unmanageable in the sprinting space of sheet layers. We have to place them into a design layer, create a cropped viewport and split the viewport in the sheet layer to edit the drawing layout.

Would be great to have an option that allows to split the main worksheet into txt linked parts so that if one is resized, the intact of towns and columns adjust accordingly. just like a txt box tool in InDesing.

 

Looking forward for your feedbacks on this.

 

cheers and ciao(s)

[Anders Blomberg]

I was just searching for a way to do this. Would be hugely helpful, and surprising it's not possible at the moment. Even the dinosaur that is AutoCAD does this. I'm currently working on a large site where the table of existing trees is too long for the page size.

[Amorphous - Julian]

What we do as a workaround is to have multiple sheets set up and move the worksheet up on each page to show a different part of the worksheet 
Another ridiculous workaround haha 

[Jesse Cogswell]

I have something of a workaround for this that I've been working on for a while.  There are a couple of caveats that I'll list down below, but I've tested it with a fairly wide variety of worksheets and it seems to work.

 

It is a Vectorscript plug-in I've written attached to this post called Split Worksheet.  It launches a dialog box allowing you to select a worksheet, enter in the number of rows in each "split" worksheet, and whether or not to repeat the first row in each split.  Once you click OK, it copies the formatting and content of the target worksheet and creates new worksheets with the same data and formatting with the naming scheme of "<original worksheet name> SPLIT-#" with the number incrementing upward with each split.  It will even let you overwrite an existing set of splits (in case data has changed or you want to change the number of rows per split sheet).

 

 

For standard worksheet rows, the data will be referenced back to the master sheet, so things like text formatting and images will carry across correctly, and will update if you make changes to the master sheet.  I can't really do that for database rows unfortunately, so the plug-in instead will make the database data "absolute".  So it will appear exactly as it does in the master worksheet, but will no longer reference the master worksheet.  It also will not carry images from a database row, as there's not a great way to pull the source of the image from a database row.  It also will not merge cells for you, though it will copy the data across with references, you'll just have to manually remerge the cells.

 

Now for the caveats.

1. This will only currently work for worksheets of 26 columns or less.  For referencing data, I need the name of a cell (like A2), but I got lazy about writing the algorithm to have columns exceed the alphabet, so it will instead throw a warning if you try to run the command on a worksheet with more than 26 columns.
2. The plug-in will work with any version of Vectorworks from 2019 forward.
3. There is a bug in the SetWSRowHeight procedure that prevents the row height from being applied to a worksheet that has been created or had rows added in the same script.  So, even though it is storing the row heights properly, the newly created sheets will have every row set to "Auto Fit Row Height" regardless of what the master sheet says.  HOWEVER, it seems like that bug has been fixed in VW2022, so if you are most current, this won't be a problem for you.
4. If one of the split worksheets is already on a design or sheet layer and you overwrite it with the command, it will update properly, but the selection box will not until you open the worksheet and close it again.  I could not seem to find a way to get Vectorworks to update this.  However, if you have the option to open the split worksheets upon completion of the command, you won't have to worry about it.
5. I tested this with a worksheet with a little over 300 rows.  VW took a little bit of time to run the command, but it does complete properly.  However, on one of my tests, when I had "Open Split Worksheets" checked in the dialog before pressing OK, Vectorworks hung for a long time before crashing.  But opening the drawing up and trying again was fine, so I don't know what caused it to crash.  If it crashes on big sheets, try running it with "Open Split Worksheets" unchecked.
6. Borders will come across but not necessarily border thickness.  There are a ton of ways to set a cell's border style, weight, and color, but no way to pull that information from an existing cell, just whether or not it has a border.

 

Plug-in installation in Vectorworks is a little obtuse if you haven't done it before.  Instructions are below.

1. Find your plug-ins user folder.  In Windows, this tends to have the default file path C:\Users\<user name>\AppData\Roaming\Nemetschek\Vectorworks\<version number>\Plug-ins.  I have no idea where it is on Mac.  The easy way to get to it, and is best if it has been changed from default is to go to Vectorworks Preferences, navigate to the User Folders tab, and click on the Explore (in Windows) or Open in Finder (in Mac) button.  Then open up the Plug-ins folder.
2. Download the Split Worksheet.vsm file attached to this post into the plug-ins user folder.
3. Restart Vectorworks.
4. Now you will need to add the command to your workspace.  This is achieved by going to Tools - Workspaces - Edit Current Workspace.
5. In the Edit Workspace dialog, click on the Menus tab.
6. In the box on the left, navigate to and expand the category named JNC.
7. In the box on the right, find a suitable place to put the command (like Modify or Tools) and expand it.
8. Drag the Split Worksheets command from the box on the left to the desired menu in the box on the right.
9. Click OK.

Happy splitting and please let me know if anything looks weird or breaks.

 

As a bonus, I am also attaching a menu command that I wrote that will quickly "stripe" a worksheet with alternating fill colors.  I was initially unhappy with it since it didn't work on database rows, but it might come in handy alongside the Split command.

 

Split Worksheet.vsm Stripe Worksheet Rows.vsm

[Anders Blomberg]

Thanks @Jesse Cogswell! I had a go at it with some worksheets and it's really simple and intuitive to use, great job! But I had an issue where I summed the items on the last line so I'll probably end up using @Amorphous - Julian method. Still wish VW would integrate this basic feature though.

 

[Anders Blomberg]

I'm getting some really frustrating results here. See attached video. Some issues:

• One of the worksheets in a viewport only appears when zooming out.
• One of the worksheets in a viewport only appears when adjusting the crop to include the heading.

I've tried exporting to 2021 and have the same issues there. Anyone recognise this?