Susan Harkins
ssharkins at gmail.com
Tue Aug 26 16:45:45 CDT 2008
RE: Know a DDE expert?Okay, this is from a reader -- on DDE and I haven't a
clue... my first suggestion is to check the actual data, but you know how
helpful that is...
Hi Susan,
The server at issue is a broker www.thinkorswim.com.
The following, placed in an Excel cell, works 100% of the time:
=TOS|BID!'.DIAHI' (TOS=server name, BID = datafield, .DIAHI=option symbol
aka OPRA symbol)
Trying to obtain the same data in Access VBA (below) works about 95% of the
time.
I've tried adding extra loops, putting a time delay in and it didn't fix it.
The broker (barely) supports the spreadsheet interface and won't help with
this code problem - I asked.
I'm using 3 DDE functions: DDEInitiate, DDERequest and DDETerminate.
I *think* doing it the Excel way is equivilent to doing it in VB with the
DDE function in a ControlSource property. But that has to be done at design
time only. The ControlSource property becomes read only.
I need to retrieve this data for dozens of symbols that are always changing.
The way the server works, if data is not available it returns a "N/A",
otherwise it returns a number.
I've been trying to solve this for several days and doing a lot of testing.
I might have data on 20 symbols I'm trying to retrieve and Access will
return data on 17. Excel will do all 20.
On the 3 that don't work in Access, Excel will sometimes return a "N/A", but
then a split second later change it to a valid number. The DDE
communication in Excel is continuous.
When Access fails, I tried making the loop try 100 or 200 times, but once it
fails, Access just can't retrieve the value.
Another interesting observation. If Access fails on a symbol, then I do it
in Excel and it succeeds, Access will then also succeed. It seems the data
is being cached(?) and then becomes available to Access.
I might be able to create a workaround if I understood better what is
happening. I don't really understand why Access fails for only certain
symbols but Excel works for all.
Brooks
-----Original Message-----
From: Susan Harkins [mailto:ssharkins at gmail.com]
Sent: Tuesday, August 26, 2008 3:07 PM
To: brooks at rimesrv.net
Subject: Re: Know a DDE expert?
Dear Brooks;
I don't know whether I can help you or not, but you can certainly ask.
Regards,
Susan Harkins
----------------------------------------------------------------------------
From: Brooks Rimes [mailto:brooks at rimesrv.net]
Sent: Tuesday, August 26, 2008 9:55 AM
To: Jody Gilbert
Subject: RE: Know a DDE expert?
Hi Jody,
DDE is the only data interface that my broker supports
(www.thinkorswim.com).
http://www.thinkorswim.com/notices/Release_083006.html
The problem is that it work great in Excel but less reliably in Access
with VBA code. I have code and examples.
Could I try writing to Susan?
Thanks,
Brooks
-----Original Message-----
From: Jody Gilbert [mailto:jody.gilbert at cnet.com]
Sent: Tuesday, August 26, 2008 9:03 AM
To: Brooks Rimes
Subject: RE: Know a DDE expert?
Brooks,
I checked with Susan Harkins, my Office/VBA expert, and she replied:
Wow... that's a really old technology (Windows 3). You might want to
ask if they're looking for information on the following -- or something
else.
Dynamic Data Exchange - (DDE, originally Dynamic Data Linking, DDL) A
Microsoft Windows 3 hotlink protocol that allows application programs to
communicate using a client-server model. Whenever the server (or
"publisher") modifies part of a document which is being shared via DDE, one
or more clients ("subscribers") are informed and include the modification in
the copy of the data on which they are working.
If it's the above, they can ask me, but I doubt I'll remember and I'm
not running any Windows 3 systems to check. If they don't need DDE, but are
just using an older term, anybody with some Office development experience
can probably help.
Are you working on an older system? I guess we need to know what
you're working on before figuring out what or who to suggest!
Best,
j
-----Original Message-----
From: Brooks Rimes [mailto:brooks at rimesrv.net]
Sent: Monday, August 25, 2008 6:21 PM
To: Jody Gilbert
Subject: RE: Know a DDE expert?
Thank you, it's appreciated.
Brooks
-----Original Message-----
From: Jody Gilbert [mailto:jody.gilbert at cnet.com]
Sent: Monday, August 25, 2008 2:46 PM
To: brooks at rimesrv.net
Subject: RE: Know a DDE expert?
Not off the top of my head... Let me check around.
j
-----Original Message-----
From: brooks at rimesrv.net [mailto:brooks at rimesrv.net]
Sent: Saturday, August 23, 2008 3:03 PM
To: Jody Gilbert
Subject: Know a DDE expert?
Hi Jody,
Do you know anyone who might be able to answer an advanced DDE
question?
Thank you.
Brooks Rimes
Access MCP
Internal Virus Database is out of date.
Checked by AVG - http://www.avg.com
Version: 8.0.138 / Virus Database: 270.5.5/1569 - Release Date:
7/23/2008
1:31 PM