DDE to Excel on network

I have successfully created a DDE project that will dump live data into an Excel spreadsheet. Is there a way for multiple people on the LAN to view the document with live data coming in? Will a program written for SQL Server or Visual Basic do this?

Thanks for your time.

Anybody want to take a shot at this?

Not really my area but what about an HTML or XML frontend ?

Thanks for your reply Tragic.

I tried to Use an Excel spreadsheet that was saved as an HTML and it did not work.

Lol, I hate to burst your bubble, but here goes. You've taken the next step in a progression that outgrows the architecture. When I constantly recommend against using Excel as a datalogging application, it's not because it's hard to set up, it's because it's a bad idea in terms of scalability. Let's count the reasons:
1. There's a 65k row limit. You wouldn't want to go much bigger since everything is running in memory. This will slow down.
1a. Unless you want to cycle or delete logged data, this means that you have to separate files. Besides the obvious point of not being able to query a spreadsheet, data mining is nearly impossible with a bunch of separate text files.
2. Excel must be running and probably running a Macro. That means that a user must be logged into Windows (can be auto), Excel must be running, and the process started. Very clunky compared to a Windows service.
3. Maintenance sucks. Large changing text files are very prone to file corruption. Tracking, backing up, and maintaining lots of large text files doesn't work well.
4. Concurrent access doesn't work well. File locks prevent writes - reads for files that aren't being modified are OK.
5. Concurrent updates are nasty. Best case scenario (in terms of confliction/traffic) each user has their own separate copy of the spreadsheet running. Now each user is slamming the PLC with the same DDE requests.

As you suggested SQL Server is a much better idea. Any SQL database is designed to support:
1. concurrent traffic
2. large amounts of data
3. queries against all the data - this leads into dynamic reporting and graphing.
4. managability in terms of backups and maintenance

In fact, you'll probably want to use Excel as a tool to work on snapshots of the data from the SQL database. It's worth mentioning that the "Enterprise" (expensive) or "Historian" (meant for datalogging) versions of all the major vendors use SQL Databases - many favor SQL Server because they're in bed with MS in some way that will be reflected in the price tag.

Vendors "distributed" package aim to provide concurrent users access to data. Key points here are:
1. Centralized communication - you don't want each client to communicate with devices directly. This doesn't scale - PLCs can't handle that.
2. Centralized project configuration - you want to be able to change your project, what you're logging, etc in one place and have that reflect itself on all clients. The better packages automatically push software version upgrades to clients.
3. Ease of client installation and configuration. An "install-less" web page would be the easiest, the most complicated would require a program install that had clunky server/PLC configuration, project configuration, etc. Obviously, the whole spectrum exists here.

Will a program written in Visual Basic do this? It could do it perfectly or not at all - that depends on how you write the program. Writing a solid distributed app from scratch is challenging and time consuming at best - that's an understatement! There are standard technologies to be leveraged.

For example, "Internet" technologies, I use the term loosely, were designed to be lightweight and allow concurrent access. This can include: server side scripting web pages (ASP, PHP, Cold Fusion, Perl, Java Servlets, etc), client side web apps (Java, Flash, ActiveX, etc), or whatever you want. The bottom line is that they work over TCP/IP, typically Ethernet, use standard protocols (particularly for security), and utilize SQL databases. They should also utilize "modern" techniques and standards - XML, web services, SSL, be "firewall friendly" (work over a small definable port range), etc.

Internet technologies are really the pinnacle of lightweight distributed applications. The more you deviate from this model (particularly in a custom code approach), the more you'll be "re-writing the wheel"

insert shameless, but highly relevant advertising...

Inductive Automation specializes in this sort of thing. I'd request a live web demo for more info. FactorySQL would be used for datalogging to any SQL database, FactoryPMI is a Java web based HMI client. You also can launch an online example.

Kyle Grathwol said:

I have successfully created a DDE project that will dump live data into an Excel spreadsheet. Is there a way for multiple people on the LAN to view the document with live data coming in? Will a program written for SQL Server or Visual Basic do this?

Thanks for your time.

Click to expand...

Our system administrator has been developing WW Information Server as well as SQL Server Reporting Services. Note we have WW InSql Historian.

Now our web services have been recognized by our corporate friends. We have desktop historical information (even can be considered as live). We even have the ability to integrate information into SharePoint (how our web services are accessed).

Anyway, I agree with Surferb that using Excel is not a good idea. Now granted, there are certain instances where Excel may be an easy solution in the near term (I do this from time to time), but SQL tools are a much more robust deployable solution.

Kyle Grathwol said:

I have successfully created a DDE project that will dump live data into an Excel spreadsheet. Is there a way for multiple people on the LAN to view the document with live data coming in? Will a program written for SQL Server or Visual Basic do this?

Thanks for your time.

Click to expand...

IF you have a fairly small application, all you need is an individual copy for each machine, with the correct version of RSLinx. Remember any changes need to be made in all locations.

Surferb is correct when it comes to storing, sorting and maintaining the data. I have seen this done, and done it myself many times. Just try to limit the number of PC's and keep it super simple. If you need to store data, think database. I stored data for up to a week at a time in the PLC, then dumped it and started over. In other cases I stored it for only one shift or one day. We never had more than three copies running at any given time. Loss of data was no major concern. We just wanted to monitor the current situation and watch short term trends.

Take a good look at what you expect to get from the data, if you want to keep it, then think SQL.

I was exaggerating my point a bit - thanks plchacker for putting in due applicability for the approach (although I'm guilty of doing it before, but despise it). Excel is a world class application for spreadsheets. Your enterprise solution will undoubtedly utilize it. Using it as a free cheap way to get data to and from your PLC via DDE is fine. Using it as a short term data logger is hacky, but ok - just don't plan on growing that. SQL databases are king when it comes to data storage and concurrent access.

Oakley uses Wonderware's various packages that ride on their custom versions of SQL Server. That's a solid way to go, particularly if your enterprise is MS based. Things like Sharepoint integration are huge heavy hitters in terms of functionality, scalability, cost, and management. Sharepoint, for example, is an ideal portal for hundreds, if not thousands, of collaborating users.

Also, as Oakley pointed out - becoming friends with IT is a good thing. We're talking distributed network access, their turf. The more you want to interconnect sites, provide remote access, and integrate with other Enterprise applications, the more relevant this becomes.

WOW. Thanks guys for your advice. I appreciate your time very much. I will give you more details on the specifics of this project.

Happy Thanksgiving.

I recently used excell to log some integers from an SLC 500 works great, but the others are correct about several points my biggest beef is that it has to be constantly monitored, clearing logs, making sure it's running, printing.

On the bright side the Plant Manager asked for a report every morning and I said I could do it, and it does work for that

I made it in excell and save it as html (autosave add on every 10 min.)in the computers web folder turned on Ils and anybody on the companies network can type the name of the computer (PlantLog)and up pops the sheet refreshed every ten minutes.

It works but a little quirky and after about a week needs to be rebooted, I wished I knew how to auto save using VB though since I have a hard time remembering to click the autosave.

Clint

Kyle - Happy Thanksgiving! I'd love to hear specifics on your project.

CJones - This may be helpful: Using Events with Microsoft Excel Objects. The approach scares me, but probably no worse than an autosaving scheme that requires regular reboots. Just keep an eye on it.

You will probably want to save your Excel Spreadsheet every x number of times you log (possibly better than every 10 minutes) with the following:
ActiveWorkbook.Save
I'd recommend a network path to save your HTML document:
ActiveWorkbook.SaveAs "c:\test.html", xlHtml

It probably wouldn't be too terrible to automatically save to a different daily file with a name based on the date. You could also create a dynamic web page using ASP on IIS to generate an index to the daily linked HTML dumps. You probably won't be able to take it much farther than that (Graphs, reports, data mining/analysis, alarming, realtime status/control, integration with any other systems, etc). Also expect to babysit this magical spreadsheet. If that satisfies your needs, great!

BTW Kyle, vieing HTML from a web server (IIS) is a great way for many concurrent users to view the same data. Go with your instinct on standard technologies - I'm sure you know this already.

CJones said:

I recently used excell to log some integers from an SLC 500 works great, but the others are correct about several points my biggest beef is that it has to be constantly monitored, clearing logs, making sure it's running, printing.

On the bright side the Plant Manager asked for a report every morning and I said I could do it, and it does work for that

I made it in excell and save it as html (autosave add on every 10 min.)in the computers web folder turned on Ils and anybody on the companies network can type the name of the computer (PlantLog)and up pops the sheet refreshed every ten minutes.

It works but a little quirky and after about a week needs to be rebooted, I wished I knew how to auto save using VB though since I have a hard time remembering to click the autosave.

Clint

Click to expand...

Hi Surferb,

Thanks for your interest in my project.

I have to take data from 6 machines that are all in different areas of the state and combine it into one document, HTML file, or whatever will work. It has to be able to be viewed by several different people at any given time and the data within the document has to be live. They are Rockwell MicroLogix1200 processors running RSLinx Gateway.

I am pretty much self taught in this area. I just finished up a Visual Basic class so I could write a program to solve a different problem with this project. Now it appears as though I should learn SQL Server to write a program to collect this data. Will the data be live when pulling from SQL Server?

Should I write a VB app. to pull the data in from the SQL database or use a webpage? I would guess the webpage would be the most efficient.

In summary, 6 different machines, in different areas of the state all sending data to one location that can be viewed by several people with live data.

I think I would check into the Red Lion Data Staion. I think it will do what you are wanting.

However if you did want to do it on your own I would do it in ASP. If you can do VB then you wont have any trouble with ASP. It is just like VB but for making web pages and connecting to databases.

Hi Charles, thanks for your reply.

I will definitely look into ASP.

Look at NetDDE.