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

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


    Well that's what I did. Cell A1 updates from the server and Cell B1=A1. It doesn't work.

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


    Was there a change from the first screen shot?


    B1 has time in it and A1 has a value.

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


    Did you try Activeworkbook.Setlinkondata?

    Rory
    Theory is when you know something, but it doesn’t work. Practice is when something works, but you don’t know why. Programmers combine theory and practice: nothing works and they don’t know why

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


    @iwrk4dedpr the screenshot was from a previous try. You are right. The Cell with the =A1 function was C or D. It was right. The outcome was endless posting of timestamps and endless copying of the first value. It didn't work.
    rory I want to try it, but I don't know how to write the code. Can you help?

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


    It's basically:

    Code
    ThisWorkbook.Setlinkondata "your DDE link name", "name of routine you want to run"


    Your best bet is probably to run it from the Workbook_Open event - this code needs to go into the ThisWorkbook module of your workbook:

    Code
    Private Sub Workbook_Open()
ThisWorkbook.Setlinkondata "your DDE link name", "name of routine you want to run"
End Sub

    Rory
    Theory is when you know something, but it doesn’t work. Practice is when something works, but you don’t know why. Programmers combine theory and practice: nothing works and they don’t know why

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


    Thanks for the immediate response. By routine, what do you mean? A macro? Or the name of the variable?

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


    A macro. So you'd have a routine in a normal module:

    Code
    Sub CopyValues()
    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


    Then:

    Code
    Private Sub Workbook_Open() 
    ThisWorkbook.Setlinkondata "your DDE link name", "CopyValues" 
End Sub

    Rory
    Theory is when you know something, but it doesn’t work. Practice is when something works, but you don’t know why. Programmers combine theory and practice: nothing works and they don’t know why

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


    I put the Sub Copy values on the module and the private sub on the worksheet. Didn't work.

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


    The workbook_open code must be in the ThisWorkbook module, not a worksheet module. You'll also need to run the code yourself or reopen the workbook. I assume you have altered the DDE link name in the code appropriately.

    Rory
    Theory is when you know something, but it doesn’t work. Practice is when something works, but you don’t know why. Programmers combine theory and practice: nothing works and they don’t know why

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


    IT IS WORKING!!!!! Only problem is that I have to hit run on the code. Can we make it run all the time and save the values??? Please tell me you can do that. You are going to save my day! Thanks no matter what.

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


    What do you mean by "Only problem is that I have to hit run on the code"?

    Rory
    Theory is when you know something, but it doesn’t work. Practice is when something works, but you don’t know why. Programmers combine theory and practice: nothing works and they don’t know why

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


    I mean that i open the vba and hit run sub (the green triangle). I run the machine with the PLC on it and it keeps the value. Next time i have data it wont work. I have to go again on the vba window and hit the run sub button. Why? I want to have the excel open, linked to the PLC and recording data all the time.

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


    Quote from tsiou;704749

    I mean that i open the vba and hit run sub (the green triangle). I run the machine with the PLC on it and it keeps the value. Next time i have data it wont work. I have to go again on the vba window and hit the run sub button. Why? I want to have the excel open, linked to the PLC and recording data all the time.


    then use the "Application.Ontime" method! Schedule the routine to run every xx seconds you choose!

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


    This is a nice solution, but i dont know when the data will be placed. The machine is a robot and the purpose is to run whenever the user wants. So there is no use of application.Ontime. I guess i can do it though and have it running. Can you help me with the syntax?

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


    Tried it. If the code runs on a loop, it constantly make copies. So if there is no new value, it returns the last one. It corrupts my data. Is there any solution? Any ideas? I guess we have to find a trigger. Right? Like a variable True/False which will start/stop the code. Any help?

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


    Ok, so I found a way to trigger DDE via Calculate_Event. To be honest you helped me a lot, so you did found it. Now, what I did is to set a True/False variable as a trigger. The only problem is that excel calculates while the variable is True (those miliseconds) and returns every new value 4 times. So, I can copy values as they come, whenever they come but I have every value 4 times. What can we do? Here is the code I used.
    I set A5=A3, with A3 updating from DDE as True/False and I set A7=IF(A5=FALSE,"150","-100")


    In Sheet1

    Code
    Private Sub Worksheet_Calculate()
If Range("A7").Value = -100 Then CopyValues
End Sub


    And module

    Code
    Sub CopyValues()
Worksheets("Sheet1").Range("Output2").Value = Range("Input")
Worksheets("Sheet1").Range("Time2").Value = Time & Date
ThisWorkbook.Names("Output2").RefersTo = Worksheets("Sheet1").Range("Output2").Offset(1, 0)
ThisWorkbook.Names("Time2").RefersTo = Worksheets("Sheet1").Range("Time2").Offset(1, 0)
End Sub

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


    I confess I have no idea why SetLinkondata only runs once or why the original Calculate event didn't work, but perhaps try:

    Code
    Private Sub Worksheet_Calculate() 
    If Range("A7").Value = -100 Then
      Application.EnableEvents = False
      CopyValues 
      Application.EnableEvents = True
End Sub

    Rory
    Theory is when you know something, but it doesn’t work. Practice is when something works, but you don’t know why. Programmers combine theory and practice: nothing works and they don’t know why

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


    It's ok. I am grateful that you are still answering and helping. Tried your suggestion. I have 7 copies now. Is there any solution with filters? I tried remove duplicates but wont clean all of them. Any suggestion?

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


    Perhaps your DDE link is actually triggering multiple times even if the value doesn't change. Maybe try something like:

    Code
    Dim vOldVal
Private Sub Worksheet_Calculate()
   If Range("A7").Value = -100 Then
      If Range("A5").Value <> vOldVal Then
         vOldVal = Range("A5").Value
         CopyValues
      End If
   End If
End Sub


    So that the code only runs when the value is actually changed.

    Rory
    Theory is when you know something, but it doesn’t work. Practice is when something works, but you don’t know why. Programmers combine theory and practice: nothing works and they don’t know why

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


    I think we 're close. Seems that oldval does the trick. But, with the code as you wrote it (Dim voldval first) wont trigger the CopyValues. I have to do it manually. Like hitting the run button. If I place the Dim voldval after the Private Sub it copies as earlier (multiple times). How can we insert oldval without troubling the CopyValues macro?

Participate now!

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

Register Yourself Login