Red Lion G307K to Excel - options?

I have an upcoming project where I will need to transfer values from a G307K200 to an Excel spreadsheet on a nearby PC. I've worked with both products extensively but never had to marry the two. I have some experience with VBA which I assume will be required on the Excel side. Using an Ethernet connection would be preferred but I'm not opposed to serial if there's some advantage. Basically I will be transferring arrays of test results... the user will enter some identifying information in the spreadsheet, hit a button, and the corresponding array data will be looked up and transferred from the G307 to the proper cells in Excel. Basic Internet research has turned up ways of doing this with OPC and Modbus TCP, which usually involve purchased add-ons for Excel. (I'm not opposed to this if it provides a robust solution.)

The underlying challenge here is that this will be part of an OEM machine where the end user may not be very "techie," in other words the Excel interface needs to be reliable and packaged in such a way that it's not too difficult for the average user to install and use.

I have zero experience with OPC so I'm curious to hear opinions on that, but I'm open to any and all solutions.

A number of users have used AdvancedHMI to exchange data with a Red Lion using the ModbusTCP driver. Once the data is in AdvancedHMI, you can use a library named EPPlus that will read/write excel files. There is a sample project showing how to use Excel files with the CLX driver, you could use this as a reference, but use the ModbusTCP driver:

https://sourceforge.net/projects/advancedhmi/files/advancedhmi/3.5/SampleProjects/

The sample project is not based on the latest version. To start from scratch with the latest project:

- Download and extract the latest version of AdvancedHMI
- Open in Visual Studio
- Build->Build Solution
- Project->Manage Nuget Packages and search for EPPlus
- After it finds the EPPlus library, select it and click Install
- In Solution Explorer, double click MainForm.vb to open the designer
- From the Toolbox, add a ModbusTCP driver to the form
- Set the IPAddress to that of the Red Lion
- From the Toolbox, add a Button to the form
- Double click the button to get back to the code
- Enter code similar to this:

Now you can run the application, click the button and it should create an Excel file with the point of data from the Red Lion.


A completed AdvancedHMI solution is very easy to deploy. Simply copy the project to the target PC and make a shortcut to AdvancedHMI\bin\debug
No installation nor licensing headaches to deal with.

Archie, thanks for the detailed response. To pursue this solution it sounds like I need Visual Studio... Will it work with the free version? (Formerly Express but now called Community I think.)

kolyur said:

Archie, thanks for the detailed response. To pursue this solution it sounds like I need Visual Studio... Will it work with the free version? (Formerly Express but now called Community I think.)

Click to expand...

Yes, it will work with the free Community Edition. If you use version 2015 and you only see a few drivers in the Toolbox (there should be 14 drivers), I do recommend using version 2013 because 2015 has a minor bug that will not always show all of the drivers in the Toolbox. Another work around is to add any one of the drivers that do show up, exit VS, then re-open.

Microsoft claims this quirk will be fixed in the next version of VS.