excel shared workbook access

[Rdnfs2012]

Hi,

I know writing to excel with Labview is well documented, however I didn't find anything to my specific issue.

The ultimate goal is to have a central computer with and open .xls file (in excel) show realtime (or close to) data updates from multiple sources. I was originally thinking to acomplish this with a 'shared workbook' in excel which allows simultaneus updates from multiple users. That way, each of 10 remote PC's could all save the file, and I would set the shared workgroup update rate to 5 mins or so thus giving me near realtime data updates.

I'm not sure the best way to do this, I'm not confident the activex method would work in this case.

The excel file in question must be an .xls because it was some functions that we have to keep that break in csv/text format.

The only other method I could think of is for each pc to save updated values to its own .csv file on the central computer then write a vba script to periodically update data from there

Any suggestions?

[SteveE]

Hi, I think you are going to have trouble using excel for this. When Excel is open it will not notice if you have updated the data in the file.

You will have to force Excel to refresh and reread the file, this will not be elegant. You are likely to have file locking issues. Had you considered using a Labview frontend to monitor the data, refreshing every 5 mins and then have the other Labview apps updating the data. Another option you could use database connectivity, this naturally gives the ability to update the data from multiple sources, then you could display with Excel using ODBC connectivity into the database. I should warn you that ODBC can be very difficult to get working although when its running its generally OK.

Steve

[Rdnfs2012]

I was imagining having file locking issues and update issues as well, which is why I was toying with the idea of a shared workbook. Using a shared workbook allows multiple users to modify ONE excel file, and you can set the update interval, i.e. 5 mins. I'm not sure if the accessing computers need to open the file in excel, though. Guess I'll have to experiment.

It sounds like I might need to go with my other option-having labview funnel data into a text/deliminated file and then script excel to update the file occasionally. Or like you mentioned use labview on the frontend to deal with handling the data. Perhaps I'll just create a labview GUI that looks identical to the standardized excel file we have to display 

[SteveE]

What version of Labview are you using? What version of Excel ?

I am a Labview 2011 user with Office 2007. Having just been reading the "whats new in 2013" I realised that writing to .xlsx files is now an option. It would be an interesting experiment to have Labview writing to the same file as several PCs using a shared workbook as you originally proposed.

If you do try this please post the results it could be interesting for other users.

[Rdnfs2012]

I'm using labview 2012, and ideally will be able to get office 2010 for the frontend so I can try writing to .xlsx. The project is still being considered so it may not happen, but I plan to play around with a shared workbook first.

I think the question is if computer hosting the shared workbook looks for the incoming changes to be from remote excel locations, or just keeps the file unlocked so that all users to do basic inputs (i know things like charts dont work on shared workbooks) and periodically updates the data from the file to the open document.

I'll post back when I get to this, could be interesting to play around with

[Jirav]

Hello,

Did you find any solution to the issue you were facing ? I am also facing same issue. Please suggest if you find anything.

[Bob_Schor]

A couple of comments.

• LabVIEW 2012 (and possibly earlier, as well) Report Generation Toolkit has no trouble reading/writing .xlsx files (provided the companion version of Excel on the same PC supports this format, i.e. probably not Office 2003).
• Recall that using the RGT requires that LabVIEW "hook into" a copy on Excel on the same PC.  I don't see an easy way to implement a "Shared Workbook".
• Having said that, you could do something like the following:  Have the Excel file on a Share, along with a "Lock" file.  If you want to access the file (to do a quick read/modify/write), see if the Lock file exists -- if it does, someone has "locked" the file, and you'll have to wait.  Otherwise, you write the lock file (perhaps pointing back to yourself, so people can tell who has locked the file), then use the RGT to open, modify, save, and discard (meaning "close Excel") the Report, then delete the Lock.
• You'll need to think how to handle multiple people wanting the file at the same time -- is it First Come, First Served, or First Request when Free, or some other access algorithm?

Bob Schor