Jump to content

Linking Record formats to referenced Excel worksheets


Recommended Posts

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.

  • Like 1
Link to comment
  • Vectorworks, Inc Employee

@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! 

  • Like 1
Link to comment

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.

Link to comment
  • 2 weeks later...
  • Vectorworks, Inc Employee

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

Link to comment
  • 2 weeks later...
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...

Link to comment
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.

Link to comment
  • Vectorworks, Inc Employee

@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! 

Link to comment
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!

Link to comment
  • 3 months later...

@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

  • Like 1
Link to comment

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 by tspilman
Link to comment
  • 1 month later...
Posted (edited)

Because I don't know to leave well enough alone, I made a couple further changes to this plug-in.  Don't worry, unless something is broken or someone wants me to add some crazy cool feature, this one is likely finished.

  • The dialog will now remember the Show Hidden Records choice after pressing OK.  This means that if you want to update your record data for a PIO, you won't have to recheck the box and reselect the Record Format, the dialog will automatically load your previous selection.
  • After I did a test for a user trying to link to a Lighting Device (whose record has 100+ fields), I decided to revamp the Check Mismatch button.  Before, it would launch an alert dialog for each missing record field, which was fine on records with only a couple fields, but is incredibly obnoxious when you have a hundred or so.  It now launches a dialog box with a multi-line text box, so you can copy and paste out of it in case you need to really check for spelling/capitalization/extra spaces.

image.thumb.png.24434f1a4861fa489a6108d0647e25df.png

 

Installation remains the same as described above.

 

Link Worksheet to Record.zip

Edited by Jesse Cogswell
  • Like 2
  • Love 1
Link to comment
  • 1 month later...

Hello @Jesse Cogswell

 

Your plug-in is such a life saver! I was wondering if there is any way to update worksheet & referenced excel file by changing record format attached to the object.

A regular worksheet that reports the record items is updated by what is on the vectorworks file, but I was wondering if link from excel to record only works in one-way not the other way around. 

  • Like 1
Link to comment

@J P At the moment, it is strictly one way, from worksheet to record format.  This means that you would need to update the worksheet first and re-run the command if you wanted to maintain a referenced Worksheet.  I like your idea, though and it makes a lot of sense.  When I find some time, I'll add a "Link Direction" option to the dialog, letting you sync up the worksheet from the record data.  I can't really give you much a timeline for this, I have a lot of work to get done before I can devote much time to this, but I'll post the update here once it's ready.

  • Like 3
Link to comment

@Jesse Cogswell Thank you so much! No rush at all. For the time being, I have one worksheet that is linked to the external excel file for data reference that I use to change records & update excel, etc. I decided to have another worksheet that is strictly for reporting VWX object with record, which allow me to select item and see where they are. 

Link to comment

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.

×
×
  • Create New...