DDE link syntax

[stack]

I am using a DDE server to pull stock/option quotes into an excel spreadsheet. I am able to call to the server and symbols by using this formula:

=STIDDE|Q!'MSFT,1'

STIDDE = server
Q! = quotes
MSFT = Microsoft
1= field type (1 = Last Price, 2 = Bid, 3 = Ask, etc)

My problem comes when trying to automate the formula so that the 'field type' cells change with a change in the stock symbol.

Example:
Stock Symbol MSFT is in cell C27, the price (=STIDDE|Q!'MSFT,1') in cell C28.

One of my many attempts was changing C28 to =STIDDE|Q!'$C27&",1"' (wrong! )

What is the correct syntax to add the result of cell C27 into the formula in C28?

[NBVC]

I am still not sure if this would work.. I don't work with DDE links.. so maybe at least give it a try.


=INDIRECT("STIDDE|Q!'"&$C27&",1'")

[stack]

Thanks for the quick try, unfortunately error = #REF!

[NBVC]

Maybe since it is an external source and Indirect() doesn't work on external sources, you'd be willing to try a free adding from here (many of us Excellers have this addin as a must have):

Then try:

=INDIRECT.EXT("STIDDE|Q!'"&$C27&",1'")

but not guaranteeing that it will work.. but it is the same as Indirect except deals with closed workbooks and possibly other external sources....

[stack]

I installed, then embedded morefunc into the workbook, still getting the #REF! error

I don't understand why =STIDDE|Q!'$C27&",1"' won't work. Does the formula not equal the same as =STIDDE|Q!'MSFT,1' (of course with C27 = MSFT)?

Would it be possible to create a macro that would output the correct formula to another cell, then run that formula? Something along the lines of ="=STIDDE|Q!'"&$C27&",1" into another cell, then executing it?

Thanks again for the help

[NBVC]

You need to somehow, indirectly refer to the cell contents of C27 to get it to work and Indirect() or Indirec.Ext(), theoretically, should do that... but the DDE thing is confusing...

Do a google search of DDE links and Excel and you may find macros, etc...

[stack]

I didn't find much relating to DDE link macros that apply to my issue. The only info that was close (THIS) draws on using relative/offset referencing, but I think this has to do more with the macro the guy was trying to build and not the formula to retrieve the quotes.

Going back to my previous workaround attempt to output the correct formula to another cell; I was able to then copy the formula then paste the values into a third cell, then execute. The problem with creating a macro with these steps is that the values pasted are static to the original cell source (MSFT), thereby bringing me back to the original problem (argh ).

E27 ="=STIDDE|Q!'"&$C27&",1'"

Please Login or Register  to view this content.

This way seems a bit backwards, but I am at a loss for other ideas. Is there a way to manipulate the macro to simply execute the cell contents as is without using the indirect function?

[NBVC]

I am sorry, I am not a VBA person really... so I hope someone will join in and help you....

If nobody helps in a few hours, please repost in the Programming forum with a link to this post.

[stack]

Thanks again for your help, I am learning a lot from all the googling at the least