Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
call a macro in another worksheet?
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
call a macro in another worksheet?
Dim wb As String
wb = "myBook.xls" Application.Run wb & "!myMacro()" or if it has an arguement Application.Run wb & "!myMacro", "hello you" -- HTH Bob Phillips "George Conard" wrote in message ... 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
call a macro in another worksheet?
I bet Bob meant:
Application.Run wb.Name & "!myMacro()" (in both cases) Bob Phillips wrote: Dim wb As String wb = "myBook.xls" Application.Run wb & "!myMacro()" or if it has an arguement Application.Run wb & "!myMacro", "hello you" -- HTH Bob Phillips "George Conard" wrote in message ... 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 -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How can run a macro ( call a macro) on selection of any filtercriteria? | Excel Worksheet Functions | |||
Call an Access macro from an Excel macro | Excel Discussion (Misc queries) | |||
how to call the event of other worksheet | Excel Worksheet Functions | |||
how to call a macro from a worksheet event? | Excel Programming | |||
Call VB6 DLL from Worksheet without VBA wrapper | Excel Programming |