User Tag List

Results 1 to 8 of 8

Thread: Using RSLinx Classic DDE/OPC to get data into Excel

  1. #1
    Member bodboy92's Avatar
    Join Date
    Sep 2019
    Posts
    4
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)
    Thanks Thanks Given 
    0
    Thanks Thanks Received 
    0
    Thanked in
    0 Posts

    Using RSLinx Classic DDE/OPC to get data into Excel

    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.

  2. #2
    Administrator Shawn Tierney's Avatar
    Join Date
    Sep 2016
    Location
    Berkshires, MA
    Posts
    2,344
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks Thanks Given 
    24
    Thanks Thanks Received 
    6
    Thanked in
    6 Posts
    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
    Looking for affordable automation training? If you are, check out my courses at TheAutomationSchool.com!

  3. #3
    Member bodboy92's Avatar
    Join Date
    Sep 2019
    Posts
    4
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)
    Thanks Thanks Given 
    0
    Thanks Thanks Received 
    0
    Thanked in
    0 Posts
    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

  4. #4
    Administrator Shawn Tierney's Avatar
    Join Date
    Sep 2016
    Location
    Berkshires, MA
    Posts
    2,344
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks Thanks Given 
    24
    Thanks Thanks Received 
    6
    Thanked in
    6 Posts
    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
    Looking for affordable automation training? If you are, check out my courses at TheAutomationSchool.com!

  5. #5
    Member bodboy92's Avatar
    Join Date
    Sep 2019
    Posts
    4
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)
    Thanks Thanks Given 
    0
    Thanks Thanks Received 
    0
    Thanked in
    0 Posts
    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.

  6. #6
    Administrator Shawn Tierney's Avatar
    Join Date
    Sep 2016
    Location
    Berkshires, MA
    Posts
    2,344
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks Thanks Given 
    24
    Thanks Thanks Received 
    6
    Thanked in
    6 Posts
    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
    Looking for affordable automation training? If you are, check out my courses at TheAutomationSchool.com!

  7. #7
    Member Catlett's Avatar
    Join Date
    Nov 2019
    Posts
    1
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Thanks Thanks Given 
    0
    Thanks Thanks Received 
    0
    Thanked in
    0 Posts
    Which one of those two would you suggest, Shawn? Or are they very similar so it doesn't really matter which?

  8. #8
    Administrator Shawn Tierney's Avatar
    Join Date
    Sep 2016
    Location
    Berkshires, MA
    Posts
    2,344
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks Thanks Given 
    24
    Thanks Thanks Received 
    6
    Thanked in
    6 Posts
    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
    Looking for affordable automation training? If you are, check out my courses at TheAutomationSchool.com!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •