Copy data from a cell that reads live values from PLC via DDE connection.

    Hi all. I connected my pc to a PLC and I can read the values of the desired variables. The problem is, that I cannot copy them. So, I have for example in cell A1 the variable weight and I see values 1985, 1876.... etc. I want to store them. Be able to copy them and after that make charts. I tried this code:


    Code
    Private Sub Worksheet_Calculate()
Worksheets("Sheet1").Range("Output").Value = Range("Input").Value
Worksheets("Sheet1").Range("Time").Value = Time
ThisWorkbook.Names("Output").RefersTo = Worksheets("Sheet1").Range("Output").Offset(1, 0)
ThisWorkbook.Names("Time").RefersTo = Worksheets("Sheet1").Range("Time").Offset(1, 0)
End Sub


    With this code i can only copy the first value. Thanks

    Re: Copy data from a cell that reads live values from PLC via DDE connection.


    I named cell A1 Input, B1Time and C1 Output. All cells are in Sheet1. The code is applied in "Worksheet", "Sheet1".

    Re: Copy data from a cell that reads live values from PLC via DDE connection.


    Nice... I'd like to know how you connected the PLC. I'd like to connect other things and communicate like that.



    What if you stick a button on the sheet and pressed it to collect values will it collect more than just the first one?



    Is the calculate event being called?

    Re: Copy data from a cell that reads live values from PLC via DDE connection.


    I connected it via Ethernet. Then I run the program of the server and its connected. It's very simple really. In any given cell you can connect any variable you want and you can have a live feed. BUT, I cannot copy any values. Can you help me? I am just watching cell A1 updating its' values. Yes, the calculate event is called.

    Re: Copy data from a cell that reads live values from PLC via DDE connection.


    How about the "CHANGE" event?


    and ... how about just doing a debug.print for the target and see if that changes!


    I've not done this sort of thing and .... so all I can do is just ask questions...





    How about ... linking a cell on another sheet and trying to get the value there? Maybe that will work?

    Re: Copy data from a cell that reads live values from PLC via DDE connection.


    Thanks for the reply. I am new to VBA. Can you send me a sample of a code if it is easy? What's debuq.print?

    Re: Copy data from a cell that reads live values from PLC via DDE connection.


    Try this:


    Code
    Private Sub Worksheet_Change(ByVal Target As Range)
    Debug.Print Target.Value
    If Not (Target.Column = 1 And Target.Row = 1) Then Exit Sub
    Worksheets("Sheet1").Range("Output").Value = Range("Input").Value
    Worksheets("Sheet1").Range("Time").Value = Time
    ThisWorkbook.Names("Output").RefersTo = Worksheets("Sheet1").Range("Output").Offset(1, 0)
    ThisWorkbook.Names("Time").RefersTo = Worksheets("Sheet1").Range("Time").Offset(1, 0)
End Sub

    Re: Copy data from a cell that reads live values from PLC via DDE connection.


    Thanks for the response. It doesn't work. Seems that Worksheet_Change isn't called. Any new ideas? What about SetLinkOnData?

    Re: Copy data from a cell that reads live values from PLC via DDE connection.


    Try this

    Code
    Private Sub Worksheet_Calculate()
    Range("b" & Rows.Count).End(xlUp)(2).Resize(, 2).Value = Array(Time, Range("a1").Value)
End Sub

    Re: Copy data from a cell that reads live values from PLC via DDE connection.


    It doesn't work. Seems that calculate event wont trigger. I found this code.


    Code
    Sub ReadValue()
    Channel = Application.DDEInitiate("GatewayDDEServer", "Gateway_DDE.pro")
    Value = Application.DDERequest(Channel, Sheets("Example").Range("E5"))
    Sheets("Example").Range("E9") = Value
    Application.DDETerminate Channel
End Sub


    Code
    Sub SendValue()
    Channel = Application.DDEInitiate("GatewayDDEServer", "Gateway_DDE.pro")
    Application.DDEPoke Channel, Sheets("Example").Range("E5"), Sheets("Example").Range("E7")
    Application.DDETerminate Channel
End Sub
    Code
    Sub SendValue1()
    Channel = Application.DDEInitiate("GatewayDDEServer", "Gateway_DDE.pro")
    Application.DDEPoke Channel, Sheets("EXAMPLE1").Range("L38"), Sheets("Example").Range("N38")
    Application.DDETerminate Channel
End Sub


    What it does is that when you hit a save button it saves the current values. Is there any way to program this button to save automatically when the link is updated? It is kinda unorthodox I know. It saves a file with current values.

    Re: Copy data from a cell that reads live values from PLC via DDE connection.


    What do you mean? Lets say I put =A1 to cell D1. Whats the difference? Where do I put this to the code? Thanks.

    Re: Copy data from a cell that reads live values from PLC via DDE connection.


    I did it with your code and didn't work. I set input the cell =A1. The cell updates as the cell from the live feed. Still cant save new values.

    Re: Copy data from a cell that reads live values from PLC via DDE connection.


    If it doesn't work, I suspect your calculation mode is set to manual somehow or enableevents set to false, otherwise no idea.

    Re: Copy data from a cell that reads live values from PLC via DDE connection.


    Can you tell me how to check if it is set to manual? Can you write the code once more with the new parameter? I am referring to A1=B1 as new parameter. Thanks.

    Re: Copy data from a cell that reads live values from PLC via DDE connection.


    Just open "Immediate Window" from [View] while you are VBE and type in


    Code
    application.calculation = xlautomatic


    then hit Enter

    Code
    application.enableevents = true


    then hit Enter

    Re: Copy data from a cell that reads live values from PLC via DDE connection.


    Where did you put the formula? =A1


    My understanding
    1) A1 will be updated periodically from the link.
    2) if you place the formula in any available cell EXCPT A1 on the same sheet, the formula will calculate.


    So that Calculate Event should happen.

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!

Register Yourself Login