Using DDE in VBA

[CPWilson]

Hi,

I am accessing DDE VIA a cell, for example "=TOS|LAST!AAPL". Is it possible to do the same in VBA placing the result directly into a variable?

Thanks!
<input id="gwProxy" type="hidden"><!--Session data--><input *******="jsCall();" id="jsProxy" type="hidden">

 

[Legacy 98055]

<A HREF="http://msdn.microsoft.com/en-us/library/ms648712(VS.85).aspx" TARGET="_blank">Dynamic Data Exchange Management Library</A>

The you will need to create your own client. Of course you will gain the best performance by creating it in C, but as far as I know, a VB dll is plenty fast enough for anything DDE can throw at it. I don't know about VBA's performance using the above library. It might do. A problem: the documentation is targeted at C programmers. I am familiar with this library and will work with you over time if you want to pursue this.It is a particular interest of mine.

As far as native methods for grabbing DDE data and placing it directly into variables...

MyVar = Application.DDERequest(Args...)

Unfortunately, you will still be under the same contraints and have no way of determining when and if data has actually changed on the server let alone if there is any data available to begin with. The above library is very fast and recieves the data immediately as it is sent.

If you are willing to settle for a loop, you could use Application.DDERequest by sending all of your requests, assigning each result to an array, and then post it to a range in one shot. That may do for you...

 

[Legacy 98055]

To clarify. Remove all of your links and try something like this...

Sub StartHere()
    Dim Channel As Long, Items(2), TotalResults
    
    'assign your items to the items array here
    '
    '
    '
    
    Channel = Application.DDEInitiate("AppName", "TopicName")
    TotalResults = GetDDEUpdate(Channel, Items)
    Application.DDETerminate Channel
End Sub

Sub GetDDEUpdate(Channel As Long, Items() As String)
    Dim Ret, FinalRet
    
    loop through items
        Ret = Application.DDERequest(Channel, Items(X))
        assign ret to FinalRet
    end loop
    
    GetDDEUpdate = FinalRet
End Sub

 

[smartsource]

Found when setlinksondata calls a procedure updating any item from the DDE server|topic Excel immediately refreshes all items from server|topic in the grid. Solves the data integrity issue and the test file updates in 10ms.

Sub Force_DDE_Refresh()
Dim ddechan, Monitor
ddechan = Application.DDEInitiate(app:="Server", topic:="topic")
Monitor = Application.DDERequest(ddechan, "any_valid_item")
Application.DDETerminate ddechan
End Sub

Sub Watch_Links()
    Dim aLinks As Variant
    i As Long
    aLinks = ActiveWorkbook.LinkSources(xlOLELinks)
    If Not IsEmpty(aLinks) Then
        For i = 1 To UBound(aLinks)
        '  Filter the links for the server|topic you want to force update 
        ActiveWorkbook.SetLinkOnData aLinks(i), "Force_DDE_Refresh"
        Next i
    End If
       
End Sub


'Module Used for precise timing
Option Explicit
Public Const start As Boolean = True 'used for Stopwatch
Private Declare Function QueryPerformanceFrequency& Lib "kernel32" (x@)
Private Declare Function QueryPerformanceCounter& Lib "kernel32" (x@)
Private Type SYSTEMTIME
        wYear As Integer
        wMonth As Integer
        wDayOfWeek As Integer
        wDay As Integer
        wHour As Integer
        wMinute As Integer
        wSecond As Integer
        wMilliseconds As Integer
End Type

Private Declare Sub GetSystemTime Lib "kernel32" _
   (lpSystemTime As SYSTEMTIME)
   
Public Function Timems() As String
    Dim sAns As String
    Dim typTime As SYSTEMTIME
    On Error Resume Next
    GetSystemTime typTime
    sAns = typTime.wYear & "-" & typTime.wMonth & "-" & typTime.wDay & " " & typTime.wHour & ":" & typTime.wMinute & ":" & typTime.wSecond & "." & typTime.wMilliseconds
    Timems = sAns
End Function

 

[Kolobok]

Hi, guys.<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
I know this is a rather old thread, but I’m currently looking to do the same thing – update DDE via VBA only. Have you managed to resolve this? Does one need to have links in cells to trigger SetLinkOnData macros? And what about data integrity? If one uses SetLinkOnData method, does Excel update the values in the cells quick enough to reflect the latest change? And if one SetLinkOnData call makes “Excel immediately refresh all items” (again, is there no delay here?), then does one needs to assign SetLinkOnData to one cell only, since the queued updates from other links would just trigger macros unnecessarily?<o></o>
Many thanks<o></o>
<o></o>

 

[comedoo]

Hi, guys.<!--?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /--><o></o>
I know this is a rather old thread, but I’m currently looking to do the same thing – update DDE via VBA only. Have you managed to resolve this? Does one need to have links in cells to trigger SetLinkOnData macros? And what about data integrity? If one uses SetLinkOnData method, does Excel update the values in the cells quick enough to reflect the latest change? And if one SetLinkOnData call makes “Excel immediately refresh all items” (again, is there no delay here?), then does one needs to assign SetLinkOnData to one cell only, since the queued updates from other links would just trigger macros unnecessarily?<o></o>
Many thanks<o></o>
<o></o>

To solve this you can use concatenate formula like:

.Range("B4").Formula = _
         "=CONCATENATE(Trade|Ult!WINQ12,""/"",Trade|Qng!WINQ12,""/"",Trade|Ofc!WINQ12,""/"",Trade|Ofv!WINQ12)"