Can I call a macro from a specific book

M

Michelle

I have a number of workbooks open all with a macro called 'MyMacro' in them

2 Questions:

1. When there are a few open, how can I run the macro from a particular book
(I want something like: Call Workbooks("Book1.xls").MyMacro)

2. Also... How can I run the MyMacro macro from the ActiveWorkbook?

Thanks

M
 
A

aflatoon

Use Application.Run:


Code:
--------------------



Application.Run "'Book1.xls'!MyMacro"
--------------------





I have a number of workbooks open all with a macro called 'MyMacro' i
them
2 Questions:

1. When there are a few open, how can I run the macro from a particula book
(I want something like: Call Workbooks("Book1.xls").MyMacro)

2. Also... How can I run the MyMacro macro from the ActiveWorkbook?

Thanks

M
 
D

Dave Peterson

First, I think that this is a very bad idea (using the same name). I've seen
excel run the macro it thinks it should run instead of the one I want.

But in my light testing today, this worked ok for me in xl2003 under winXP Home.
(I'm not sure what version I've see the problem with.)

#1. You can use something like this:

Option Explicit
Sub testme()
Dim OtherWkbk As Workbook
Set OtherWkbk = Workbooks("book1.xls")
Application.Run "'" & OtherWkbk.Name & "'!mymacro"
End Sub

#2. If you really mean the activeworkbook's project -- not the workbook's
project that's running the code:

Option Explicit
Sub testme()
Dim OtherWkbk As Workbook
Set OtherWkbk = ActiveWorkbook
Application.Run "'" & OtherWkbk.Name & "'!mymacro"
End Sub

If you meant that you wanted to have a procedure call another procedure in the
same project:

Option Explicit
Sub testme()
Call myMacro

'or even
Dim OtherWkbk As Workbook
Set OtherWkbk = ThisWorkbook
Application.Run "'" & OtherWkbk.Name & "'!mymacro"

End Sub

Again, I've seen some very bad problems when I've tried to do this in real life.
I'd spend some time making nice names.

In fact, if those macros are all the same (and work against the active sheet or
active workbook, then I think I'd create an addin that contained the code. And
remove all that code from the individual workbooks.

It would make life lots simpler (only one file to update). And I could just
open that addin when I needed to run any of those macros.
 
M

Michelle

Thanks - good advice. I may have to ignore it, but it's great that you've
made me think it through again.

M
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top