DDEExecute and Excel - how run Excel macros from MacroSched

crozzer · Post by **crozzer** » Sun Jul 20, 2003 7:13 am

// Can you help me get something like this to compile and run?
// I want to fire off a macro in Excel using a better method than
// switching windows and sending keystrokes.
// If DDE is not the answer, is there another way?
// Thanks - [email protected]
// MacroScheduler 7.2 generates type mismathces at compile and runtime
// on the DDEInitiate line.
VBSTART
sub DDEExample
lChannel = DDEInitiate "Excel", "ddetest.xls"
DDEExecute lChannel, "[RUN(" & Chr(34) & "HelloWorld" & Chr(34) & ")]"
DDETerminate lChannel
End sub
VBEND
VBRun>DDEExample

support · Post by **support** » Sun Jul 20, 2003 11:03 am

Hi,

As far as I am aware there are no such functions as DDEInitiate, DDEExecute and DDETerminate in VBScript. I have looked in the VBScript documentation and can find no mention of them - where did you get them from?

Macro Scheduler has DDE commands to poke and request data but I don't think these can be used to run an Excel macro.

Using VBScript I would use Excel's ActiveX like this:

VBSTART
Sub ExcelExample
Dim xlApp
Dim xlBook

Set xlApp = CreateObject("Excel.Application")
Set xlBook = xlApp.Workbooks.Open("d:\test.xls")

xlApp.visible = True
xlApp.Run "MyMacro"

xlApp.Quit
Set xlBook = Nothing
Set xlApp = Nothing
End Sub
VBEND
VBRun>ExcelExample

See the .Run command. Simply provide the name of the macro in quotes and it will run the Excel macro.

Or you could make a Workbook_Open subroutine in the ThisWorkbook module for the workbook which runs your macro. Then you just have to start Excel from Macro Scheduler:

ExecuteFile>myworkbook.xls

The macro will run automatically.

Or, you can assign a shortcut to Excel macros. If you want to be able to run a number of different macros at different times you could assign a shortcut to them and then all you need to do is:

SetFocus>myworkbook.xls*
Press Ctrl
Send>m
Release CTRL

See under Options for the macro in question.

My preference would be to use the VBScript/ActiveX approach.

armsys · Post by **armsys** » Sun Jul 20, 2003 12:32 pm

Hi Support,

Pardon me to jump in. Can DDE poke + request be used to retrieve data from tables in visual Foxpro 7/8 and Access XP database? Or through ODBC? Thanks.

crozzer · Post by **crozzer** » Mon Jul 21, 2003 3:52 am

Thanks for the help executing macros in excel. Your answers were very helpful.

Because I am running macroscheduler from an open excel file and I need to transfer control back to excel when I am done, I am using the following.
===============
VBSTART
Sub RunXLMacro(sMacroName)
Dim xlApp
Set xlApp = GetObject(, "Excel.Application")
xlApp.Run sMacroName
Set xlApp = Nothing
End Sub
VBEND
VBRun>RunXLMacro,FillInMacroNameHere
===============
Which I plan to use numerous times throughout my macros.
(Any pointers for making this tool more usefule much appreciated.)

In answer to your question regarding VB and the DDE calls I used.
DDEExecute, DDEInitiate, DDETerminate are all standard DDE calls used in VB. I found them in online help for Visual Basic. Go to your editor (I dont' have the studio, I use the editor provided with Excel), type DDEInitiate, and hit F1.

support · Post by **support** » Mon Jul 21, 2003 10:17 am

Hi,

Those DDE commands may well be standard in VB, but Macro Scheduler uses VBScript not VB.