Excel is a powerful application with many uses. It can be used as an inexpensive PLC interface when combined with a communication driver such as Kepware. Kepware can be downloaded from the company website and the demo version can be used for 2 hours at a time. A countdown timer is started when Kepware runs and after the 2 hour limit, it will have to be shutdown and restarted to reset the demo mode timer.
A license can be purchased online at any time to activate the software. The manufacturer website is accessed at http://www.kepware.com
I recently used an Excel spreadsheet with Kepware to monitor PLC registers for a number of PID controllers. The following picture shows the Spreadsheet on-line and in use.
Although this spreadsheet is intended only for monitoring it could also be used to write to registers in a PLC. In this example the PLC is a Siemens S7 but this can be done with any PLC supported by
The first step is to configure Kepware to access the needed tags. In my case a Kepware opf configuration file already existed on a server running QuickStudy and using Kepware to communicate to the Siemens S7 PLC. I simply saved a copy from the server and put the file on my computer which I wanted to use in a remote office area to monitor the process. The following picture shows the Kepware configuration and one of the tags I wanted to monitor is high-lighted.
Notice the demo mode timer shown in the top information bar.
The following string was entered into a cell in Excel and used to display the contents of the desired register:
RFL2 is the channel name defined in Kepware
APC_PLC is the device name defined in Kepware
TIC100_RSP is the tag name used in Kepware
Once the cell is populated with the data from Kepware the cell can be referenced for a calculation, used for a bar chart, or other spreadsheet functions.
To write data to the PLC from Excel a macro is used to execute a DDE Poke.
For more detailed information a document is available from Kepware entitled KTAN90029_KepserverEx_and_Excel.pdf that can be downloaded from their website at http://www.kepware.com/Support_Center/SupportDocuments/KTAN90029_KEPServerEX_and_Excel.pdf
Doug Deiterman – Senior Controls Engineer