Excel Add-In to log data from OPC

Excel Add-Ins to log from OPC Server
Does anyone know of any Excel Add-Ins that will allow me to log data from a OPC Server. I am looking for something easy
and not to expensive.I am logging from a AB Contrologix.


Thanks in advance,

Try OPCEx by Resolvica or write your own VB code?

www.resolvica.com/

You might try a company named National Instruments and a product named Measure.

Coachman said:

Excel Add-Ins to log from OPC Server
Does anyone know of any Excel Add-Ins that will allow me to log data from a OPC Server. I am looking for something easy
and not to expensive.I am logging from a AB Contrologix.

Click to expand...

Go to www.opcfoundation.org then look for companies or products and you will find several companies with the product you are requesting, you can download samples and trial then you can decide on price and performance.

Try this... its cheap and dirty using Visual Basic. I modified it from something someone else did, but I can't remember where I got it. Just add your tagname to the formulas of row 3, and put a label in row 2. Add as many as you need until excel crashes.

Improved version of the excel sheet.
Just enter topic name and the tag name.
(sheet password is blank)

Thanks for the response. What version of RS Linx do I need. I have
RS Linx Classic for RS View.

Any version above lite.... lite doesn't allow you a dde/opc topic

I tried your excell sheet but when it ask to start linxs it says that the program or one of its components is damaged or missing. Any ideas why?

If you have done DDE calls before, you will recognize that you need to do some additional work on the spreadsheet before it will work.


I was playing around with it, had to change the time/date tags to what I was using, had to put the OPC topic in the correct field. There was something else, but I don't remember right now. Play with it, it is pretty cool.

Could explain these directions just a little more:

-Enable Macros and Update Sheet

-Enter the name of the Parameter you are monitoring in Row 2.
What is the Parameter ? It is not the actual tag?

-Where AI_Tagname is the PLC Tag you wish to log.
Where is that located ?

Thanks in advance,

- First start RSLinx Pro or Gateway. (Lite won't work)

- Create a DDE/OPC topic. Remember the name of that topic.

- Point the Topic to the PLC processor that you wish to extract data from.

- Open the excel datalogger spreadsheet. When Excel opens it asks if you want to enable macros (security step) say YES.

- Then excel will ask you to update the data from the external source. Say Yes.

- Enter your topic name from RSLinx that you created in step 2, in cell F1.

- In Row 2 from C column on... enter the ControlLogix tagname you wish to log. Or PLC legacy address. "Tagname" or "N7:0"

- hit START ... and watch the data roll in until excel fills up and crashes.

If that doesn't work then you've got some weird windows or MS office configuration problem.

Oh, as Oakley said... you may need to change the PLC tags that gathered the time and date stamp. I was using "SYS_TimeAndDate" as my 7 word array that gathered time and date from the GSV command.

no offense killer...

I feel compelled to mention that Excel is a horrible data logging tool - period.

It's a nifty trick for you, the technical guy, to do some troubleshooting. I'll even give kudos to the author of the spreadsheet. However, do yourself a favor - don't show it to production/management. You'd give them nothing but headaches.

I might as well add some justification since someone is bound to challenge my last post. I might also mention that MS Excel would battle for my #1 spot of top 10 applications of all time.

Excel DDE->PLC might be ok for viewing data NOT logging data. Ideally, data should be logged to a database. The next tier would be applications that write separate .CSV files, usually daily, to be opened externally with Excel. This isn't great since you have to deal with concurrent write locks, it's hard to analyze separate files, etc, but is workable.

Having Excel log the data to itself is horrible for many reasons including:

1. Excel has a 32k row limitation. Your application will likely crash before you reach this point as killer mentioned. Everything is memory resident, which is bad.
2. Storing/archiving/cleaning old data in this format isn't viable.
3. You can't set this up to work automatically in a consistent and reliable manner. You'll have issues with: RSLinx needing startup delays, running as a service versus application (Excel doesn't run as service), automatically running macros, etc. I've set this up for several customers where it worked properly after rebooting, then would mysteriously stop after several months.
4. DDE and to some extent VBA are deprecated technologies that are troublesome and painful to work with.
5. You can't share your data across the network effectively.
6. You can't easily accommodate changes, things like scaling data, etc.

That's all I can come up with off the top of my head, but there are more reasons. You're presenting a valuable feature that your users demand in a manner that's not supportable or expandable. IMO it's professionally irresponsible to use in a data logging capacity - or any other way beyond your own troubleshooting.

edit - I looked into OPCEx (recommended by Moggie) a bit. They do things like using Excel for reporting/alarming/updating values etc. They do not log values to excel - for good reason. That application will log data to a database, though.

surferb said:

I might as well add some justification since someone is bound to challenge my last post. I might also mention that MS Excel would battle for my #1 spot of top 10 applications of all time.

Excel DDE->PLC might be ok for viewing data NOT logging data. Ideally, data should be logged to a database. The next tier would be applications that write separate .CSV files, usually daily, to be opened externally with Excel. This isn't great since you have to deal with concurrent write locks, it's hard to analyze separate files, etc, but is workable.

Having Excel log the data to itself is horrible for many reasons including:

1. Excel has a 32k row limitation. Your application will likely crash before you reach this point as killer mentioned. Everything is memory resident, which is bad.
2. Storing/archiving/cleaning old data in this format isn't viable.
3. You can't set this up to work automatically in a consistent and reliable manner. You'll have issues with: RSLinx needing startup delays, running as a service versus application (Excel doesn't run as service), automatically running macros, etc. I've set this up for several customers where it worked properly after rebooting, then would mysteriously stop after several months.
4. DDE and to some extent VBA are deprecated technologies that are troublesome and painful to work with.
5. You can't share your data across the network effectively.
6. You can't easily accommodate changes, things like scaling data, etc.

That's all I can come up with off the top of my head, but there are more reasons. You're presenting a valuable feature that your users demand in a manner that's not supportable or expandable. IMO it's professionally irresponsible to use in a data logging capacity - or any other way beyond your own troubleshooting.

edit - I looked into OPCEx (recommended by Moggie) a bit. They do things like using Excel for reporting/alarming/updating values etc. They do not log values to excel - for good reason. That application will log data to a database, though.

Click to expand...

And, if your playing with netdde it won't work in Vista, at all.