XLM (Excel Macro) vs. VBA for data logging application

Consider reviewing this link, which is a bit of a good thing, if you continue using XLM. Apparently Microsoft is committed to supporting XLM for the time being.

XLM + AMSI: New runtime defense against Excel 4.0 macro malware

gclshortt said:

Hi AlfredoQuintero
Mitsubishi has Modbus communication protocol on most of their PLCs.
It was mentioned before that AdvancedHMI. It has Modbus TCP (Ethernet) and RTU (Serial) communication drivers.
https://www.advancedhmi.com/
You would then easily store the information in a database.
Excel would then read the information.

Another way to do this is to use Node-RED.
https://nodered.org/
I am currently doing a series on data collection using Modbus TCP and RTU using node-red. This will store information in a SQL database and then show how to read the logged data using a spreadsheet.
https://accautomation.ca/series/node-red-iot-enabling-software/

Regards,

Click to expand...

Garry, thanks for your comments. Will look into this. The machine is an existing one and the PLC only supports Melsec. Also the network is local, not connected to the company LAN. So I need to stick with MX SHeet and Excel for this project, but will look inot this nodered approach for future projects.

Mispeld said:

Consider reviewing this link, which is a bit of a good thing, if you continue using XLM. Apparently Microsoft is committed to supporting XLM for the time being.

XLM + AMSI: New runtime defense against Excel 4.0 macro malware

Click to expand...

Thank you very much for this article Mispeld. I have read it and duly noted it.

Nova5 said:

Has XLReporter been around that long? I've been working with it a few months and it feels like a beta product with the number of bugs i've found and hacky ways i've had to do things.

Click to expand...

Nova5, I am intrigued by your comment. Would love if possible to read further from you as well as from widelto. I have an opportunity with the customer I am working for and I think when they start using the application I will be delivering in the next few months, they may want to have the same function for many other very old machines that sill have decades of operational time. I was going to give a look at many of the recommendations I have had in this post, one of which is XLReporter. The runtime license for this software is less than $1K. An OPC server is needed for this solution. After Nova5 warning I am hesitant. Since the factory has in some lines PLC from different vendors I will be using an OPC server which is needed for XLReporter anyways as the data source. Since the Excel macros that I am developing can be reused I am considering using an add-in for Excel which is an OPC client https://ipuerto.co.jp/product/#ua (sorry I think it is only in Japanese). What is is Nova5 has found needed hacky ways to workaround?

Probably not what you will end up doing but Kepware OPC server has a plugin that will log directly to a Database, I used this to log data from a range of PLC's to our SQL server, Excel then was used to produce the reports. I purchased what they call the factory suite which contained most of the common drivers and the logging function. there was no need for any other software just the OPC server, SQL was already there (could use the free version SQL Server Express) this I think is up to 10gb which should be enough for quite a lot of logging, then you can export & delete old tables if the DB gets close.

Hi parky, that would be the ideal solution. Other OPC server such as Softing's (with which I normally work with) also have this SQL server logging capabilities. But here in Japan in some industries at least, the factory floor people do not have access to these resources and IT people do not help much. As I said, the network for this PLC is completely separate from the company LAN. The engineer has to come to the shop floor and connect to the DIN-rail mount PC through a cable laid between the switch and the control room to get the Excel files. I even had to setup a DHCP server on this PC so when the engineer brings his laptop to retrieve the excel files he does not need to statically set IP address.

You could download SQL Server Express & have it on the local PC, I did all my trials on a laptop so as not to offend our IT Dept. only moved the database & OPC server over when they were satisfied But kept the laptop running for a couple of years as I also had a second license for the OPC server. I also found VBA easy to get the data into Excel.

Thanks for this advice. Will keep it in mind for a future project.