call a macro in another worksheet?

[Guest]

Here's my scenario: I've got a macro that is currently run manually that
processes a bunch of data in another workbook and then saves itself with a
unique filename based on the date. I want to run this processing macro
automatically from a SQL SPROC. I can use auto_run to get a macro running
from a SPROC, but I don't want to have an auto_run macro in the main workbook
that does the processing since it gets sent automatically to a bunch of
people.

So, it seems that my option is either to
a) use an auto_run in the main sheet and then write code to delete the
auto_run macro or
b) have an auto_run in a separate workbook that calls into the main
processing workbook and runs the processing macro

Option a seems to run into security issues, so I've landed at option b.

The question, then is how do I call a macro in another (open) workbook via
VBA? Note that the name of the workbook I want to call into will vary, so I
need to have a parameterized way of doing this. My expectation would be
something like this:

fn = "dailyreport_" + GenerateDateString() + ".xls"
mac = fn + "!Controller.Main"
Call (mac)

where Controller.Main is the Module.Macro I want to call (this code doesn't
work, of course, just trying to explain the logic)

Can anyone advise on whether this is possible and if so what the right way
is to do it?

tia

 

[Bob Phillips]

Dim wb As String

wb = "myBook.xls"

Application.Run wb & "!myMacro()"

or if it has an arguement

Application.Run wb & "!myMacro", "hello you"

 

[Dave Peterson]

I bet Bob meant:

Application.Run wb.Name & "!myMacro()"

(in both cases)