I really could not find a simiar problem to mine, so I highly welcome any guidance from you:
String Example: "=APP|DATA!FIELD.DETAIL" This is basically a DDE link in Excel 2003 which reads a data field from
an external stock application currently running under windows7. The application reads real-time stock data from a server and
I capture this data into Excel for some analysis. FIELD is stock symbol and DETAIL is Bid, Ask, Lot, etc. There are 10
details info for each stock symbol arranged in an Excel table. I monitor up to 20-50 stock symbols and add and delete new
sysmbols occasionally when I lose or gain focus on anyone of them. So, I am trying to make this table an automatic one so
that by changing the only-changing bit of the above string, say YHOO (for Yahoo) for the first row in my table, then
subsequent cells of the first row would be filled with automatically-updated formulas like "=APP|DATA!YHOO.Ask",
"=APP|DATA!YHOO.Bid", "=APP|DATA!YHOO.Lot" and some 7 others. The second and other rows hold other stock
symbols, but colums are identical for all rows, that is they show Bid, Ask, Lot info etc. On the same row for a Stock symbol,
then comes cells which send these DDE strings as parameters to several functions for various if-then calculations. If I
cannot automate the DDE string creation as I hope to manage eventually, then I have to manually change DDE strings each time
I replace a new stock symbol. Meanwhile I have to make sure that the functions that uses these newly-created DDE strings
accept as parameters. Current situation is, when I type manually all 20 up to 50 stocks x 10 fields = 200 up to 500 cells
then the respective functions work. However, when I try to automate the DDE strings, then the functions return
One interesting observation of mine is that if I manually type "=APP|DATA!YHOO.Ask" in
any cell of the Excel Sheet then all of a sudden the function which previously returns "N/A" actually shows the
true value. Of course this solution makes no point to get the data because eventually I would have to manually enter all DDE
strings as opposed to automatically getting them calculated.
How I make the function call to get the real-time
B1 other stock symbol
C1 another stock symbol
and seven more
The function is:
' Application|Topic!'Symbol.Field' This is the structure the Stock Program that I use sticks with
' APP|DATA!'Yhoo.Ask' Example for Yahoo Ask price
Dim Command As String
Command = "=APP|DATA!'" & Symbol & "." & Field & "'"
FunctionEvaluate = Evaluate(Command)
When I replace the cell A1 another stock symbol instead of YHOO, then I would expect the table would
show Bid, Ask, Lot etc data for the new symbol and all function calls in subsequent cells on the same row would use these new
real-time data to make and return numerous if-then calculations.
Currently, it does not because the
FunctionEvaluate above returns "N/A".
Lastly, my attempts to formulate DDE strings and get the real-time
data below failed:
=INDIRECT("APP|DATA!'" & $A1 &".Ask'")
=INDIRECT("=APP|DATA!'" & $A1 &".Ask'")
="=APP|DATA!'" & $A1
&".Ask'" this returns a string. Use of Evaluate("=APP|DATA!'" & $A1 &".Ask'") is
not recognized by Excel here
=eval("=APP|DATA!YHOO.Ask") tried to send the whole string as one parameter
in another version of FunctionEvaluate to see if it is the function call that fails, which turns out to be "yes"
Since the old-macro EVALUATE is not
recognized by Excel in Sheet calculations I had to use it within a function as presented above in FunctionEvaluate.
I think I have already tried all possible alternatives to get the job done but failed.
Thanks in advance for
all your return