Linking Record formats to referenced Excel worksheets

[Tim Harland]

I am starting to explore the new excel referencing in Vectorworks 2024. Am I correct in thinking that only 'dumb' / self contained worksheets can be referenced, ie if we have a worksheet with database rows and or fields that are reading information from record formats there is no way to link them to an excel file? This seems to limit the benefits of this new feature significantly if the worksheet can't take advantage of the data contained within the vectorworks file? Or am I missing how to do this?

How I had hoped this feature would work when I saw it announced would have been; that when have for instance our door schedule generated with information from our door symbols, that we would have been able to link that schedule to an excel file where we could format it more easily and use the filtering funtion in excel to interact with our schedule, and any changes in the vectorworks files would be reflected in the excel file. In an ideal world we would also be able to update any fields not linked to the geometry or location of the object.

[Tom W.]

@Luka Stefanovic talks about the new excel functionality in this recent coffee break:

 

https://university.vectorworks.net/mod/overview/view.php?id=5846

 

37:40 onwards might have some relevance to what you're talking about.

[Luka Stefanovic]

@Tim Harland In short, there are two ways you can use referenced Excel data in Vectorworks - just bringing a worksheet in and viewing it as such, for example a consultant list, or a product data sheet. The other, more complex way, is to use referenced Excel data in another database worksheet - for example, creating a furniture schedule that counts the Symbols in your model and hooking that up to a product price list from a manufacturer.

In both cases, integration goes both ways - if you make a change in Excel and update the reference, the data in Vectorworks worksheet will be updated. If you make a change in Vectorworks, you can push it back to Excel and update the worksheet. 

What you seem to be describing is the complex workflow in reverse. It is certainly possible, but I think it's overly complicating - taking advantage of reading the model as a database, then pushing that data back to Excel to be a plain spreadsheet just for the sake of formatting in Excel, which can be done in Vectorworks too, seems overly complicating the workflow to me. As I said, you should be able to do it, but I would recommend a simpler way of formatting a door schedule in Vectorworks.

Good luck with setting it up! 

[Tim Harland]

Thanks @Tom W. that coffee break was quite helpful - I must have missed it.

HI @Luka Stefanovic OK that is good to know it can be done. It wouldn't just be for formating, we would also use it for data entry, especially as it is much quicker and less destructive to filter lists in excel. There are also still advantages to formatting in excel (printing over multiple pages for instance, adding headers & footers, printing headers on multiple pages) unless there are additional printing option for worksheets that I have missed in the last few versions? It is also necessary for excahnging information with clients and consultants.

As far as I understand it from your Coffee Break, we would have to have a referenced version of the Excel Schedule that is linked to a Database worksheet (eg. via the Door number or a Door Type). But all the information about the item being scheduled would have to be just in the excel file - we wouldn't be able to have the information about the item as a record field in the symbol?  In the coffee break example would it have been possible to change any of the information about the sanitaryware products within vectorworks? I guess it would be possible in the refenced Excel file, but not in the Database worksheet? So again in that example you wouldn't be able to select all sanitary objects from a particular manufacturer, or with a particular finish?

In your example would it have been possible to list all the objects indivually and add other information from a record format eg Layer or class or a record format attached to the symbol? That could potentially work for us.

What would be most useful for us would be to be able to for instance change the fire rating of a door in vectorworks and then update the excel version of our door schedule to reflect this without having to do an export. Of course it would be amazing if we could also make changes in the excel that would be reflected in the vectorworks file but I can see how that is technically very challenging and would essentially need a permanet connection to the vectorwokrs file to know the state of all the objects that were being scheduled.

[Luka Stefanovic]

Hi @Tim Harland it sounds complex what you're trying to achieve. Can you send an example of what you're trying to do, in Excel and in Vectorworks, and I can try to put it together as a sample. 

I think what you describe should work but it's easier to understand by looking at actual examples rather than just a description 🙂

Thanks, Luka

[Amanda McDermott]

On 11/23/2023 at 9:34 AM, Luka Stefanovic said:

use referenced Excel data in another database worksheet

Hello - I'm wanting to use this in a similar way to Tim, I think - for plant schedules. I want to rearrange and group plants in excel because it's easier, maybe add in header rows etc. But will need to keep updating numbers if landscape area sizes change / species get swapped in etc. The webinar was useful, but I didn't understand how you had "referenced the referenced excel in the worksheet", which sounds like what I need to do...

[Tim Harland]

On 12/6/2023 at 7:14 PM, Luka Stefanovic said:

Hi @Tim Harland it sounds complex what you're trying to achieve. Can you send an example of what you're trying to do, in Excel and in Vectorworks, and I can try to put it together as a sample. 

I think what you describe should work but it's easier to understand by looking at actual examples rather than just a description 🙂

Thanks, Luka

Hi Luca, yes I'll pull something together - I suspect what we are after is going more on the direction of the SQLITE Databases post that was in a recent newsletter, albeit even in those videos it didn't seem to be possible to link back out to the database from record fields.

[Luka Stefanovic]

@Tim Harland I still think it would be possible to do it through Excel Referencing, but I'll need those examples to understand what exactly needs to be done. Let me know when you put it together and I will try to do it.

 

@Amanda McDermott Can you also send me an example of what you're trying to do along with a Vectorworks and Excel file and I can see if I can put that one together for you. It sounds like it should be possible to do. 

 

Thanks! And happy holidays! 

[Amanda McDermott]

4 hours ago, Luka Stefanovic said:

@Tim Harland I still think it would be possible to do it through Excel Referencing, but I'll need those examples to understand what exactly needs to be done. Let me know when you put it together and I will try to do it.

 

@Amanda McDermott Can you also send me an example of what you're trying to do along with a Vectorworks and Excel file and I can see if I can put that one together for you. It sounds like it should be possible to do. 

 

Thanks! And happy holidays! 

Thank you very much for the offer - I'm about to leave my desk until the new year, but will send something through then. All the best for the festive season!

[Maury Jensen]

@Tim Harland 

 

Came across this topic when trying to figure out an excel referencing thing.  

 

I have a site plan with symbols, each symbol will have a record attached to it with a unique number / letter code.  I've attached a file.   

 

When I enter the "Footprint ID" into the "Vendor Record" connected to the symbol I want to pull the related data from the referenced excel worksheet

When I alter the data in the Length and Depth fields in the vendor record I want it to update the referenced excel worksheet

 

Basically a two way sync.

 

What is the best & cleanest way to do this?

 

To put it more simply, I need to two way sync a worksheet that reports symbol record data to an external excel file.  The footprint ID field would be the "Key field" that is used to match up the data. I can enter the footprint ID manually into each symbol's record field. 

 

I watched the coffee break above and it looks like this is possible, but I'm struggling to understand how to set this up.  Mainly, how do I sync the worksheet that is made from the records in the drawing with the worksheet that is synced to the external excel file? Or can a worksheet made from the record formats be synced to an excel file on its own? Do I need two worksheets in this case? 

 

Any help greatly appreciated!  Thanks. 

Connect Record to Referenced Worksheet.vwx Footprint Master Spreadsheet.xlsx

[tspilman]

Hi @Luka Stefanovic , in this video (at about 37:26) you talk about advanced features of the excel import and linking the fields with a schedule. 
Is there any documentation on how you achieved this? I cannot find any way of getting this to work. 

Also, at 36:49, you show a table layout and a worksheet - these appear to be linked; with data tags attached to each seat (this seems like an obvious way to take advantage of the excel import capabilities). Are the graphic layout and the worksheet linked? Or would you have to make changes in excel / VW to update the spreadsheet, then also have to go into the drawing to update the graphical representation?

(I would assume they are linked - or at least there is a road map for them to become linked in the future)
If there is support for this, please could you link me to it? I'm struggling to find the appropriate resources within the VW help system, so could do with some guidance on what would have been a successful search.

Thanks in advance. 

Edited March 27 by tspilman

[msharon]

I did a video a few weeks ago that may be of help. https://university.vectorworks.net/course/view.php?id=3195

[Jesse Cogswell]

I watched the Vectorworks University video and, no offense to Vectorworks, but big yikes!  Sometimes I wonder if they think we get paid by the click.

 

@Maury Jensen contacted me earlier this week about this topic, and I sat down and wrote him a menu command to do this.  The menu command launches a dialog box letting you choose a Worksheet, a Record to link it to, and a Field to use as a key.  When you press OK, it will go through the chosen Worksheet line by line and scour the drawing for objects that have the chosen Record attached with a matching key.  When it finds one, it will go through the Record Fields and pull the data from the Worksheet with the matching key, then reset the object.  The tool also runs a "Deselect All" command when you press OK, and will select any affected object, so upon completion, you should be able to easily tell which objects have been updated.

 

 

The tool requires the Worksheet to have a header row with the columns matching exactly the Record Field names, so be careful about extra spaces and capitalization.  It will do some basic error checking for you, when you select a Worksheet or Record, it will compare the first row of the Worksheet and the Record Field names.  If any Record Field does not have a matching column, the Status will change to Column Name / Record Field Mismatch and the Check Mismatch button will be enabled.  If you click the button, it will pop up an alert for each missing Record Field.  That said, the tool won't stop you from clicking OK when there's a mismatch, so if you have extra Record Fields that you don't want on the Worksheet, you can.

 

It will also save your choices, so if you run the command again, it will have the Worksheet, Record, and Key Field will be pre-selected, so it's very quick to update as your Worksheet is updated.

 

For Maury's test case, he used a Worksheet that was referencing an external Excel document.  One huge advantage of this system over the one highlighted in the Vectorworks University video is that this also maintains the Excel file rather than having to shuttle a CSV file between Excel and SQ Lite, so it's a little easier to maintain as Excel is a bit friendlier than an SQL database.

 

To install this plug-in, follow the steps below:

1. Download the attached Link Worksheet to Record.zip file, but do not extract it.
2. In Vectorworks, go to Tools - Plug-ins - Plug-in Manager.
3. Click on the Third-party plug-ins tab.
4. Click on the Install button.
5. Navigate to the downloaded .zip file.
6. Restart Vectorworks.
7. Once restarted, you will need to add the plug-in to your Workspace.
8. Go to Tools - Workspaces - Edit Current Workspace.
9. In the Workspace Editor, click on the Menus tab.
10. In the box on the left, find and expand the JNC category.
11. In the box on the right, find a menu where you'd like to have the command, such as Tools.
12. Click and drag the Link Worksheet to Record command from the box on the left to the desired menu in the box on the right.
13. Click OK to confirm and rebuild your Workspace.

The plug-in should work with all versions of Vectorworks from 2019 or newer.  Please let me know if anything breaks.

Link Worksheet to Record.zip

[Jeff Prince]

15 hours ago, Jesse Cogswell said:

Sometimes I wonder if they think we get paid by the click.

If I had a nickel for every click…