เอาข้อมูลจาก AB PLC ออกมาแสดงในExcelผ่าน RS Linx

โค้ดดังต่อไปนี้แสดงการดึงข้อมูลจาก Allen Bradley PLC ออกมาแสดงบน Excel ผ่าน RS Linx ที่ทำงานแบบ DDE

เปิดไฟล์ Excel ใหม่ขึ้นมา เลือกเมนู Tools > Macro

แล้วเขียนโค้ด VBA เพื่ออ่านค่าจาก PLC เช่น

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

Advertisements
This entry was posted in Uncategorized. Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s