DDE server data into excel, how to auto record those datas?

Please answer the questions completely or we can go no further...

1. Is there an actual link in your cell that recieves the DDE updates?

The answer to this question is important. Please post the link(function) here...

2. Are you able to use VBA code? You will have to use VBA to automate this as far as I know.

Please see this post for more info.

Please answer the questions completely or we can go no further...

1. Is there an actual link in your cell that recieves the DDE updates?

The answer to this question is important. Please post the link(function) here...

2. Are you able to use VBA code? You will have to use VBA to automate this as far as I know.

Please see this post for more info.

Click to expand...

This is the code in my active cell:

=MT4|BID!USDJPYm

say for example at cell A1. The info in this cell refreshes every ten seconds. So every ten seconds, I would like to store the info into cell A2, then A3... then on....

I have some basic knowledge on visual basic. Should be able to use it.

Hope that I get your question right. Thanks a lot

Here is a basic example. Dedicate a worksheet for your DDE link(s) and record the data in a separate worksheet. Create a new workbook with two sheets. "DDE_Link" and "Data". Copy and paste the code or download the example.

falanks_DDE_Example.zip

<table width="100%" border="1" bgcolor="White" style="filterrogid:DXImageTransform.Microsoft.Gradient(endColorstr='#C0CFE2', startColorstr='#FFFFFF', gradientType='0');" ><TD><font size="2" face=Courier New>  <font color="#008000">'if you wish to automatically begin recording information when the workbook</font>
  <font color="#008000">'is opened; place this procedure into the "ThisWorkbook" class module.</font>
  <font color="#0000A0">Private</font> <font color="#0000A0">Sub</font> Workbook_Open()
       BeginRecordingUpdates
  <font color="#0000A0">End</font> <font color="#0000A0">Sub</font>
  
  <font color="#008000">'the remainder of this code goes into a standard module</font>
  <font color="#008000">'simply run "BeginRecordingUpdates" to start recording</font>
  <font color="#0000A0">Private</font> <font color="#0000A0">Const</font> MyLinksName <font color="#0000A0">As</font> <font color="#0000A0">String</font> = "MT4|BID!USDJPYm"
  <font color="#0000A0">Private</font> <font color="#0000A0">Const</font> RecordToSheetName = "Data"
  
  <font color="#0000A0">Sub</font> BeginRecordingUpdates()
       ThisWorkbook.SetLinkOnData MyLinksName, "RecordUpdates"
       ThisWorkbook.Sheets(RecordToSheetName).Cells.ClearContents
  <font color="#0000A0">End</font> <font color="#0000A0">Sub</font>
  
  <font color="#0000A0">Sub</font> RecordUpdates()
       <font color="#0000A0">Dim</font> r <font color="#0000A0">As</font> <font color="#0000A0">Long</font>
  
       <font color="#0000A0">With</font> ThisWorkbook.Sheets(RecordToSheetName)
           r = Application.WorksheetFunction.CountA(.Columns(1)) + 1
           <font color="#0000A0">If</font> r > 65535 <font color="#0000A0">Then</font> StopRecordingUpdates
           .Cells(r, 1).Value = ThisWorkbook.Sheets("DDE_Link").Range("A1").Value
           .Cells(r, 2).Value = Now
       <font color="#0000A0">End</font> <font color="#0000A0">With</font>
  <font color="#0000A0">End</font> <font color="#0000A0">Sub</font>
  
  <font color="#0000A0">Sub</font> StopRecordingUpdates()
       ThisWorkbook.SetLinkOnData MyLinksName, ""
  <font color="#0000A0">End</font> <font color="#0000A0">Sub</font>
  
</FONT></td></tr></table>
<SPAN>
<button onclick='document.all("TomsCode2").value=document.all("TomsCode2").value.replace(/<br \/>\s\s/g,"");window.clipboardData.setData("Text",document.all("TomsCode2").value);'>Copy to Clipboard</BUTTON> falanks_DDE_Example.zip</SPAN>

<textarea style="position:absolute;visibility:hidden" name="TomsCode2" wrap="virtual">
'if you wish to automatically begin recording information when the workbook
'is opened; place this procedure into the "ThisWorkbook" class module.
Private Sub Workbook_Open()
BeginRecordingUpdates
End Sub

'the remainder of this code goes into a standard module
'simply run "BeginRecordingUpdates" to start recording
Private Const MyLinksName As String = "MT4|BID!USDJPYm"
Private Const RecordToSheetName = "Data"

Sub BeginRecordingUpdates()
ThisWorkbook.SetLinkOnData MyLinksName, "RecordUpdates"
ThisWorkbook.Sheets(RecordToSheetName).Cells.ClearContents
End Sub

Sub RecordUpdates()
Dim r As Long

With ThisWorkbook.Sheets(RecordToSheetName)
r = Application.WorksheetFunction.CountA(.Columns(1)) + 1
If r > 65535 Then StopRecordingUpdates
.Cells(r, 1).Value = ThisWorkbook.Sheets("DDE_Link").Range("A1").Value
.Cells(r, 2).Value = Now
End With
End Sub

Sub StopRecordingUpdates()
ThisWorkbook.SetLinkOnData MyLinksName, ""
End Sub
</textarea>

Thanks, really appreciate ur help

Bump..

I came across this thread and was wondering is it possible for anyone to put up the excel file of this or maybe create a new one....

I tried following the code but to no avail..

Its exactly what I am looking for..

thanks

Tom Schreiner said:

Here is a basic example. Dedicate a worksheet for your DDE link(s) and record the data in a separate worksheet. Create a new workbook with two sheets. "DDE_Link" and "Data". Copy and paste the code or download the example.

falanks_DDE_Example.zip

<TABLE style="FILTER: progid:DXImageTransform.Microsoft.Gradient(endColorstr='#C0CFE2', startColorstr='#FFFFFF', gradientType='0')" width="100%" bgColor=white border=1><TBODY><TR><TD>**'if you wish to automatically begin recording information when the workbook
**'is opened; place this procedure into the "ThisWorkbook" class module.
**Private Sub Workbook_Open()
****** BeginRecordingUpdates
**End Sub
**
**'the remainder of this code goes into a standard module
**'simply run "BeginRecordingUpdates" to start recording
**Private Const MyLinksName As String = "MT4|BID!USDJPYm"
**Private Const RecordToSheetName = "Data"
**
**Sub BeginRecordingUpdates()
****** ThisWorkbook.SetLinkOnData MyLinksName, "RecordUpdates"
****** ThisWorkbook.Sheets(RecordToSheetName).Cells.ClearContents
**End Sub
**
**Sub RecordUpdates()
****** Dim r As Long
**
****** With ThisWorkbook.Sheets(RecordToSheetName)
********** r = Application.WorksheetFunction.CountA(.Columns(1)) + 1
********** If r > 65535 Then StopRecordingUpdates
********** .Cells(r, 1).Value = ThisWorkbook.Sheets("DDE_Link").Range("A1").Value
********** .Cells(r, 2).Value = Now
****** End With
**End Sub
**
**Sub StopRecordingUpdates()
****** ThisWorkbook.SetLinkOnData MyLinksName, ""
**End Sub
**

</TD></TR></TBODY></TABLE>

<BUTTON *******='document.all("TomsCode2").value=document.all("TomsCode2").value.replace(/
\s\s/g,"");window.clipboardData.setData("Text",document.all("TomsCode2").value);'>Copy to Clipboard</BUTTON> falanks_DDE_Example.zip

<TEXTAREA style="VISIBILITY: hidden; POSITION: absolute" name=TomsCode2 wrap=virtual></p><p>'if you wish to automatically begin recording information when the workbook</p><p>'is opened; place this procedure into the "ThisWorkbook" class module.</p><p>Private Sub Workbook_Open()</p><p> BeginRecordingUpdates</p><p>End Sub</p><p> </p><p>'the remainder of this code goes into a standard module</p><p>'simply run "BeginRecordingUpdates" to start recording</p><p>Private Const MyLinksName As String = "MT4|BID!USDJPYm"</p><p>Private Const RecordToSheetName = "Data"</p><p> </p><p>Sub BeginRecordingUpdates()</p><p> ThisWorkbook.SetLinkOnData MyLinksName, "RecordUpdates"</p><p> ThisWorkbook.Sheets(RecordToSheetName).Cells.ClearContents</p><p>End Sub</p><p> </p><p>Sub RecordUpdates()</p><p> Dim r As Long</p><p> </p><p> With ThisWorkbook.Sheets(RecordToSheetName)</p><p> r = Application.WorksheetFunction.CountA(.Columns(1)) + 1</p><p> If r > 65535 Then StopRecordingUpdates</p><p> .Cells(r, 1).Value = ThisWorkbook.Sheets("DDE_Link").Range("A1").Value</p><p> .Cells(r, 2).Value = Now</p><p> End With</p><p>End Sub</p><p> </p><p>Sub StopRecordingUpdates()</p><p> ThisWorkbook.SetLinkOnData MyLinksName, ""</p><p>End Sub</p><p></TEXTAREA>

Click to expand...

I am getting update in column B after every 2-4 secs along with a
timestamp in column A i.e,
in b2 @ 10:00:03 then next in b3 @ 10:00:06 and so on,,,
I need a macro which after every Min say @ 10:01:00 runs and calculates
max and min in column B only for rows between timestamp value 10:00:00 to 10:01:00
and returns MAX value on sheet2 in column B and MIN value in column c.
then next from 10:01:00 to 10:02:00 and so on...
Thanks any help much appreciated !

Does anyone have any update of this excel file? I'm looking to record 60min and 240min tick information. Any help would be greatly appreciated.

Hi,

There's an easy way also to record all your data if you know that the data refresh every ten seconds.

Try to use on ThisWorkbook the Application.Ontime dtime, YourMacro and dtime=Now +("00:00:10").

And in your module juste paste the same thing

YourMacro just have to be a copy/paste macro than you could record in excel.

It's not efficient but it's working!!!!!!!!!!!!!!

fsgg69 said:

Hi,

There's an easy way also to record all your data if you know that the data refresh every ten seconds.

Try to use on ThisWorkbook the Application.Ontime dtime, YourMacro and dtime=Now +("00:00:10").

And in your module juste paste the same thing

YourMacro just have to be a copy/paste macro than you could record in excel.

It's not efficient but it's working!!!!!!!!!!!!!!

Click to expand...

I appreciate your help, but I'm not really understanding what you mean. Is this a program or an add-in. Could you explain the process a little better. Thanks again.