Using RSLinx Classic DDE/OPC to get data into Excel

[bodboy92]

Hi,

I saw the video as below,

- https://theautomationblog.com/get-a-...-using-rslinx/ (admin: updated link)

I would like to generate the Excel file by RSLinx Classic. Then, my another device can read this file.

Question:
1. Where is the Excel file location? Is it stored in PLC? or stored in PC? Because my device have to know where the file is, then it will read file then do next processing.
2. Should I put the Excel file with *.ACD together? or I can put into different path?

Thanks.

[Shawn Tierney]

Good morning @bodboy92,

In the article and video you reference, I have a PC connected to a PLC via a paid copy of RSLinx (not the free "Lite" version.)

Then on the PC, in an Excel file, I use RSLinx to read data from the PLC into the Excel file.

I also explain that RSLinx's help file has details on how to do this.

As far as automating Excel to save or create files using Visual Basic for Applications, I would suggest this book from Amazon: https://amzn.to/2ntVgrP

As far as where you save your ACD or Excel file, anywhere on your harddrive should be fine, and they don't need to be in the same place.

There are also products you can buy that do this for you, like XLReporter and Dream Report, and PLCs and HMIs that can log directly to a CSV file eliminating the need for Excel.

Best of luck!

Shawn Tierney,
Instructor at The Automation School

[bodboy92]

Hi Shawn,

Thanks for your information. I already followed your method and got the result.

For the CSV file, it doesn't update the data once I close the file. When I opened CSV file again, the function is gone, only show the value which is generated last time. It wouldn't update value when I change the value from PLC.

However, if I save the function in Excel file (*.xlsx), the data would be updated immediately. Is this result correct? If I want to update the data in CSV file, how can I do?

Thanks for sharing your information.

Regards,

bodbooy92

[Shawn Tierney]

Good morning @bodboy92,

In response to your statement, "For the CSV file, it doesn't update the data once I close the file" I'm confused - MS Excel can't update a file when it's not open and running?

If you review the MS Excel / RSLinx Classic examples I pointed out, you'll see they update when the connection from the Excel Spreadsheet to RSLinx is refreshed (like when you open the file in Excel) or when a macro is run.

That said, here's some options you may want to pursue further:

A - Learn how to use your PLC's or HMI's data loggging features (if any) to create updating CSV files

B - Learn how to use MSExcel and VBA with RSLinx Classic to create XLS files with PLC data

C - Buy a program that makes getting PLC data into a file / report easy (like XLReporter or Dream Report) without leaving Excel open and running.

Good luck!

Shawn Tierney,
Instructor at The Automation School

[bodboy92]

Hi Shawn,

I would like to generate the data to Excel file row by row when I toggle bit every time.

However, what I am doing is COPY the Data Table Address through the "Copy DDE/OPC Link", and PASTE the Data Table Address on the row of Excel, the whole processing is manual.
I would like to generate the date and save to Excel file row by row when I toggle bit.
How to paste the Data Table Address to the continue Row automatically?

For example,

Toggle Bit => Row 1 = 10/18/2019 07:56:22 AM
Toggle Bit => Row 2 = 10/19/2019 12:09:11 PM
Toggle Bit => Row 3 = 10/20/2019 09:25:56 PM
Toggle Bit => Row 4 = 10/21/2019 10:10:33 AM
Toggle Bit => Row 5 = 10/22/2019 11:19:41 PM

Thanks.

[Shawn Tierney]

Good morning @bodboy92,

You can easily use VBA scripting inside of Excel to accomplish this.

Well, first you need to know how to use VBA, and there are some good books here: https://amzn.to/35XMLqS

If it was me, I'd start by paste linking the trigger bit into a cell, and then when it changes run a macro that uses the code from the RSLinx help file to do a "read" of the data you want and then put it in the next available cell.

Or you could buy a product like XLReporter or Dream Report.

Good luck!

Shawn Tierney,
Instructor at www.TheAutomationSchool.com

[Catlett]

Which one of those two would you suggest, Shawn? Or are they very similar so it doesn't really matter which?

[Shawn Tierney]

Good afternoon @Catlett,

Both products are good, but also very different.

XLReporter is less expensive and focused on using Excel for reports, while Dream Report is more expensive but also more of a standalone product.

Also, both companies have great people that can help you decide if their products are right for you,

Hope this helps,

Shawn Tierney,
Instructor at https://TheAutomationSchool.com