Implementing the Omron CX Server DDE and Excel

Utilizing the CX Server DDE Manager will allow us to provide a link to the information in the Omron PLC. This link can be placed on an Excel spreadsheet. Charts, graphs, etc. can be made from the data. We will create links on an excel spread sheet from a CQM1H and a CP1L PLC. The information will then be displayed as a bar graph comparing the first ten memory areas.

Dynamic Data Exchange (DDE) is a channel through which correctly prepared programs can actively exchange data and control other applications within Microsoft Windows. The DDE manager will allow us to set up all of the links.

Start the DDE Manager by clicking Start/Programs/Omron/CX-Server/DDE Manager

This will start the DDE Manager and place an icon on your toolbar at the bottom of your screen. The application will run minimized.

Right-click on the minimized DDE Manager icon and select Project/New.

Assign a filename (ACC DDE.cdm) to the CX-Server project that you will create, and save it.

The create project will minimize to the taskbar. Right-click on the minimized DDE Manager icon and select Project/Edit.

This will call up the DDE Manager Project Editor. We can now add the PLC’s (Devices) and Points that we want to display.

Select File/New/PLC, or hit the PLC Icon on the taskbar.

Under Change PLC, we can now enter the information about how we are communicating to the programmable logic controller.

Device Name: – Name in which the DDE Manager will call the PLC
Device Type: – The model of PLC that will be connected
Network Type: – This is the communication method that we will be talking with the device.

In our example we will use the following two Omron PLCs.

  • CQM1H – SYSMAC WAY (Omron Host Link Protocol) Communication Port  11, 9600, E, 7, 2. Unit #00
  • CP1L – USB

 Once we have the PLC information, we can now add points that will contain the information that we wish to retrieve from the units. Select File/New/Point  or select the point editor icon to start the point editor.

On the Logical tab you can give a name (Or Change) to the point in the PLC to get. Select the Physical tab.

On the physical tab we set the following information:
PLC: – This is the list of PLCs entered in the previous step
Data Location: – The memory location that we want to read from
Internal Data Type: – This is the interpretation of the data that we are reading. (Example: Bit, Word, BCD, etc.)
Command Modifier: – This is used if we need to Force Set / Reset the data location. This can be used to override the PLC program. Use with caution.
Press OK when finished setting the address.

 Our point is now programmed. You will notice the symbols before the name of the point. This tells us at a quick glance what type of data we are looking at. Please refer to the CX Server Runtime manual for all of the symbol meanings.

Fill out the remaining points for our example.
CP1L – D0 to D9
CQM1H – DM0 to DM9

Start Microsoft Excel.

Go back to the DDE manager and the points under the Project Editor. We can now select the first ten items in the list. Click the first one and then move to the last item. Hold the shift while clicking the last item will select all of them between.
Hit the DDE Link icon.

In Excel, we now paste the information onto the worksheet. (Ctrl + V) Right click the cell and select paste.

The information will now be displayed and updated  on your worksheet.
The update time will depend on the interval set in the DDE Manager. Right-click on the minimized DDE Manager icon and select Update Interval…

We can also set individual point update times in the DDE link item. The default update time is set via the DDE Manager but we can also set the time in the individual points on the spreadsheet.
DDE Update Default Link:
=CDMDDE|’C:\ACC DDE.cdm’!’\CP1L D0′
5 Second Update Link:
=CDMDDE|’C:\ACC DDE.cdm’!’\CP1L D0,5′

In the Project Editor of the DDE Manager you can see the first 10 memory areas are being used. This is the yellow triangle with the lightning bolt symbol. (You will not be able to delete the point if it is in use.)

Calling up an excel file with the links embedded, will give you a Security Alert – Links warning. Select ‘Enable the content’ and then hit OK.

Here is an example of the finished excel file. We have 10 memory locations from each PLC listed. We have inserted a graph that will dynamically change when the values are read.
Download the excel file here.

 As you can see implementing the CX Server DDE Manager is not a difficult task. It provide a quick and efficient way to view the information that we need.

Additional Information:
Excel – Conditional Movement of Data

Watch on YouTube : Implementing the Omron CX Server DDE and Excel
If you have any questions or need further information please contact me.
Thank you,
Garry



If you’re like most of my readers, you’re committed to learning about technology. Numbering systems used in PLC’s are not difficult to learn and understand. We will walk through the numbering systems used in PLCs. This includes Bits, Decimal, Hexadecimal, ASCII and Floating Point.

To get this free article, subscribe to my free email newsletter.


Use the information to inform other people how numbering systems work. Sign up now.

The ‘Robust Data Logging for Free’ eBook is also available as a free download. The link is included when you subscribe to ACC Automation.

How to Implement the Omron Host Link Protocol Part 2 – VBA

ACC Omron Host Link VBA

In Part 1, we used VB6 to communicate from the computer to the PLC. We will now use Visual Basic for Applications (VBA) to accomplish the same task. NETComm will be the serial driver and Excel will be the program that we will use. Using Excel we will have access to the Excel Object Model so we can utilize worksheets, ranges etc.

Please refer to Part 1 (How to Implement the Omron PLC Host Link Protocol) for the details of the wiring of the serial port and protocol sequence.

The first step in using VBA is to download and register NETComm.ocx. To use serial communications with VBA you must register the NETComm.ocx driver.

Download the NETComm.ocx file to the c:\ directory.

Call up a dos prompt with administrator authority. (Right click on the cmd.exe program and select run as administrator.)

Type ‘regsvr32 netcomm.ocx’

An information box will be shown indicating that the NETComm.ocx was registered successfully.

Now open Excel and make the following on Sheet 1.

Select Developer and then Visual Basic.

If you do not have the Developer option to select, then do the following:
Select File : Options: Customized Ribbon
Select Developer and hit OK

VBA – Visual Basic for Applications will open

Insert a user form.

Add NETComm1 to UserForm1. If it is not on your Toolbar, then select additional controls and select NETCommOCX.NETComm. Press the OK. Then drag this control onto your form.

Create three command buttons. This can be done by going to Design Mode,  and under the Insert menu you can select Command Buttons.

Here is the entire code for the application:

Private T$ ' Modual Scope for variable
Private TRead$
Private NumWords
Private startcycle
Private Sub CommandButton1_Click()
If NumWords = "" Then
    MsgBox "Set Read String"
    Exit Sub
End If
If startcycle = 0 Then
    startcycle = 1
    CommandButton1.BackColor = "&H0000FF00"
Else
    startcycle = 0
    CommandButton1.BackColor = "&H8000000F"
    UserForm1.NETComm1.PortOpen = False ' Close Communication Port
    Exit Sub
End If
UserForm1.NETComm1.PortOpen = True ' Open Communication Port
Application.EnableEvents = True
Do
    T$ = TRead$ ' Reset the Transmitted Information
    charreturn = 11 + (NumWords * 4) ' Determine the return characters
    GoSub FCS ' Checksum
    GoSub communicate ' Get informaiton
    
    'Check returned information and Display
    If Mid(rxd$, 6, 2) = "00" And (Len(rxd$)) >= charreturn Then
        For x = 1 To Sheets("Sheet1").Range("D7")
            Sheets("Sheet1").Range("B" & x + 10 - 1) = Mid(rxd$, x * 4 + 4, 4)
        Next 'x
    End If
    DoEvents ' Do other tasks
    'Update the date and time
    Sheets("Sheet1").Range("C09") = Format(Date, "YYYY/MM/DD") + "    " + Format(Time, "HH:MM:SS")
Loop While startcycle = 1
If startcycle = 0 Then Exit Sub
communicate:
rxd$ = ""
Buffer = T$ + FCS$ + "*" + Chr$(13)
UserForm1.NETComm1.Output = Buffer
Time1 = Now
Timeout = Now + TimeValue("0:00:02")
Do
    If Timeout <= Time1 Then GoTo timeoutcom
    DoEvents
    Time1 = Now()
Loop Until UserForm1.NETComm1.InBufferCount >= charreturn
rxd$ = UserForm1.NETComm1.InputData
fcs_rxd$ = Left((Right(rxd$, 4)), 2)
If Left(rxd$, 1) = "@" Then
    T$ = Mid(rxd$, 1, (Len(rxd$) - 4))
ElseIf Mid(rxd$, 2, 1) = "@" Then
    T$ = Mid(rxd$, 2, (Len(rxd$) - 5))
    rxd$ = Mid(rxd$, 2, (Len(rxd$) - 1))
End If
GoSub FCS
If FCS <> fcs_rxd$ Then
    rxd$ = ""
End If
clearbuffer$ = UserForm1.NETComm1.InputData
Return
FCS:
    L = Len(T$)
    A = 0
    For J = 1 To L
        TJ$ = Mid$(T$, J, 1)
        A = Asc(TJ$) Xor A
    Next J
    FCS$ = Hex$(A)
    If Len(FCS$) = 1 Then FCS$ = "0" + FCS$
Return
timeoutcom:
If startcycle = 0 Then Exit Sub
clearbuffer$ = UserForm1.NETComm1.InputData
rxd$ = ""
Return
End Sub
Private Sub CommandButton2_Click()
If startcycle = 0 Then
    UserForm1.NETComm1.CommPort = Sheets("Sheet1").Range("A4")
    UserForm1.NETComm1.Settings = Sheets("Sheet1").Range("B4") & "," & Sheets("Sheet1").Range("C4") & "," & Sheets("Sheet1").Range("D4") & "," & Sheets("Sheet1").Range("E4")
    MsgBox UserForm1.NETComm1.Settings
Else
    MsgBox "Stop Reading Parameters to Set MSComm"
End If
End Sub
Private Sub CommandButton3_Click()
If startcycle = 0 Then
    Unit = Sheets("Sheet1").Range("A7")
    If Len(Unit) < 2 Then Unit = "0" & Unit
    StartADD = Sheets("Sheet1").Range("C7")
    Do
        If Len(StartADD) < 4 Then
            StartADD = "0" & StartADD
        End If
    Loop Until Len(StartADD) = 4
    NumWords = Sheets("Sheet1").Range("D7")
    Do
        If Len(NumWords) < 4 Then
            NumWords = "0" & NumWords
        End If
    Loop Until Len(NumWords) = 4
    T$ = "@" & Unit & "RD" & StartADD & NumWords
    TRead$ = T$
    MsgBox T$
    For x = 1 To Sheets("Sheet1").Range("D7")
        Sheets("Sheet1").Range("A" & x + 10 - 1) = "DM " & Sheets("Sheet1").Range("C7") + x - 1
        Sheets("Sheet1").Range("B" & x + 10 - 1) = ""
    Next 'x
    For x = Sheets("Sheet1").Range("D7") To 200
        Sheets("Sheet1").Range("A" & x + 10) = ""
        Sheets("Sheet1").Range("B" & x + 10) = ""
    Next 'x
Else
    MsgBox "Stop Reading Parameters to Set Read String"
End If
End Sub
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
End Sub

Note: The Chart on Sheet1 is just a selection of the first ten DM areas and insert a bar graph.

Running the program produces the following information:
 

Download the excel file ACC Omron Host Link VBA.XLS. This is the complete program mentioned above.

 

When you open the file it will warn you about macros. This is the VBA application. Press ‘Enable Macros’.

When changing the parameters on the screen, you will also get a warning about Active X. This is the NETComm.ocx which was registered above. Press ‘OK’ to run the application.

Additional Information:
Excel – Conditional Movement of Data

Watch on YouTube : How to Implement the Omron Host Link Protocol Part 2 – VBA
If you have any questions or need further information please contact me.
Thank you,
Garry



If you’re like most of my readers, you’re committed to learning about technology. Numbering systems used in PLC’s are not difficult to learn and understand. We will walk through the numbering systems used in PLCs. This includes Bits, Decimal, Hexadecimal, ASCII and Floating Point.

To get this free article, subscribe to my free email newsletter.


Use the information to inform other people how numbering systems work. Sign up now.

The ‘Robust Data Logging for Free’ eBook is also available as a free download. The link is included when you subscribe to ACC Automation.

Three Free PLC Programming Software Tools

Every manufacturer has there own software to program the programmable logic controller (PLC) or the human machine interface (HMI). However there are a few tools that are free which will help in the development of your PLC programs. We will look at three of these software tools and show how beneficial they can be to you.

Synergy

When programming, I usually will have my laptop for the ladder logic and my desktop for the screen software. Connecting the two together and using one mouse and keyboard saves me the aggravation of switching back and forth between the keyboards.

Synergy lets you easily share a single mouse and keyboard between multiple computers with different operating systems each with its own display without special hardware. It is intended for users with multiple computers on their desk since each system uses its own monitor(s). Redirecting the mouse and keyboard is as simple as moving the mouse off the edge of your screen. Synergy also merges the clipboards of all the systems into one, allowing cut-and-paste between systems. It works on Windows, Mac OS X and Linux.

Synergy Website
http://synergy-project.org/
Downloads:
Synergy for Windows:
http://download.cnet.com/Synergy/3000-2072_4-10714570.html
Synergy for Mac
http://download.cnet.com/Synergy/3000-2094_4-75362427.html

Windows Calculator

That’s right. The windows calculator can be a very helpful tool when it comes to programming PLCs. When you need to convert, hexadecimal to binary, BCD to hexadecimal, or any other combination the windows calculator can do it for you.

What everybody ought to know about PLC numbering systems.

Start the calculator. Start – All Programs – Accessories – Calculator

View the programmer calculator. View – Programmer (Alt + 3)

We can then choose Hex for our numbering system. Then Word for our length of address. You will notice that the display will show the Bin equivalent along with the marking of bit 0 to bit 15.

7ABC base 16 = 0111 1010 1011 1100 base 2 = 31420 BCD

Note: I am using Windows 7, but all of the versions of window calculator have similar functionality.

Windows HyperTerminal

Serial communication can be difficult using the PLC. HyperTerminal can be used to monitor the communication being sent from or to the programmable logic controller. Just hook up to the serial port and program HyperTerminal to monitor the port. Set the correct Data Bits, Baud Rate, Parity, Stop Bits etc. Viewing the information on the monitor will assist you in seeing the exact data being sent to, or received by the PLC.

HOW TO IMPLEMENT THE OMRON PLC HOST LINK PROTOCOL

Hype Terminal – Windows 7 and 8

HyperTerminal was no longer sent with windows when Windows 7 was introduced.

Hype Terminal is fully functional replacement of HyperTerminal, perfect for GSM and GPS debugging, works with AT Commands. You can use Hype Terminal to help debug source code from a remote terminal. You can also use Hype Terminal to communicate with older character-based computers. Hype Terminal is designed to be an easy-to-use tool and is not meant to replace other full-feature tools available on the market.

Download Hype Terminal:
http://download.cnet.com/Hype-Terminal/3000-2086_4-76158601.html

Watch on YouTube : Three Free PLC Programming Software Tools
If you have any questions or need further information please contact me.
Thank you,
Garry



If you’re like most of my readers, you’re committed to learning about technology. Numbering systems used in PLC’s are not difficult to learn and understand. We will walk through the numbering systems used in PLCs. This includes Bits, Decimal, Hexadecimal, ASCII and Floating Point.

To get this free article, subscribe to my free email newsletter.


Use the information to inform other people how numbering systems work. Sign up now.

The ‘Robust Data Logging for Free’ eBook is also available as a free download. The link is included when you subscribe to ACC Automation.