DDERequest and Excel Tip

Bob Hansen · Post by **Bob Hansen** » Thu Aug 25, 2005 5:07 pm

I have frequently tried DDE with Excel and failed to get data.
I had done Searches on the forum and tried to follow what others had done.
I often got the messages DDE_SERVICE_INVALID or DDE_TIMED_OUT.

Not having time to dig in deeper I quickly went away and found another solution.
But today I finally cracked the code.
So I thought I would include my discovery here to save time for others.
---------------
This is the basic syntax from the Help section:
DDERequest>Server,Topic,Item,Result,Timeout
---------------
This is the syntax I frequently found in the forum for Excel:
DDERequest>Excel,yourfile.xls,RowColumn,yourVariable,HowLongToWait
---------------
This is the syntax that I would suggest for Excel:
DDERequest>Excel,yourfile.xls,R(RowNumber)C(ColumnNumber),yourVariable,HowLongToWait
Ignore the ( ) symbols in line above, for reference only to show the letters R and C are needed with a number.

Example to get data from cell D21:
DDERequest>Excel,yourfile.xls,R21C4,yourVariable,HowLongToWait
---------------
Things leading to this syntax:
1. Normal references to cells in Excel are ColumnLetter-RowNumber, such as G5, D21, B3, etc.
2. Syntax needs the Row before the Column, opposite sequence of normal reference. Tendency was to put in G5, D21, B3 which was wrong and led to failures.
3. Syntax needs R/C before the RowNumber and the ColumnNumber. Tendency was to enter R5CG, R21CD, R3CB.
4. Syntax needed calls for the ColumnNUMBER, NOT the ColumnLETTER that is used in normal reference. That was the biggest discovery that solved this for me. I now need to convert G=7, D=4, B=2. So now I see these must be entered as G5=R5C7, D21=R21C4, B3=R3C2.

Success!
---------------
Once you see it explained it may seem quite obvious, but I have struggled with this too often, and hope this explanatioin will save work for someone else.

Also not mentioned was that spreadsheet must be opened first for DDE to work. This was probably obvious to those who have worked with DDE before, but this was new to me., So, for new DDE users, note that lines like these should preceed the DDERequest line.
Run Program>drive\path\excel.exe drive\path\yourfile.xls
WaitWindowOpen>Microsoft Excel*

I have not tried this with DDEPoke, but suspect the same rules will apply.
Perhaps the Help section could provide something like this example in the Help section.
And/Or this posting could also be placed in the forum for Scripts and Tips?

Post by **Marcus Tettmar** » Thu Aug 25, 2005 5:28 pm

Bob, I wish you had asked, 'cos I always thought it was obvious! Shows the way my mind works I guess!

Bob Hansen · Post by **Bob Hansen** » Thu Aug 25, 2005 7:15 pm

Thanks Marcus, but sometimes I need to force myself to do my own understanding.
Sometimes a challenge becomes personal vs. technical, and I refuse to be beaten.
Looking back I have to wonder why this one was so difficult for me.

Ignoring the syntax, and looking at the values, I still do not know how one determined that EXCEL should be used for the Server.
At times I used drive\path\excel.exe. Who knew the format was RowNumberColumnNumber for Item?
I checked Excel Help and it was no help. I have checked some sites on DDE, but found little to help.
If it wasn't for the forum messages I would probably have given up and asked for help.

It would be good to find a reference that filled in the blanks for various programs, listing the values for Server, Topic, Item.
I am sure that at some point I will want to add/copy a value from Access, and then I will need the values for Server, Topic, and Item again.
I suppose it will be something like this:
DDERequest>Access,myDatabase.mdb,qryResultRowColumn,myVariable,myTime ?
Who knows, how to find out?

Anyway, I now have one more tool that I can use with Excel. Onward and forward.

Thanks for the support.

Post by **Marcus Tettmar** » Thu Aug 25, 2005 7:37 pm

You'll be lucky to find documentation for DDE topics at all, certainly in any Microsoft apps, as DDE has largely been forgotten about and discontinued. It's a bonus that DDE still works in Excel at all. Don't expect it to be there with each new version of office!

Bob Hansen · Post by **Bob Hansen** » Thu Aug 25, 2005 9:25 pm

I did find this link:
http://www.angelfire.com/biz/rhaminisys/ddeinfo.html
that has more than I really want to know, but does give some insight into DDE past and future.

Too much tech stuff.
Instead of reading, I may wait for the movie to come out.

pgriffin · Post by **pgriffin** » Sun Aug 28, 2005 10:10 pm

I have also used DDE quite often. It works fine, always a few stumbling blocks. When/if DDE goes away in Excel, just export to an Access database and use VBscript to get at the data. MUCH faster, much more is possible as far as data manipulation.

Luciano · Post by **Luciano** » Mon Aug 29, 2005 9:49 am

Bob, you have made me a happy man with jour DDERequest syntax struggling. Sometimes I have the impression that the software experts (like you) know everything, but this prove that there also have too learn the correct syntax, and this is not always so obvious.
You are a lucky man, in this specific case of the DDERequest syntax, that you are using an English version of Excel. The people using a different language version of Excel have also to change the letter R (from Row) and C (from column) into the syntax of the local language. For a Dutch version of Excel (my version) one has to use R (from Rij) and K (from Kolom). So Bob, your suggested syntax for a Dutch version of Excel become DDERequest>Excel,yourfile.xls,R(RowNumber)K(ColumnNumber),yourVariable,HowLongToWait

Support, can you report this in the help file? Otherwise, it can takes a long time before one finds the correct syntax for a different language version of Excel.

Greetings.

Luciano

support · Post by **support** » Mon Aug 29, 2005 1:12 pm

Luciano wrote:Support, can you report this in the help file? Otherwise, it can takes a long time before one finds the correct syntax for a different language version of Excel.

But this is about Microsoft Excel. It is not specifically about DDERequest. So where would we put it in the help file? Should we write about every piece of software in the world that uses DDE?

Bob Hansen · Post by **Bob Hansen** » Tue Aug 30, 2005 2:53 am

Great timing Luciano.

I just started doing some work, remotely, for someone in Holland. He had mentioned that I could use English and he would translate visible text, like labels, etc. for the end user.

I do not anticipate the need to use DDE for that project, but will use Macro Scheduler. But it is good to know about this Excel anamoly.

I will try to remember that I saw this hint somewhere. It may come in handy.