Jump to content

Linking fixture data to external Excel work sheets


Recommended Posts

It should be possible to create at least a near live link using ODBC connectivity. But I don't think it is a casual, easy thing. If you need the ability it is going to take a fair amount of work and a huge amount of consistency on Both the VW and Excel sides. And probably an intermediate database to act as a buffer as I don't think Excel on the Mac does ODBC very well.

  • Like 1
Link to comment

Thanks Pat, A live link is exactly what i was thinking. I have a huge number of fixtures and it would be so much faster to be able to edit and modify parameters in a work sheet rather than a drawing with so many layers. No Excel does not do well at all on a mac, i have been trying for some time, however I do use a PC workstation in my office so i'll give i t a go on that.    

Link to comment

Are you talking about Spotlight fixtures, Lighting Devices?  Forget about ODBC; you'll either be committed or dead before you get it to work consistently with Excel.

It is possible export worksheets to Excel and import in from Excel, but you have to be careful with which fields you set up in a database worksheet since drop down don't paste values into multiple fixtures.  It can be done with most of the data that you probably care about.  Not live, but it travels back and forth.

The much better solution is to spring for Lightwright.  The initial expense will hurt, but the link with VW is live and you get a worksheet database designed for editing light plot data.

Link to comment

How about editing them in the VW Worksheet rather than trying to link to Excel. It operates a little differently, but has a lot of what you probably need and since it is internal to VW you don't have to worry about the external link.

 

Criteria in a Database row in a VW worksheet will let you easily get a subset of your "huge number of fixtures" so you can focus on what you need to work on.

Link to comment

It would be great if some sort of open gateway to excel or others would exist. Easier than obcd that is. 
 

lots of people have their own custom rack and patch sheets in excell. Lots of people don’t like to use Lightwright. 
 

My excel rack sheets are filled with numerous excell macros that I could never be able to reproduce in a VW worksheet. 
 

I’d be so happy if I wouldn’t  have to import and export anymore, but live update either of both ways. 

Link to comment

I wonder why this would not happen for a very long time? I am certain many users would happily benefit from this.  I would understand the argument that one must be careful about compromising data in a VW file. But if the function is off by default an a user is warned when turned on. Then why not? 
 

So what did Lightwright do to get it’s own very special data connection? Did he write it himself? Would an other developer be able or be allowed to use the same xml for data exchange? I mean the function already exists. Why not open it or even expand it to other record formats? Or give users the option to make it a csv. Excel is better at handeling that. 

Link to comment

The biggest reason it won't happen is because an Excel file can't have a direct link to an object in VW.

 

In a VW file you can reference objects relatively easily. In a database row you can Summarize them easily. Many of the VW worksheet functions (length, width, area, etc.) can't be run in an Excel file because it has no way to link to the object.

 

So in order to do anything close to a live link (other than ODBC), you would have to make sure every object in VW had a permanent id (UUID). Without that kind of a link, if you change the sort order in Excel when you take the data back to VW it is likely to be applied to the wrong object. Especially if you did any kind of real modifications in Excel.

 

A bad database is far worse than no database.

Link to comment

Actually every Lighting Device has a UUID.  There are so many other reasons.  No, I'm not going to start listing them now, but I'll give them to you one at a time when I'm not doing something else.

First.  You can use the XML file created when you tell VW Spotlight to use data exchange.  Open it up and see what you can make of it.  Then how are you going to get Excel to automatically read and distribute the data to Excel cells without you telling it to and telling it how?  If you have commands that accomplish this, how are they different from importing and exporting?  Let's start there.  

Link to comment
3 hours ago, Sam Jones said:

Let's start there.  


Nobody said it should work automagically without any setup of some kind. I do believe that once set up there would be a major benefit, especially in making updates to the data.
 

The initial import and export isn’t the biggest issue. the having to do it again to update both excel and drawing is where it gets annoying and where mistakes are easily made.  
 

I totally understand the way you look at possible problems in creating such a link to excel. And I very much appreciate how much in dept knowledge you have on the developer side of this. So please look at my comments as a user wish and not me trying to be a smart ass. 
 

I would believe you straight away if you said there are too many problems in creating such a link. But just a quick google learned me that in AutoCAD there is a function called ‘Data Linking’ they actually say this about it on their own website: “AutoCAD tables are great — however, in some cases, using Excel is just the better way to edit spreadsheet-like information. That’s where data linking comes in.”

 

I admit to have no AutoCAD knowledge at all. So maybe I am pointing at something that is not working in the way that we are discussing here. But at first glance it looks like they found a way to work around that long list of reasons you where referring to? 
 

Would that really not work in VW? 

Link to comment

I have no idea about AutoCAD's "Data Linking".  I am handicapped by not being sure what an "AutoCAD table" is.  In VW there is a worksheet and a database worksheet and they can occupy the same worksheet.  I will assume that the table they are talking about is a database of object records.  If they have implemented an easy user initiated link between an AutoCAD database and Excel, super.  I would love to know more without having to purchase and learn AutoCAD.

Are there "too many" problems creating the link to Excel in VW.  I don't know.  I just know that there are many problems, and it is hard to imagine that some heavy massaging of Excel is not required.  I'm tempted but I will not speculate, on what exactly is required by AutoCAD and Excel to make this work.  If you find out, let us know what they say the steps are, or some rough outline of same.

 

Link to comment

@Sam Jones I admit to have no knowledge about Auto-cad tables either. But when I read this text it apears that the whole setup is being done within A'cad. It appears like A'cad is able to read and write from and in .xls and .xlsx files.

 

https://autocadtips1.com/2014/07/24/creating-a-data-link-with-an-excel-table/

 

It reads to me like the Excel sheet is treated like an external reference, similar to how a reference is made in VW. If there is a change to the external data the user can see this and click on an update button. 

 

I could see a lot of benefits from such a function in VW. There must be disadvantages of course. But still would be amazingly useful.

Link to comment
On 2/3/2020 at 4:39 AM, Pat Stanford said:

The biggest reason it won't happen is because an Excel file can't have a direct link to an object in VW.

 

In a VW file you can reference objects relatively easily.

 

A bad database is far worse than no database.

 

So what if this function was coded in such a way that it would link an existing VW worksheet to an external spreadsheet application? The object link would then exist within the VW Worksheet. Not many people have the profound knowlegde of VW worksheets that you have. Referencing, editing, formatting and many more functions as such are just way more accessible in Excel than they are in VW.

 

Link to comment

The following is my opinion.

 

The main function of a worksheet in Excel is to be a user interface to display data about objects in the drawing. That could be direct information such as the size of the object, or the layer or class, or it could be data attached to the object using a record.field format, either the built-in fields of a PIO parameter record or a custom record a user has attached. Yes you can also do some calculations in the worksheet, but without the data coming from the objects in the drawing, it is not much more than a glorified typewriter.

 

If you pull that data to an external program (such as Excel) which does not have a way to directly access the data from the objects in a live mode, you effectively have two separate sets of the same data that are extremely difficult to keep in sync.  If you change data in Excel and before sending it back also make a change in VW, which one should win? Data synchronization is an extremely difficult problem.

 

I agree that the formatting abilities of Excel are much better. If you need that I would recommend exporting and formatting. Perhaps you can even find a way to use the Format Painter in Excel to allow you to very rapidly apply the formatting each time you export.

 

My hope is that the formatting abilities of worksheet will improve and the need to use Excel will decrease.

 

I hope to be proven wrong and someone comes up with a simple and reliable way to do 2-way communication with Excel & worksheets, but based on my experience with both programs I am not holding my breath.

 

But it never hurts to put in a detailed request in the Wish List forum.  It needs to detail exactly what you want to do and why. Something like "Make worksheets work with Excel" will be ignored as it does not define a problem.

 

/Opinion

 

Link to comment

As I said in my other post, a worksheet is really just a display method for data that is attached to the object. Unless Excel has access to the objects, it does not really do any good to link it to a worksheet. Remember you have to recalculate worksheets to get the current data. Even internally there is not a live link between the objects and the worksheet.

Link to comment
19 minutes ago, Pat Stanford said:

The following is my opinion.

 

 

/Opinion

 

 

Then I am curious what your opinion is on the live link that exists between VW and Lightwright? They solved the issues that you are addressing to. 

 

Could that be made to work with other apps? 

 

I agree the solution would be to make worksheets in VW better. Importing and exporting to an external appis also maintaining two sets of data. And as you said it is difficult to keep it in sync and cumbersome to either make the changes in two apps or to have to import back into vw. 

 

Would be so nice if we had the same types of formula's, conditional formatting, referencing to other sheets etc that are in excel and other spreadsheet apps.

 

I don't have the answers to how to solve this, just the wish as a user.

Edited by Sebastiaan
Link to comment
13 minutes ago, Pat Stanford said:

As I said in my other post, a worksheet is really just a display method for data that is attached to the object. Unless Excel has access to the objects, it does not really do any good to link it to a worksheet. Remember you have to recalculate worksheets to get the current data. Even internally there is not a live link between the objects and the worksheet.

 

Ok, but if I make a change in data in a worksheet now then my lighting instrument is updated also. What if that external link did the same? would that not work?

 

Link to comment

As to my opinion about the LW/VW link, it is great.

It needs to be turned on in each file.  From then on, each app knows it needs to read the XML for updates, and each app knows what the agreed upon XML structure is.  LW does data consistency checks and will instruct you on what to do if something is wonky.

I'm sure the method could be used with other apps, but it requires that both apps implement the switch to turn it on and know where to look for the XML file and what its structure is.  This is not something that could be implemented by VW alone.  At least not the way it is implemented with LW.  Do you think Microsoft would be willing to do something special with VW?

Link to comment
1 minute ago, Sam Jones said:

 Do you think Microsoft would be willing to do something special with VW?

 

I am not expecting them to.

 

I would like te be able to use a live data link with VWX.

 

OBCD is possible but so difficult to use that nobody uses it.

 

So OBCD is possible, Lightwright is possible. Why wouldn't something else be possible for other users that want to link external data? 

Link to comment
11 minutes ago, Sebastiaan said:

 

Ok, but if I make a change in data in a worksheet now then my lighting instrument is updated also. What if that external link did the same? would that not work?

 

The worksheet resides with VW so VW knows what the connection is.  Excel does not know and does not care.  AFAIK, the only way Excel can can exchange data with another file created and edited by another app is using ODBC.  That even includes with its own in house apps like WORD.  If ODBC is not being used then I think Excel would need to make a special effort to communicate with an app that didn't use ODBC.  As a result, the AutoCAD link is interesting.  I am guessing that they have simplified the user interface (UI) for creating the ODBC link.  It would be nice to know how they get handles to the Excel part of the set up.  It is all very curious and a bit intriguing.  If AutoCAD uses a method other than ODBC that would also be intriguing.  However, since AutoCAD has almost no tools for creating entertainment lighting, rigging, and sound drawings, I will never invest the time to investigate.

Link to comment

I always seem to be one conversation exchange behind.

Using any other method than ODBC for a live data connection to Excel or any other app would require the active participation of the other app, its company and engineers.  John McKernon is a lighting designer who desperately wanted to eliminate the import and export of data that was then required to communicate between LW and VW.  VW wanted to solidify its hegemony over entertainment production drawings, so LW and VW actively cooperated in developing the link.

I know you have invested a lot of time in developing your Excel templates, but if you want a live connection, use Lightwright.  It has a surprising array of formatting capabilities.  I expect that won't happen, so you are left with importing and exporting and putting in a feature request for VW to implement a feature like AutoCAD's Data Link.  That would be a useful feature for all the industries that use VW.  Before I got too excited about AutoCAD's Data Link feature, I would like to see it work with a real set of data, but there is no denying it is intriguing.

Link to comment
4 minutes ago, Sam Jones said:

I always seem to be one conversation exchange behind.

Using any other method than ODBC for a live data connection to Excel or any other app would require the active participation of the other app, its company and engineers.  John McKernon is a lighting designer who desperately wanted to eliminate the import and export of data that was then required to communicate between LW and VW.  VW wanted to solidify its hegemony over entertainment production drawings, so LW and VW actively cooperated in developing the link.

I know you have invested a lot of time in developing your Excel templates, but if you want a live connection, use Lightwright.  It has a surprising array of formatting capabilities.  I expect that won't happen, so you are left with importing and exporting and putting in a feature request for VW to implement a feature like AutoCAD's Data Link.  That would be a useful feature for all the industries that use VW.  Before I got too excited about AutoCAD's Data Link feature, I would like to see it work with a real set of data, but there is no denying it is intriguing.

 

 

I will make the feature request. Thank you @Sam Jones and @Pat Stanford for the interesting discussion. I learned from it 

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...