By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
429,480 Members | 771 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 429,480 IT Pros & Developers. It's quick & easy.

VBA code for excel DDE link formula

P: 1
Hi all, newbie here, but having a go

I am trying to build a stock watchlist in excel 2007 with a dynamic link to a DDE server (paid for from a broker).There is no add-in or plug-in, I just CTL ALT & drag each code from a watchlist in the program I am using and place it in a cell in excel, however can only choose one data field at a time. There are 14 data fields and over 150 codes in my list which makes 2100 cells. (My guess is about 3 days work)

The 14 data fields are the same for each column, however the stock codes change. I have a list of stock codes in column A, all data fields are in columns to the right.

I would like to just enter the stock code in column A (A3) and enter each DDE data field (e.g. lastprice, open, cose, high etc) in subsequent columns to the right and have it lookup the stock code in cell A3 and combine with the first 2 parts of the formula, based on the code in column A, rather than entering each cell individually.

Is it possible to write a macro or vba code to create the cell formula so I can just fill down and save myself 3 days work?


The formula syntax for the DDE server in excel is :

=PROGRAMNAMElDATAFIELD!STOCKCODE

example

=MISDATAlLASTPRICE!BHP.ASX


The way I understand it, a DDE formula will not read a cell value as part of the string and so I was wondering if there was a way to concatenate the first 2 parts of the formula with the stock code in the following format:

=MISDATAlLASTPRICE!BHP.ASX

where BHP.ASX has been looked up from column A

eg.

=MISDATAlLASTPRICE!$A3

I have tried brackets, quotes, etc but it does not seem to work. I have also tried

="="MISDATAlLASTPRICE&$A3 and the concatenate function, which does not work either.

I was thinking maybe there was some vba code that I could use to combine the two parts (=MISDATATAlLASTPRICE! with $A3) and paste the formula in a cell so excel reads it as a formula.


Could anyone help please, I'd be very greatful
Sep 12 '07 #1
Share this Question
Share on Google+
1 Reply


P: 1
Hi Barnzie

I have been trying to do exactly the same thing.

No Luck

If you have found a way it would be great to hear.

Cheers

John
Oct 3 '07 #2

Post your reply

Sign in to post your reply or Sign up for a free account.