Tim Harland Posted November 22, 2023 Share Posted November 22, 2023 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. 1 Quote Link to comment
Tom W. Posted November 22, 2023 Share Posted November 22, 2023 @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. Quote Link to comment
Vectorworks, Inc Employee Luka Stefanovic Posted November 23, 2023 Vectorworks, Inc Employee Share Posted November 23, 2023 @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! 1 Quote Link to comment
Tim Harland Posted November 23, 2023 Author Share Posted November 23, 2023 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. Quote Link to comment
Vectorworks, Inc Employee Luka Stefanovic Posted December 6, 2023 Vectorworks, Inc Employee Share Posted December 6, 2023 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 Quote Link to comment
Amanda McDermott Posted December 14, 2023 Share Posted December 14, 2023 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... Quote Link to comment
Tim Harland Posted December 18, 2023 Author Share Posted December 18, 2023 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. Quote Link to comment
Vectorworks, Inc Employee Luka Stefanovic Posted December 21, 2023 Vectorworks, Inc Employee Share Posted December 21, 2023 @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! Quote Link to comment
Amanda McDermott Posted December 21, 2023 Share Posted December 21, 2023 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! Quote Link to comment
Maury Jensen Posted March 23 Share Posted March 23 @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 1 Quote Link to comment
tspilman Posted March 27 Share Posted March 27 (edited) 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 Quote Link to comment
Vectorworks, Inc Employee msharon Posted March 29 Vectorworks, Inc Employee Share Posted March 29 I did a video a few weeks ago that may be of help. https://university.vectorworks.net/course/view.php?id=3195 1 Quote Link to comment
Popular Post Jesse Cogswell Posted March 29 Popular Post Share Posted March 29 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: Download the attached Link Worksheet to Record.zip file, but do not extract it. In Vectorworks, go to Tools - Plug-ins - Plug-in Manager. Click on the Third-party plug-ins tab. Click on the Install button. Navigate to the downloaded .zip file. Restart Vectorworks. Once restarted, you will need to add the plug-in to your Workspace. Go to Tools - Workspaces - Edit Current Workspace. In the Workspace Editor, click on the Menus tab. In the box on the left, find and expand the JNC category. In the box on the right, find a menu where you'd like to have the command, such as Tools. 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. 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 5 3 Quote Link to comment
Jeff Prince Posted March 30 Share Posted March 30 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… 2 Quote Link to comment
Popular Post Jesse Cogswell Posted May 8 Popular Post Share Posted May 8 Good morning, friends. I made a quick revision to the Link Worksheet to Record command linked above. I added a checkbox to Show Hidden Records. This lets you link a worksheet to the parametric records of Plug-in Objects, which should allow for you to more easily control parametric objects using worksheets. Download instructions are the same as listed above. Link Worksheet to Record.zip 3 3 Quote Link to comment
Jesse Cogswell Posted May 11 Share Posted May 11 (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. Installation remains the same as described above. Link Worksheet to Record.zip Edited May 11 by Jesse Cogswell 2 1 Quote Link to comment
J P Posted July 5 Share Posted July 5 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. 1 Quote Link to comment
Jesse Cogswell Posted July 5 Share Posted July 5 @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. 3 Quote Link to comment
J P Posted July 5 Share Posted July 5 @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. Quote Link to comment
Recommended Posts
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.