INTELLIGENT WORK FORUMS
FOR ENGINEERING PROFESSIONALS

Log In

Come Join Us!

Are you an
Engineering professional?
Join Eng-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Eng-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

DDE and PLCs

How do I use Excel and DDE to get data out of an Allen Bradley PLC? by chakorules
Posted: 27 Dec 02

Make sure RS Linx is running and you have created a DDE topic. In our example below, our DDE topic is "M1138".

Using the following code, Open an Excel file and click on Tools then Macro, finally clicking on Visual Basic Editor. This will allow you to write some VBA script to tell Excel what you want to read and where to place the data once read from the PLC.


<<<<<<<<<<<<<<<<<<<BEGIN SAMPLE CODE>>>>>>>>>>>>>>>>>>
Sub Start()

Dim lngRow As Long
Dim varCycle As Variant
Dim varLogging As Variant
Dim varResults As Variant
On Error GoTo Error
    
 
    'opens a COLD DDE link
    RSIchan = DDEInitiate("RSLinx", "M1138")
    
    'assign PLC bit values to VB variant varibles
    varLogging = DDERequest(RSIchan, "B3/163")
    varCycle = DDERequest(RSIchan, "B3/161")
    
    'close COLD DDE link
    DDETerminate (RSIchan)
    
    'check to see if the Cycle bit went to "1" if it did, excute read data
    If varCycle(1) = "1" And varLogging(1) = "1" Then

        'starts at row 3 of sheet
        lngRow = 3
        
        If Range("INDATA!A3").Value > 3 Then
            'look up last cell and change position
            lngRow = Range("INDATA!A3").Value
        End If
        
        'check until end of sheet
        For lngRow = lngRow To 65500
            'look for next empty cell
            If Cells(lngRow, 1) = "" Then Exit For
            
            'write current cell location to sheet INDATA
            'rather than writing a loop to search on
            'every cycle, by the time the log is at row 21,500
            'it could take a long time to search the rows...
            Range("INDATA!A3").Value = lngRow + 1
                        
        'add 1 to row "x" to check next row
        Next
          
        'opens a COLD DDE link
        RSIchan = DDEInitiate("RSLinx", "M1138")
        
        'there might be a better way to do this like
        'using this somehow ???????? but I don't know how
        'data = DDERequest(RSIchan, "f11:0,L7,C7")
        'Range("[M1138.xls]LOG!R[x]C1:R[x]C7").Value = data
                                                        
     
        'read word F8:10 and load into a VB variant variable
        f810data = DDERequest(RSIchan, "F8:10")
        'read word F8:11 and load into a VB variant variable
        f811data = DDERequest(RSIchan, "F8:11")
        'read word F8:12 and load into a VB variant variable
        f812data = DDERequest(RSIchan, "F8:12")
        'read word F8:16 and load into a VB variant variable
        f816data = DDERequest(RSIchan, "F8:16")
        'read word F8:18 and load into a VB variant variable
        f818data = DDERequest(RSIchan, "F8:18")
        'read word F8:17 and load into a VB variant variable
        f817data = DDERequest(RSIchan, "F8:17")
        'read word F8:20 force check #1 and load into a VB variant variable
        f820data = DDERequest(RSIchan, "F8:20")
        'read word F8:21 force check #2 and load into a VB variant variable
        f821data = DDERequest(RSIchan, "F8:21")
        'read word F8:22 force check #3 and load into a VB variant variable
        f822data = DDERequest(RSIchan, "F8:22")
        'read word F8:23 force check #4 and load into a VB variant variable
        f823data = DDERequest(RSIchan, "F8:23")
        'read word F8:24 max force and load into a VB variant variable
        f824data = DDERequest(RSIchan, "F8:24")
        'read word F8:25, get PASS or FAIL status from PLC and load into a VB variant variable
        varResults = DDERequest(RSIchan, "F8:25")
        
        'close COLD DDE link
        DDETerminate (RSIchan)
        
        'write all values to cells
        Cells(lngRow, 1).Value = f810data
        Cells(lngRow, 2).Value = f811data
        Cells(lngRow, 3).Value = f812data
        Cells(lngRow, 4).Value = f816data
        Cells(lngRow, 5).Value = f818data
        Cells(lngRow, 6).Value = f817data
        Cells(lngRow, 7).Value = f820data
        Cells(lngRow, 8).Value = f821data
        Cells(lngRow, 9).Value = f822data
        Cells(lngRow, 10).Value = f823data
        Cells(lngRow, 11).Value = f824data
        
      
        'capture time and date stamp into column 8
        Cells(lngRow, 13).Value = Now()
 
        'scrolls the screen down automatically
        'Range("A" & lngRow + 1).Select
      
    End If

End Sub

Back to Programmable logic controllers (PLC) FAQ Index
Back to Programmable logic controllers (PLC) Forum

My Archive


Resources


Close Box

Join Eng-Tips® Today!

Join your peers on the Internet's largest technical engineering professional community.
It's easy to join and it's free.

Here's Why Members Love Eng-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close