Excel to/from AB PLC

[wilkerson29847]

Good morning.

I originally posted about this topic on April 4th and ran into an issue with the IT department and their rules but have since found a resolution.

I acquired another laptop from IT and an OEM version of RS Linx for the Excel to PLC interface. I can get the data to read from the PLC using the Copy DDE/OPC link in RS Linx and pasting into Excel. Here is my question:

We are using an MES system and I want to be able to update the Part numbers in the PLCs from this master Excel File and all of these Part numbers are string values. All of the examples I have seen show updating multiple tags with one command button in excel but they are all in sequence, e.g. Tag[0], Tag[1], Tag[2], etc.
I think the example I saw was from someone or somewhere on this forum.

In my situation the tags are part of a UDT like the following:
Tool[1].SAP_Number_1
Tool[1].SAP_Number_2

Tool[2].SAP_Number_1
Tool[2].SAP_Number_2

Tool[3].SAP_Number_1
Tool[3].SAP_Number_2............and so on.

Also, there are between 40 and 50 PLCs that will have to be updated depending on what part numbers change with revision changes. Am I wasting my time or is there a fairly easy way to do this? Do I have to have a separate Command Button in Excel for SAP_Number_1 and SAP_Number_2? I currently have a worksheet made for each machine, so would I need 2 Command Buttons per sheet for a Read operation and 2 per sheet for a Write Operation?. Can someone give me some direction? I have never tried to send and receive data from Excel to/from a PLC so I am pretty clueless on the VBA side of Excel.

Thank you to everyone helping those of us with less experience and knowledge!!
Report Post

 

[Aardwizz]

It's not a waste of time.

Arguably, the easiest way to accomplish your task is to create 2 columns on your spreadsheet: One with the tag name, one with the tag value you wish to Write when you push a "[WRITE]" button. You might want to add a 3rd column for the value being read in the PLC, either live (as you've done), or populated by a "[READ]" button.

It can help to have a topic that is the value in a cell, rather than hard-coded. I do that on a sheet called "REFERENCE".

You didn't say whether each PLC would get the same SAP data at the same time, or different ones at different times. Based on the fact that you have one sheet per PLC implies to me that they are different. You could, perhaps, modify the code here to put all the topics on one sheet, have a single write button with a For-Next loop that does one column with one topic (in the first row), then does the second column with a second topic, etc.

The code here contains examples of all the techniques that you should need, but not necessarily a complete solution for you.

------------------------------

You should first create a "Common" module for the functions that will be common to all your sheets: "PLC_Connect", "PLC_Read", and "PLC_Write".

Sample code:

Public Function PLC_Connect() As Long
    On Error Resume Next
    
    Dim Topic As String
    
    'Open the Connection
    [COLOR="Blue"]Topic = Worksheets("REFERENCE").Range("A2")[/COLOR]
    PLC_Connect = DDEInitiate("RSLinx", Topic)
    
    'Verify that the Connection was made
    If Err.Number <> 0 Then
        MsgBox "Error Connecting to PLC", vbExclamation, "Error"
        PLC_Connect = 0
    End If

In your case, rather than a single location for the topic (shown in Blue), you might have the topic or the as an input parameter from an individual sheet.

-------------------------------

Next would be a generic PLC_Write function, that takes the tag name and value from "Write" button on a sheet and the connection to the PLC from the above function, and sends that data to the PLC.

I've run into an issue with the DDE function in Excel that the datatype (DInt, Real, String) of data being sent is determined by the datatype in the cell. So a generic "Write" function either must be passed a cell reference, or use some cells to define the datatype. This code does the latter:

Public Function PLC_Write(RSLink, Data, Tag As String)
    On Error Resume Next

    If IsNumeric(Data) Then
        If Int(Data) = Data Then
            XmitInt = CLng(Data)
            Worksheets("REFERENCE").Cells(2, 4) = Data
            DDEPoke RSLink, Tag, Worksheets("REFERENCE").Cells(2, 4)
        Else
            XmitReal = CSng(Data)
            Worksheets("REFERENCE").Cells(3, 4) = Data
            DDEPoke RSLink, Tag, Worksheets("REFERENCE").Cells(3, 4)
        End If
    Else
        XmitString = CStr(Data)
        Worksheets("REFERENCE").Cells(4, 4) = Data
        DDEPoke RSLink, Tag, Worksheets("REFERENCE").Cells(4, 4)
    End If

    If Err.Number <> 0 Then
        MsgBox "Error Writing to PLC", vbExclamation, "Error"
        Open_RSLinx = 0
    End If
End Function

------------------------------------------------

Next is a generic "Read" function, which passes a row, a column, and a tag, and this function will read the PLC (from the above connection) and write the value to the row & column on the sheet where the [READ] button lives.

Public Function PLC_Data_Read(RSLink, row, col, Tag As String)
    On Error Resume Next
    
    RequestString = Tag + ",L1,C1" + Chr$(34) + ")"
    Cells(row, col) = DDERequest(RSLink, RequestString)

    If Err.Number <> 0 Then
        MsgBox "Error Reading from PLC", vbExclamation, "Error"
        Open_RSLinx = 0
    End If

End Function

==================================

Now that the generics are in place, you would put a Command Button (from the Developer Tab: Insert >> ActiveX Controls).
The tag name ("Tool[1].SAP_Number_1", etc) would be in Column A, starting on row 2 (Row 1 has the button and column header). It's important that there be no blank lines for this function to work.

The Write button has the following script (right click on the button and select View Code). Note: I renamed my Command Button from the default name of CommandButton1 to "WriteData"

Private Sub WriteData_Click()

    Dim TagName As String

'Determine the number of the last row in column 'A' of the Sheet
    RowLast = Cells(Rows.Count, "A").End(xlUp).row

'Connect to the PLC
RSLink = PLC_Connect()

'Scroll through the list of tags
For r = 2 To RowLast   'Read the Table

    TagName = Me.Cells(r, 1).Value
    Data = Me.Cells(r, 2).Value
    Call PLC_Data_Write(RSLink, Data, TagName)
    
Next r
    
     'Sever the PLC Connection
    DDETerminate (RSLink)

End Sub

Similarly, if you wish to read the PLC data rather than having live links,

Private Sub ReadData_Click()

    Dim r As Integer '(row of Tag list)
    Dim TagName As String

'Determine the number of the last row in column 'A' of the I/O List Sheet
    RowLast = Cells(Rows.Count, "A").End(xlUp).row

'Connect to the PLC
RSLink = PLC_Connect()


'Scroll through the list of tags
For r = 2 To RowLast   'Read the Table

    TagName = Me.Cells(r, 1).Value
    Call PLC_Data_Read(RSLink, r, 3, TagName)
    
Next r
    
     'Sever the PLC Connection
    DDETerminate (RSLink)


End Sub

One thing I do is use Conditional Formatting to compare the value in the Read column with the value in the Write, and if they are different, change the Fill color. Now I can scroll down (or filter on color) and see any value that has changed.

Now you are ready to copy this sheet 40 times, after you do whatever you want to have the connections point to different PLCs/Topics.

Good luck !

 

[wilkerson29847]

Thank you for the quick and thorough reply. Each PLC will be updated separately and at different times, depending on the revision change for the SAP Number.

I was planning on doing a separate write button for each PLC and a separate Read button so they can compare the values to make sure the PLC received the correct number.

I was actually going to go into each PLC and add a String array in the CLX type PLCs to allow the looping through with just advancing the "[number]", so I could write to "String_Write_Excel[100] and then copy the values to the proper Tool. Same with the Read with a tag named "String_Read_Excel[100]".

Then in the MicroLogix PLCs add, for example, data files ST123 for write and ST124 for read. I thought it would make the script a little easier to write and understand.