Control Logics 5000 - DDE/OPC to Excel (Live Data)

[VastSky]

Great stuff Archie!!

I have installed AdvancedHMIBetaV397e and have been successful in adding a digital display, pointing it to a PLC via EthernetIPforCLXCom1 and having it display a live PLC value.

I am an E&I tech that frequently does transmitter calibrations.

Our original record keeping system here was to fill out paper calibration records. I transitioned this to using an Excel spreadsheet that pulled in real time live transmitter values from the PLC via RSLinx. Using Excel is really slick for my needs as I can simply copy the live value, then paste value only to create a calibration record. No more paper calibration sheets!!

I am hoping I can substitute Advanced HMI's EthernetIPforCLXCom1 comm driver for RSLinx to allow me to pull real time live data into Excel.

I have searched and read many Excel/Advanced HMI topics, but none seem to be describing how to accomplish what I need. My skill level when it comes to VB is pretty basic.

The way I make this work using RSLinx and Excel:

1) In RSLinx browse to the desired PLC processor, right click and configure new DDE/OPC Topic and give it a name.

2) In Excel accept warnings and provide permissions for the scary things to run. In a cell define the path into the PLC address/Tag via RSLinx DDE/OPC Topic name:

Code: [Select]

=rslinx|DDE/OPC Topic Name!'PLC Address/Tag' I usually export the PLC tag database and harvest the tag/address needed.

3) Set up calibrations equipment at transmitters, connect laptop to the PLC network, fire up Excel spreadsheet set up to use RSLinx and watch the transmitter's output right through to the PLC. This is so great because you can calibrate end to end alone without having a second person relay these values over a radio.

I think the closest I came to finding a match for what I seek is http://www.plctalk.net/qanda/showthread.php?t=78379&page=2 and I can follow this pretty well, but I think this is a similar application and not exactly what I hope to do.

This is a pretty incredible platform Archie! Once I get this Excel/EthernetIPforCLXCom1 working and my annual calibrations done, I hope to promote Advanced HMI here as a replacement for RSView which is is getting a bit old.

Ed 

[Archie]

There are 2 ways you can go about this. Both methods will require a little bit of code writing.

1) Show the live values on an AdvancedHMI form, then use a button to take a snapshot and write them to an Excel file.
2) Use Excel Automation to open Excel and transfer values.

My preference is the first method because it is much faster and can run on a PC without Excel installed. The second method is kind of clunky and uses some outdated software tools, but it does actually open Excel and fill in the values as you watch.

Not too long ago I developed an application that did something similar. The user would start the application, it would copy an Excel file that was used as a template. Then it would retrieve PLC values, put them in the spreadsheet, the save the file. The user could then open the file and add their notes.

The first method makes use of a software tool named EPPlus. The second is implemented similar to this:

http://support.microsoft.com/kb/301982

Let me know which method sounds better and I can give you a more detailed example of how to do it.

[VastSky]

Hey Archie,

Real time live values directly in Excel work well for me. The number of points is about thirty or less. I would use this at calibration time to observe the PV that I present to the transmitter, Ctrl-C it then paste value in a cell to record the calibration.

Showing the live values on an AdvancedHMI form, then using a button to take a snapshot and write them to an Excel file sounds good too, but I only examine a single transmitter and thus single tag/address at a time so I would have to grasp how this may work.

We are have a mix of processors that are on our network - PLC5 and PLC5000. I have had success with the PLC500 adding the digital panel meter and getting it to populate with live data (that was magical!) but need to work through get this working with the PLC5. I believe I might be in this scenario with the PLC5 as I got the exact same error http://advancedhmi.com/forum/index.php?topic=314.0

Sort of learning this when I get the odd moment to try it. My knowledge and skill level with VB is new and low, but I can make some sense of it.

Thanks Archie!!

Ed

[Archie]

There are a lot of steps to control Excel. I will try to break it down to make it easy to follow.

Start with these steps to add the reference to Microsoft Excel:

Code: [Select]

- Open the AdvancedHMI project with Visual Studio
- Right Click the AdvancedHMI project in Solution Explorer and select Properties
- Select the References Tab
- Click Add Reference
- On the COM tab, locate Microsoft Excel Object Library, and then click Select.

This essentially tells Visual Studio where to find the tools to control Excel........

[Archie]

Now you need to setup AdvancedHMI to be able to talk to the PLC. It sounds like you have already done this, but I will explain for any one else :

- In Solution Explorer, double click MainForm.vb
- Build the Solution from the Build menu
- In the Toolbox, go to the AdvancedHMIDrivers group and add an EthernetIPforCLXCom to the form
- In the properties window, set the IPAddress to the address of your PLC
- From the ToolBox, add a DataSubscriber to your form
- In the Properties Window, set PLCAddressValue to the tag name in the PLCwhere you want to get the value


So now we are ready to read data and send it to Excel

[Archie]

- Double click in a blank area of your form and it will take you back to the code
- Enter this code (note that some of it is above the subroutine declaration)

Code: [Select]

Private oXL As Microsoft.Office.Interop.Excel.Application
Private oWB As Microsoft.Office.Interop.Excel.Workbook
Private oSheet As Microsoft.Office.Interop.Excel.Worksheet

Private Sub MainForm_Load(sender As Object, e As EventArgs) Handles MyBase.Load
         ' Start Excel and get Application object.
        oXL = CreateObject("Excel.Application")
        oXL.Visible = True

        ' Get a new workbook.
        oWB = oXL.Workbooks.Add
        oSheet = oWB.ActiveSheet
End Sub


[Archie]

Now we are ready to transfer data from the PLC to Excel.

- Go back to the MainForm by double clicking MainForm.vb in Solution Explorer
- Double click on your DataSubscriber to get back to the code
- Enter this code:

Code: [Select]

Private Sub DataSubscriber1_DataChanged(sender As Object, e As Drivers.Common.PlcComEventArgs) Handles DataSubscriber1.DataChanged

    oSheet.Cells(1, 1).Value = e.values(0)

End Sub

I pullled all of this from my head and have not tested it, so there may be a piece somewhere this I missed. Let me know if it works for you.

[VastSky]

WOW! that is pretty good off the top of your head!!

I will give this a try tomorrow. It will be very exciting to make this work.

Thanks,

Ed

[thirdeye73]

I have had great success in a couple of apps using GemBox to write data to Excel from .NET.  The software is free (with some limitations, a paid version is available but for my needs I have had no issues).  It does not require Excel to be installed on the system, and as such does not use MS Excel's automation object model.  The interface is also much cleaner, and the web site has excellent samples to get you up and going.

http://www.gemboxsoftware.com/spreadsheet/overview

Scott

[VastSky]

Hello Archie - tried this today, but no success.

I wasn't familiar with the exact subroutine Declaration to paste the code ahead of. Here is the MainForm.vb code I tried after adding the reference to Excel to the AdvancedHMI project. I am using Excel Pro 2010 and Visual Basic 2010 Express

Code: [Select]

Public Class MainForm

    Private oXL As Excel.Application
    Private oWB As Excel.Workbook
    Private oSheet As Excel.Worksheet

    Private Sub MainForm_Load(ByVal sender As Object, ByVal e As EventArgs) Handles MyBase.Load
        ' Start Excel and get Application object.
        oXL = CreateObject("Excel.Application")
        oXL.Visible = True

        ' Get a new workbook.
        oWB = oXL.Workbooks.Add
        oSheet = oWB.ActiveSheet
    End Sub

    Private Sub DataSubscriber1_DataChanged(ByVal sender As Object, ByVal e As Drivers.Common.PlcComEventArgs) Handles DataSubscriber1.DataChanged

        oSheet.Cells(1, 1).Value = e.values(0)

    End Sub

    Private Sub MainForm_Load(sender As Object, e As System.EventArgs) Handles Me.Load
        Dim resources As System.ComponentModel.ComponentResourceManager = New System.ComponentModel.ComponentResourceManager(GetType(MainForm))
        Dim x = CType(resources.GetObject("MessageListByValue1.Messages"), MfgControl.AdvancedHMI.Controls.MessageByValue)
    End Sub

    '*******************************************************************************
    '* Stop polling when the form is not visible in order to reduce communications
    '* Copy this section of code to every new form created
    '*******************************************************************************
    Private Sub Form_VisibleChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.VisibleChanged
        AdvancedHMIDrivers.Utilities.StopComsOnHidden(components, Me)
    End Sub

    '**************************************
    '* Filling the form with a gradient
    '**************************************
    Private Sub MainForm_Paint(sender As Object, e As System.Windows.Forms.PaintEventArgs) Handles Me.Paint
        'Dim rect As New System.Drawing.Rectangle(0, 0, e.ClipRectangle.Width, e.ClipRectangle.Height)
        'Dim gradientBrush As New Drawing.Drawing2D.LinearGradientBrush(New Point(0, 0), New Point(0, Height), System.Drawing.Color.FromArgb(180, 100, 200), System.Drawing.Color.FromArgb(110, 200, 255))
        'e.Graphics.FillRectangle(gradientBrush, rect)
    End Sub

End Class


Code: [Select]

Error 1 Type 'Excel.Application' is not defined. F:\AdvancedHMI\AdvancedHMIBetaV397e\AdvancedHMI\MainForm.vb 3 20 AdvancedHMI
Error 2 Type 'Excel.Workbook' is not defined. F:\AdvancedHMI\AdvancedHMIBetaV397e\AdvancedHMI\MainForm.vb 4 20 AdvancedHMI
Error 3 Type 'Excel.Worksheet' is not defined. F:\AdvancedHMI\AdvancedHMIBetaV397e\AdvancedHMI\MainForm.vb 5 23 AdvancedHMI
Error 4 'Private Sub MainForm_Load(sender As Object, e As System.EventArgs)' has multiple definitions with identical signatures. F:\AdvancedHMI\AdvancedHMIBetaV397e\AdvancedHMI\MainForm.vb 7 17 AdvancedHMI

I am hoping to sort of build a very basic HMI within Excel that populates the Excel cells with real time live data at an update rate that is suitable for end to end transmitter calibrations.

Preserving the values as a record would be done manually as I present different PVs to a given transmitter, and then use Copy, & Paste Value on the transmitter PV to record it on another worksheet in Excel.


Here is a snippet from Excel of what I am doing with it using RSLinx to make a crude HMI that I grab values from:

PLC Address,       Live Value Displayed,                   Tag Info,                                                                  Old PLC5 Tag info

Code: [Select]

SCADA_REAL[29] 717.3 PT-001 METER 1 PRESSURE PT-001 TO GC$N(FC3:158) METER 1 PRESSURE PT-001 TO GC$N(FC3:158)
SCADA_REAL[30] 45.5 TT-001 METER 1 TEMPERATURE TT-001 TO GC$N(FC3:160) METER 1 TEMPERATURE TT-001 TO GC$N(FC3:160)
SCADA_REAL[32] 718.0 PT-002 METER 1 PRESSURE PT-002 TO GC$N(FC3:164) METER 1 PRESSURE PT-002 TO GC$N(FC3:164)
SCADA_REAL[33] 45.8 TT-002 METER 1 TEMPERATURE TT-002 TO CG (FC3:166) METER 1 TEMPERATURE TT-002 TO CG (FC3:166)
SCADA_REAL[35] 716.4 PT-901 DISCHARGE PRESSURE  PT-901 TO GC (FC3:170) DISCHARGE PRESSURE  PT-901 TO GC (FC3:170)

Thanks,

Ed

[Archie]

Try this:

Code: [Select]

    Private oXL As Microsoft.Office.Interop.Excel.Application
    Private oWB As Microsoft.Office.Interop.Excel.Workbook
    Private oSheet As Microsoft.Office.Interop.Excel.Worksheet

You have two load event handlers. Delete the second one because it does nothing:

Code: [Select]

Private Sub MainForm_Load(sender As Object, e As System.EventArgs) Handles Me.Load
        Dim resources As System.ComponentModel.ComponentResourceManager = New System.ComponentModel.ComponentResourceManager(GetType(MainForm))
        Dim x = CType(resources.GetObject("MessageListByValue1.Messages"), MfgControl.AdvancedHMI.Controls.MessageByValue)
    End Sub


[VastSky]

Are you a Wizard??

Mind B L O W N !! It Worked!!!!

To add additional PLC addresses would I:

1) Add individual additional DataSubscriber# for additional PLC Tags or use the original DataSubscriber1 and revise the PLCAddressValue to define a Tag range?

2) I would then need to specify where to send the DataSubscriber# results in Excel - I did figure out where in the VB to define the destination cell which gets populated for this single point so far.

Both of these would need to be created/revised within MainForm.vb - correct?

THANKS Archie - this is very cool.

Ed

Update: I tried adding and additional DataSubcriber#, and Cop/Pasted it into the VB, modified its nuember to line up with the new one, and adjusted the destination and IT WORKED!!

Thanks again SO much!!

Ed

[Archie]

That's good news that it worked. I was surprised I was that close since I was thinking it out as I was typing.

As for adding more value, additional DataSubscribers is probably the easiest way to go about it. DataSubscribers will actually let you put multiple addresses in PLCAddressValue by separating them with commas. However the values do all come back to the same event handler, so you have to write some code to check the PLC address and route it to the correct cell. For example:

Code: [Select]

If e.PLCAddress="MyTag1" then
        oSheet.Cells(1, 1).Value = e.values(0)
else if e.PLCAddress="MyTag2" then
    oSheet.Cells(1, 2).Value = e.values(0)
end if

You could have just as well used visual controls such as a DigitalPanelMeter and it's ValueChanged event. That would let you see the value in AdvancedHMI before it sends it to Excel.

[hileta]

I have been able to read/write to a CLX using excel from a PC that has RSLinx OEM and I found a site where he uses the AdvancedHMI Driver to read from the processor, but nothing for writing.

http://plc2k.com/how-to-read-values-from-controllogix-l61-directly-in-excel/#comment-36

 I was wondering if it is possible to read/write to a CLX using excel without having RSLinx installed on the PC. My dilemma is that an array of Strings need to be maintained by office personnel who work exclusively from Excel.

Tim

[Archie]

There is a sample project that shows how to read data from an Excel file, then write it to a ControlLogix:

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