Solved

DDE between Word and Excel

Posted on 1998-01-18
16
599 Views
Last Modified: 2008-02-01
I wish to launch an Excel 4 file from a Word 6 macro. On doing this control is now with Excel and the Word macro never completes. I am using DDE.

I think I need to open a channel from Excel back to Word and send the appropriate commands. The problem is that I wish this to work on any PC and therefore need to tell the DDEInitiate method where Word is. I cannot find a way of returning the pathway to the folder/directory where Word resides.
0
Comment
Question by:jsteed
  • 8
  • 5
  • 3
16 Comments
 
LVL 25

Accepted Solution

by:
dew_associates earned 100 total points
ID: 1607970
Any command sent to Microsoft Excel over a dynamic data exchange (DDE) channel must be a command-equivalent macro function. "Command-equivalent" means that the function has an equivalent choice on one of the Microsoft Excel menus, such as choosing the Close command from the File menu or choosing the Delete command from the Edit menu. Command-equivalent
functions are all designed to return a value of TRUE.
 
Commands such as GET.OBJECT() or DIRECTORY() are designed to return information other than TRUE. These commands are not accessible on the Microsoft Excel menus and therefore cannot be executed across a  DDE channel. In addition, you cannot run a command in DDE if that command is added to the a menu with an add-in macro.
 
If another application must execute a function that has no command equivalent, the functions can be included in a Microsoft Excel macro that can then be run by the other application.
 
For example, you could write the following macro in Microsoft Excel:
 
   A1:    =DIRECTORY()
   A2:    =CREATE.OBJECT(2,A1,0,0,E5)
   A3:    =RETURN()
 
Define this macro with the name "Testing" and save the macro sheet as TEST.XLM. (The first line of this macro returns the name of the current directory. The second line creates a rectangle that extends from cell A1 to E5.)
 
The DDE client can then execute the RUN() command to run the Testing macro before requesting the data from cells A1 and A2 of TEST.XLM. The syntax for this will vary with the application being used as the DDE client.  As an example, here is the macro code to accomplish this from another instance of Microsoft Excel:
 
A1:  chan=INITIATE("Excel","Test.xlm")
A2:  =EXECUTE(chan,"[run(""Test.xlm!testing"")]")
A3:  directory=REQUEST(chan,"R1C1")
A4:  ObjectID=REQUEST(chan,"R2C1")
A5:  =TERMINATE(chan)
A6:  =RETURN()
 
Explanation of Macro Code
-------------------------
A1: The INITIATE() command opens a channel from this instance of Microsoft Excel to the other.
 
A2: The EXECUTE() command runs the testing macro in the other instance.
 
A3: The first REQUEST() gets the information from cell A1 of the testing macro sheet, which will have the name of the current directory. The value is stored in the name "directory."
 
A4: The second request gets the object identifier returned by the
CREATE.OBJECT() function and stores it in the variable named ObjectID.
 
A5: TERMINATE() closes the DDE channel.
 
NOTE: You must use R1C1 notation in any DDE conversation with Microsoft Excel. The double sets of quotation marks are necessary because they are part of the execute text string.
 
I hope this helps you!

Dennis
0
 

Expert Comment

by:sgb062497
ID: 1607971
My understanding of this question (via experts-exchnage and direct conversation) is that Word has successfully launched Excel and that Excel is up and running correctly. The nub of the problem is that there remains one command in the Word macro which needs to run to termination.

Since Excel is now up and running, Word is in the background and its macro handler (or whatever) is getting impatient at being ignored.

I think jsteed wants to get word to execute the rest of the macro and then to go to sleep "quietly".

Again (I think) he can do this on PCs where he knows the full path of WINWORD.EXE, but he is trying to make this computer layout independent. So how can he either find the path to WINWORD (Win311 or Win95) or send a message to the already open application to get it to activate and complete.
0
 
LVL 25

Expert Comment

by:dew_associates
ID: 1607972
sgb, read the above please!  Your "whatever" is called DDE channel.
0
 

Expert Comment

by:sgb062497
ID: 1607973
I have read the above, and admit that i don't fully understand it - never having used DDE myself.

However I think it possible that your answer misses the point.

jsteed has successfully used DDE from within Word to launch Excel and because that has an AUTORUN macro, Excel is busy doing its stuff.
Meanwhile the Word macro has one command left to execute, namely "Teminate the DDE link" at which point the macro will terminate. The Word macro does not want any data back from Excel.
Since in its natural state Word is waiting to continue, Excel (which is required to stay running) must do something to kick word into temporary life to let Word's macro terminate nicely.
What jsteed is doing to try to terminate the Word macro is a DDE channel back to word. He can do this successfully on "his PC" where he knows the full path to Winword, but that it doesn't work if he can't "find" Winword which happens when he tries the software on other peoples differently configured PCs.

I don't see how your answer addresses this. (If it does it is me not understanding - are you able to expand on your answer?)

(jsteed really ought to be writing these comments - not me - but I'm curious. thank you DEW)

0
 
LVL 25

Expert Comment

by:dew_associates
ID: 1607974
Well sgb, you seem to have a handle on JSteed's problem, how about some dialogue from you here?
0
 

Author Comment

by:jsteed
ID: 1607975
I am very grateful to sgb for explaining my problem so clearly. Dennis your answer does not appear to get to the nub of the problem - which as sgb has detailed is that the Word macro does not complete and terminate the DDE channel. Excel runs its auto_open macro and the Word macro is just left hanging around. The Word tab on the Windows 95 taskbar starts to flash and Word eventually presents a message stating that it is waiting for a task in another application to finish. So I thought that perhaps I needed to open another channel, this time back from Excel to Word and instruct the Word macro to finish.

Thank you both for your help with this problem.
0
 
LVL 25

Expert Comment

by:dew_associates
ID: 1607976
Without making this anymore confusing than it appears, let's see if I understand your problem as well as you do.

At present you want to execute a word 6 macro to prepare a document, and as part of the running of the word 6 macro is an imbedded macro for excel, where excel is called upon to gather data, make calculations, place the data in the word 6 document, terminate and return control back to Word 6. Correct?

Currently, the macro hangs after the excel macro complete's its operations, correct?
0
 

Author Comment

by:jsteed
ID: 1607977
I am very grateful to sgb for explaining my problem so clearly. Dennis your answer does not appear to get to the nub of the problem - which as sgb has detailed is that the Word macro does not complete and terminate the DDE channel. Excel runs its auto_open macro and the Word macro is just left hanging around. The Word tab on the Windows 95 taskbar starts to flash and Word eventually presents a message stating that it is waiting for a task in another application to finish. So I thought that perhaps I needed to open another channel, this time back from Excel to Word and instruct the Word macro to finish.

Thank you both for your help with this problem.

0
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 

Author Comment

by:jsteed
ID: 1607978
Firstly apologies for repeating my last comment - my error.

Thank you for your comment. You haven't quite got the picture.

To begin at the beginning - I am creating a resource disk to help people who are starting up childcare clubs. There is a Word document which acts as a sort of contents page. On this contents page there are a number of labelled MacroButtons - each of them takes the user to an example document (for example a sample business plan or job description). One such button is designed to open a sample cashflow forecast in Excel. It is this that is causing the problem. Excel is NOT called upon to return data to the word 6 document. The problem is that the Word macro having started Excel and opened the sample file, fails to terminate as control remains with Excel.

0
 
LVL 25

Expert Comment

by:dew_associates
ID: 1607979
Okay, I believe I understand where your coming from in all of this, and I believe as well that I know wherein the problem lies. Let's make sure that we are both on the same page so to speak as to the use of Word and Excel as it pertains to DDE.

First, DDE enables you to use such programs as Access, Excel, Power Point as well as a number of other data base relational programs within Word to convert (if you will) defined data into a presentation format. Here is the basic principles of the use, as an example, of Word and Excel employing dynamic data exchange. For the purposes of simplicity I've shortened the procedures considerably.

Background: An entity desires to publish a letter in Word to its sales representatives that indicates sales results for the past 6 months as well as necessary sales for the coming 6 months and then includes individual results for the recipient. The previous results are available through an access data base as are the individual results, while necessary projections were calculated using an Excel workbook.

A word document is assembled that includes several macros, beginning from date and recipient information, through the insertion of access data, excel data and closes appropriately.

The document (in very simple terms) would look like this:

Word document started,
macro inserts data and requests choice of recipients
recipients info entered
macro terminates
macro/access data opens
data assembled and entered into word document
macro/access terminates
word continues with additional letter body
macro/excel opens
excel calculates data for necessary sales
excel inserts data in appropriate format in word
excel terminates
word continues body of letter
macro/access opens
inserts recipients previous data
macro access terminates
word closes document

Now, regardless of whether your running an Excel macro from within Word, or a Word macro from within Excel, or access or any other DDE reference, the principles are the same, eg:

A1: chan=INITIATE("Excel","Test.xlm")
A2: =EXECUTE(chan,"[run(""Test.xlm!testing"")]")
A3: directory=REQUEST(chan,"R1C1")
A4: ObjectID=REQUEST(chan,"R2C1")
A5: =TERMINATE(chan)
A6: =RETURN()

A1: The INITIATE() command opens a channel from one instance of a program to another.
A2: The EXECUTE() command runs the macro in the second instance opened. The second instance being the second macro called.
A3: The first REQUEST() gets the information.
A4: The second request gets the object identifier that's called as part of the routine, CREATE.OBJECT() function and stores it in the variable named ObjectID.
A5: TERMINATE() closes the DDE channel and returns control to the originating program, in your instance, Word.

Word can't resume control until Excel releases control, and Excel won't release control until told to do so.
0
 

Expert Comment

by:sgb062497
ID: 1607980
DEW, your last sentence makes sense and in this context is frustrating, since the Excel has no intention of releasing control and going back to Word.

Does this mean that DDE is the wrong thing to use to launch Excel and that a Shell would be better? If not what? If a shell we get back to the question of how does one find out where Excel (rather than winword) is to be able to launch it.

I think I'll go and ask some of the "softies" in our company if they have a book on DDE - sounds like I could learn a lot from reading it.  :-)

0
 
LVL 25

Expert Comment

by:dew_associates
ID: 1607981
sgb and Jsteed:  I think the problem is is that your not calling upon Excel to do anything. Your sending your user to a sample document to view it and not asking excel to perform a task and then return control eg: Excel may be brining up and excel document, but it's actually not performing anything, therefore there's no control for it to relinquish and the operation times out. Since you obviously can create the cash flow forecast in excel, however you are only "showing" the user vis-a-vis the macro'd document what can be done with no variables for excel to calculate. Instead, create a look-alike document in word and call that from the macro. It's difficult here not being able to actually see what your trying to do, and on top of that Excel 4 and Word 6 leave a lot to be desired once you've transitioned to Office 97 or Office Pro where many of these features, including power point have been built in.
0
 

Author Comment

by:jsteed
ID: 1607982
I think DEW is right - the Word macro should require the Excel file to return data to enable it to complete. But I think this is only necessary because of the cumbersome auto-open macro
in the Excel file.

I have found another way around the problem. The Word macro launches a simple Excel file (ie. no auto-open macros). This new file has a button on it from which the user is invited to launch the complex cashflow file. It works a treat.

Thank you DEW and sgb for your help
0
 
LVL 25

Expert Comment

by:dew_associates
ID: 1607983
JSteed, how about sharing what you have found. It may, indeed, help some other user visiting this site!
0
 

Author Comment

by:jsteed
ID: 1607984
DEW, I'm new here. How do I share?
0
 
LVL 25

Expert Comment

by:dew_associates
ID: 1607985
JSteed, since others visit these previously answered questions, please post how you accomplished this, "I have found another way around the problem. The Word macro launches a simple Excel file (ie. no auto-open macros). This new file has a button on it from which the user is invited to launch the complex cashflow file. It works a treat."

Thanks,
Dennis
0

Featured Post

How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

Join & Write a Comment

Recently Microsoft released a brand new function called CONCAT. It's supposed to replace its predecessor CONCATENATE. But how does it work? And what's new? In this article, we take a closer look at all of this - we even included an exercise file for…
My experience with Windows 10 over a one year period and suggestions for smooth operation
The viewer will learn how to simulate a series of coin tosses with the rand() function and learn how to make these “tosses” depend on a predetermined probability. Flipping Coins in Excel: Enter =RAND() into cell A2: Recalculate the random variable…
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

759 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now