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 "N/A".
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.
I make the function call to get the real-time data is:
B1 other stock symbol
C1 another stock symbol
and seven more
The function is:
Function FunctionEvaluate(Symbol, Field)
' 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)
If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
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
=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"
=SUBSTITUTE("=APP|DATA!'xx.Ask'","xx",$A1) or Evaluate(SUBSTITUTE("=APP|DATA!'xx.Ask'","xx",$A1))
old-macro EVALUATE is not recognized by Excel in Sheet calculations I had to use it within a function as presented above in
I think I have already tried all possible alternatives to get the job done but failed.
Thanks in advance for all your return