Using Send DDE to run an Excel macro (Windows)

Using Send DDE to run an Excel macro (Windows)

Post by Bill Merric » Sat, 12 Jan 2002 10:09:23



Does anyone know how "Send DDE Execute" script step (in FM5 for Windows) can
be used to run a macro in Excel? I have the Service Name and Topic figured
out but can't seem to get the Command text to do anything but paste itself
into a cell on the spreadsheet. For example:
Application.Run "TestFile.xls!TestMacro1",
simply pastes that text into the current cell in excel.
 
 
 

Using Send DDE to run an Excel macro (Windows)

Post by Greg » Sat, 12 Jan 2002 21:08:14


Bill
As far as I know you can't use DDE Execute. Instead you use a Send Message
script step to open the relevant Excel file. Then, in Excel, write a
Workbook_Open() macro attached to the Workbook object in Excel which will do
whatever you want done and run automatically when the Excel file opens.
Get back to the group if you have trouble with the Send Message script step.
Good luck.
Greg


Quote:> Does anyone know how "Send DDE Execute" script step (in FM5 for Windows)
can
> be used to run a macro in Excel? I have the Service Name and Topic figured
> out but can't seem to get the Command text to do anything but paste itself
> into a cell on the spreadsheet. For example:
> Application.Run "TestFile.xls!TestMacro1",
> simply pastes that text into the current cell in excel.


 
 
 

Using Send DDE to run an Excel macro (Windows)

Post by MkG » Mon, 14 Jan 2002 11:20:56


Bill,

I am sending a DDE execute to run a MS Word macro using the following FM
execute:

[Toolsmacro.name = "billsmacro", . Run]

It might also work in Excel.

PS. Don't forget to open the Excel file first using the Send message command...

Good Luck

Mike Graziano

 
 
 

Using Send DDE to run an Excel macro (Windows)

Post by Bob Osol » Tue, 15 Jan 2002 07:24:02




Quote:> Bill,

> I am sending a DDE execute to run a MS Word macro using the following
> FM execute:

> [Toolsmacro.name = "billsmacro", . Run]

> It might also work in Excel.

Mike and I have corresponding off line about this (Hi Mike!) and have
discovered some of this stuff breaks over different Office versions, so
major experimentation is the name of the game.

[run("macroname")]

worked for me in Office 2k Excel, but breaks in Word. Remember to store
the macro in the current file (not in the Personal Macro Workbook).

Let us know what works, what version you are on etc. Questions on this
stuff do appear here from time to time - it might be worth making a
suitable FAQ entry if we can jointly get some cross-version info on what
works and what doesn't.

--
Regds, Bob Osola

 
 
 

Using Send DDE to run an Excel macro (Windows)

Post by Bill Merric » Wed, 16 Jan 2002 02:27:49



Quote:> Mike and I have corresponding off line about this (Hi Mike!) and have
> discovered some of this stuff breaks over different Office versions, so
> major experimentation is the name of the game.

> [run("macroname")]

> worked for me in Office 2k Excel, but breaks in Word. Remember to store
> the macro in the current file (not in the Personal Macro Workbook).

> Let us know what works, what version you are on etc. Questions on this
> stuff do appear here from time to time - it might be worth making a
> suitable FAQ entry if we can jointly get some cross-version info on what
> works and what doesn't.

> --
> Regds, Bob Osola

Thanks all for the help.  

As Greg suggested I have been able to set the macro to run on opening of my
Excel 2000 file by simply using the "send message" script.  This will probably
be enough to suit my purposes since I am only trying to create an output file
in excel for distribution.  

However, this method requires that the Excel file be closed and reopened if I
wish to run the script/macro multiple times.  Therfore, using "send dde" on an
already open file would have advantages.  But I still cannot get any of your
suggestions to work.

I think my problem may be syntax.  In the send dde script definition box
whenever I specify any commands in the "Commands" text box it simply pastes
the command text (minus the parantheses) into whatever cell I happen to be in
in Excel.  For example:

[run("macroname")]

pastes: [run"macroname"] into excel.

I've tried any number of commands, different brackets, quotes etc. with the
same result.  I'm probably missing something quite obvious.

Any thoughts?

Bill

 
 
 

Using Send DDE to run an Excel macro (Windows)

Post by Bob Osol » Wed, 16 Jan 2002 05:09:42




Quote:> I've tried any number of commands, different brackets, quotes etc.
> with the same result.  I'm probably missing something quite obvious.

> Any thoughts?

What worked for me before in Office 2K on W2K and (currently) in Office XP
on Win XP was as follows (just confirmed this before posting):

- Recorded a new macro in a new worksheet called BobTest
- Chose option "Store in current workbook"
- tested the macro in Excel

Then in FM, I created a new script step thus:

Service Name: Excel                (this is always just Excel)
Topic       : Sheet1               (the current worksheet name)
Commands:   : [run("BobTest")]     (needs square brackets and quotes)

Then, with Excel running (with the same sheet still open), I executed the
script, and it performed the macro just fine. So I can't say why your
isn't working :-(

--
Regds, Bob Osola

 
 
 

Using Send DDE to run an Excel macro (Windows)

Post by Bill Merric » Wed, 16 Jan 2002 06:58:21



Quote:

> What worked for me before in Office 2K on W2K and (currently) in Office XP
> on Win XP was as follows (just confirmed this before posting):

> - Recorded a new macro in a new worksheet called BobTest
> - Chose option "Store in current workbook"
> - tested the macro in Excel

> Then in FM, I created a new script step thus:

> Service Name: Excel                (this is always just Excel)
> Topic       : Sheet1               (the current worksheet name)
> Commands:   : [run("BobTest")]     (needs square brackets and quotes)

> Then, with Excel running (with the same sheet still open), I executed the
> script, and it performed the macro just fine. So I can't say why your
> isn't working :-(

> --
> Regds, Bob Osola

As the Colorado Avalanche announcer says after an Av's goal "Thank you, thank
you, thank you!"  It was a bracket problem.  Everything works fine and my
database is a bit more user friendly.
 
 
 

1. Using a DDE to run a Macro in Excel

I'm trying to run a Macro in Excel 2002 using a DDE from FileMaker Pro 5.5.

I already run macros from FileMaker in Microsoft Word 2002, so I thought I
would do the same thing but point the DDE to Excel instead of Microsoft
Word - simple, I thought - but I cant for the life of me get it to work. If
someone could give me a little guidance it would be more than appreciated -
thanks in advance.

What I do to run a Word macro:

In a FileMaker script I use the command     Send DDE Execute

in the Service Name Box I have         :   WinWord
in the Topic Box I have                      :   System
in the Command Box I have               :  [theMacroName]

so for Excel I've been again running a          Send DDE Execute

in the Service Name Box I have         :   Excel
in the Topic Box I have                      :   System
in the Command Box I have               :  [theMacroName]

it doesnt work........

(a couple of things I thought (but from testing I dont think so now) might
be causing a problem are:  in Excel my macro is called    Macro1     in
Excel's macro list it is called     PERSONAL.XLS!Macro1     (it puts a
PERSONAL.XLS!     in front of all the macros names I've tried both the full
name
and my original name).   Also if I try to edit the macro I get the message
'Cannot edit a macro on a hidden workbook.  Unhide the workbook using the
Unhide Command.'

Again thanks in advance for the help.

Jimmy

2. Printing from 4.5 DOS

3. Help: DDE open Excel and run macro

4. a hints question

5. Help: 3rd Try DDE open Excel and run macro

6. Problem with Searching dB with 'Impromtu'

7. Help: DDE open Excel and run macro

8. External Storage for SQL Server 2000

9. Running excel from oracle using DDE

10. Sending a Fax using DDE from VFP using WinFax 4.0

11. DDE sending data from Access to Excel

12. Using DDE to start WP8.0 macro from Paradox 8.0