Read Tags in Excel From Studio 5000

[turbohamad]

Dear Friends;
I am trying to read the Tag Values in excel by using VBA. I write a code but was stuck on the following error:

Dim OPC Server1 As OPC Server

Excel can recognize the OPC server.

Image Attached for your reference.

which References or Library is required to run the code.

Regards

 

[turbohamad]

Here i mentioned that i have RSLink classic OEm 3.9 and windows OS 64 bit with Office 2016.

 

[turbohamad]

Respected members
After adding the reference of rsiauto.dll from Rockwell now I face an error can not create the object picture attached.

 

[janner_10]

As you have the OEM version, just paste the DDE link from the topic in Linx directly to an Excel cell.

 

[turbohamad]

Respected Members;

Actually, i want to know the solid solution for the following task:

1. Read the tag values from Rockwell PLC (Controllogix) and save that data on the remote MySQL database.

2. Read the values from the Database to excel at the central control room which is 500 KM away from the station.

Can anyone help me how to fix that task with a solid solution and which software are the guys you recommended?

right now I am trying to use Excel but I face the above errors with win 7 64 bit along with office 2016 64 bit.

I read that Excel is not the solid solution to accomplish the above task.

Regards

 

[FluxCap]

www.inductiveautomation.com

 

[widelto]

I used to use XLReporter and RSlinx professional fast and easy but expensive , now learning python and pylogix together with xlwings, openpyxl and xlswriter. It is going to take sometime but it is worth trying to and happy learning.

 

[turbohamad]

Hello members;
I do not about python how to code and how to run and how to dispatch the application to the client?

can anyone guide me where I start is there any IDE for python like Netbeans, visual studio to make applications?

is Python support to read the tags without IP address means through OPC server?

Regards

 

[widelto]

@turbohamad:
Python reads tags from PLCs thru a module called pylogix, I wrote this in another thread started by you some days ago.
Python for begginners can be use with an IDE by downloading Thonny.
Go to : https://thonny.org/

After thonny is installed together with python, you have to download pylogix.
Go to :https://pypi.org/project/pylogix/
or https://github.com/dmroeder/pylogix , Dustin is part of PLCs.net
You can also download pylogix thru Thonny by searching on pypi.
Dustin also has a discord, with some examples, and help.
As I mentioned the esasiest way is thru XLRporter and RSlogix professional but it is expensive, is up to you.

 

[drbitboy]

• The pylogix module is amazing: simple interface; it just works.
• https://github.com/drbitboy/pylogix_logger
  • Uses pylogix module to log PLC data, with timestamp, to ASCII (text or CSV), or to spreadsheet (eXcel, or Google) or to MariaDB/MySQL database.
  • This uses a command-line interface, but could be adapted to a GUI (e.g. PyQt).
  • I had a live demo going to the Google Sheet link in the readme; let me know if you would like me to start it up again.

Here is all that is needed to add a simple type of log (flat ASCII, in this case):

class PYLOGIX_LOGGER_FLAT_ASCII(PYLOGIX_LOGGER):

     def __init__(self,log_name,*args,fmtstr="{0} - {1} - {2}\n",**kwargs):

         self.log_name = log_name

         self.format = fmtstr.format

         super().__init__(*args,**kwargs)



     def __call__(self,*args,**kwargs):

         if self.changeds:                  ### Do nothing for no changes

             with open(self.log_name, "a") as fOut:

                 for changed in self.changeds:

                     fOut.write(self.format(*changed))

​

It took about 50 lines each to add the eXcel and MariaDB/MySQL loggers, and about 150 lines for the Google Sheet logger.

 

[andrewwei]

widelto any links to tutorial?

@turbohamad:
Python reads tags from PLCs thru a module called pylogix, I wrote this in another thread started by you some days ago.
Python for begginners can be use with an IDE by downloading Thonny.
Go to : https://thonny.org/

After thonny is installed together with python, you have to download pylogix.
Go to :https://pypi.org/project/pylogix/
or https://github.com/dmroeder/pylogix , Dustin is part of PLCs.net
You can also download pylogix thru Thonny by searching on pypi.
Dustin also has a discord, with some examples, and help.
As I mentioned the esasiest way is thru XLRporter and RSlogix professional but it is expensive, is up to you.

widelto, I downloaded Thonny and install pylogix on it. Do you have good tutorial of how to use it?
Thank you

 

[dmroeder]

widelto, I downloaded Thonny and install pylogix on it. Do you have good tutorial of how to use it?
Thank you

Here are examples

https://github.com/dmroeder/pylogix/tree/master/examples

 

[andrewwei]

Here are examples

https://github.com/dmroeder/pylogix/tree/master/examples

Thanks
You are so helpful.
I saw people have Ethernet module on Emulator, I've been trying for hours to get it.
Actually he was using Softlogix, not Emulator.

 

[drbitboy]

Here are a couple dozen lines of python to use pylogix to retrieve data over ~9s at ~1Hz from a PLC (in this case a Micro820), then write those data to a MariaDB/MySQL database. The last two sections, which could be extracted and placed in a separate script e.g. to run once a day, and delete any data older than a week from the database, retrieve those data from the database and write them to an eXcel workbook. The IP addresses of the PLC and the MariaDB/MySQL servers are arbitrary. Explanations of each group of statements can be found via Google. How to setup the MariaDB/MySQL server for such access is left as an exercise for the user; one approach is available at this link. The Micro8xx code is shown in the image below. After that, the beginning of the MariaDB/MySQL database table data; the full table is also attached.

### Cf. https://www.plctalk.net/qanda/showthread.php?t=132004

import sys

import time

import pylogix

import datetime

import pandas as pd

import MySQLdb as Mdb

### Parse command-line arguments:  PLC IP address; MariaDB/MySQL DB server IP address; DB username

plcip = (['192.168.1.160']+[a[8:] for a in sys.argv[1:] if a[:8]=='--plcip=']).pop()

ip = (['127.0.0.1']+[a[5:] for a in sys.argv[1:] if a[:5]=='--ip=']).pop()

usr = (['test_log']+[a[7:] for a in sys.argv[1:] if a[:7]=='--user=']).pop()

### Ensure DATABASE 'test_log' and TABLE 'log' exist on MariaDB/MySQL server

Mdb.connect(host=ip,user=usr).cursor().execute('CREATE DATABASE IF NOT EXISTS test_log')

cu = Mdb.connect(host=ip,user=usr,db='test_log',autocommit=True).cursor()

cu.execute('CREATE TABLE IF NOT EXISTS log (tim datetime NOT NULL,tag varchar(32) NOT NULL,val double NOT NULL,PRIMARY KEY tn (tim,tag))')

### Sample data from PLC at ~1Hz, write data to MariaDB/MySQL DATABASE.TABLE 'test_log.log'

with pylogix.PLC(plcip) as plc:

  plc.Micro800 = not ('--no-micro8xx' in sys.argv[1:])

  for i in range(10):

    time.sleep(1.001)

    dt = datetime.datetime.utcnow()

    cu.executemany('INSERT INTO log VALUES (%s,%s,%s)',[(dt,tag,plc.Read(tag).Value,) for tag in 'et_ms c.Year c.Month c.Day c.Hours c.Minutes c.Seconds'.split()])

### Retrieve data from 'test_log.log' into list of dicts

cu.execute('select * from log')

ads = [dict(ts=None)]

for ts,tag,val in cu:

  if ts.isoformat() != ads[-1]['ts']: ads.append(dict(ts=ts.isoformat()))

  ads[-1][tag] = val

cu.close()

### Write list of dicts to eXcel workbook

pd.DataFrame.from_records(ads[1:],index='ts').to_excel('test_log.xlsx')

​

​

$ mysql test_log -e "select * from log"

+---------------------+-----------+------+

| tim                 | tag       | val  |

+---------------------+-----------+------+

| 2022-02-09 20:37:28 | c.Day     |    9 |

| 2022-02-09 20:37:28 | c.Hours   |   15 |

| 2022-02-09 20:37:28 | c.Minutes |   38 |

| 2022-02-09 20:37:28 | c.Month   |    2 |

| 2022-02-09 20:37:28 | c.Seconds |   25 |

| 2022-02-09 20:37:28 | c.Year    | 2022 |

| 2022-02-09 20:37:28 | et_ms     |   46 |

| 2022-02-09 20:37:29 | c.Day     |    9 |

| 2022-02-09 20:37:29 | c.Hours   |   15 |

...

​

 

[Dayvieboy]

We got rid of our Excel sheets & DDE years ago
By putting a similar Excel looking screen on the GUI's

In Wonderware, Citect, RsView 32 & now Ignition
Create a excel looking template, repeat

Read/Write data, save recipes & sequences