LTP in excell

[mesuteryilmaz]

Hello Bhai,
i am assuming that u r taking live data in excel....
I am also facing this problem...
i have asked this question here..
https://www.mrexcel.com/forum/excel...rksheet_change-event-problem.html#post5190714

Yes it's live DDE links. Mine is not a problem yet. It is a question, yet.
The link you mentioned addresses another issue.
What I would like the code does for me is whenever the B5 cell updates excel writes the values B5:N5 to the next empty row available.

 

[Romeo1998]

Hello Bhai,
use this code in vba of the same excel sheet...
I have tested by manually entering data with keyboard, n it works fine havnt tested with dde

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("b5:n5")) Is Nothing Then
If IsEmpty(Target.Offset(1, 0)) Then
Target.Offset(1, 0) = Target.Value
Else
Target.End(xlDown).Offset(1, 0) = Target.Value
End If
End If
End Sub

Best of Luck

 

[mesuteryilmaz]

Hello Bhai,
use this code in vba of the same excel sheet...
I have tested by manually entering data with keyboard, n it works fine havnt tested with dde

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("b5:n5")) Is Nothing Then
If IsEmpty(Target.Offset(1, 0)) Then
Target.Offset(1, 0) = Target.Value
Else
Target.End(xlDown).Offset(1, 0) = Target.Value
End If
End If
End Sub

Best of Luck

thank you,
I will try and see if it is working

 

[mesuteryilmaz]

Unfortunately I was not able to do with the code you have given.
However I did find out how to do what I was trying to accomplish.

You can do it by following code:
----------------------------
Sub DDELink()
Sheets(1).Cells(1, 25).Formula = "=Server|" & Topic & "!'Field'"
ActiveWorkbook.SetLinkOnData "Server|" & Topic & "!Field", "GetData"
End Sub
----------------------------
Sub GetData()
Dim Chan As Long, vDat As Variant, sDat As String, R As Long
R = 3
On Error Resume Next

ThisWorkbook.Sheets(1).Rows(R).Insert
Chan = DDEInitiate("Server", Topic)
vDat = DDERequest(Chan, "Field")
sDat = vDat(1)
ThisWorkbook.Sheets(1).Cells(R, 1).Value = sDat

DDETerminate Chan
End Sub
----------------------------

 

[mesuteryilmaz]

DDELink() sub runs GetData() sub whenever cell(1,25) value updates by server.
then GetData() will insert a row above rowR and write the updated value.