Automatically Excuting a Macro in a different workbook ?

[dim]

Hi folks,

I have two workbooks - Book1 and Book2.

In Book1 I have Macro1,

In Book2 I also have a Macro1 (Or I can name it differently if necessary)
and also Macro2 and Macro3 and Macro4 and....etc etc

I want Macro1 in Book1 to open Book2 and then after opening to automatically
execute Book2's Macro1 ?

Sometimes I might want it to execute a different one of the Macros in Book2
automatically after opening so I can't just include it as part of an
auto-open...I think...

Please Help!!!....this is a very substantial stumbling block for my current
file if I can't get over it.... :-(

 

[Bob Phillips]

Set WB = Workbooks.Open "Book2.xls"
Application.Run "'Book2.xls'!Macro1"


--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Dave Peterson]

Typo alert:
Set WB = Workbooks.Open "Book2.xls"
should be:
Set WB = Workbooks.Open("Book2.xls")

 

[dim]

Thankyou very much,

That helps but I think I didn't explain enough. I'm having trouble with
specifying which Macro to run also. I've been trying variations of IF THEN
ELSEIF as below but I keep getting errors!

Macro1 in Book1 should decided whether to run Macro1/2/3etc in Book2
dependant upon the value of a cell (Cell A1 in the example below) in Book1.

I've tried putting variations of this into the code after the workbook
opening code but it isn't working:

If .Value(1,1) = 1 .Then .Application.Run "Macro1"
Elseif .Value(1,1) = 2 .Then .Application.Run "Macro2"
Elseif etc etc

Where am I going wrong?

:-(

 

[Bob Phillips]

It is not Dot Application. Application is the top of the OM, so it cannot be
dot qualified.

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[dim]

Thanks again Bob,

I was hoping not to bore everyone with details but I'm still stuck, and I
REALLY appreciate the help, so here is examples of what I'm using for
everything! I tried to incorporate what help I've been given so far but it's
still stuck....thanks again if you can have a look at this Bob, or anyone
else,

My Macro1 in Book1:

Sub Macro1()
'
' Macro1 Macro
'

'
If .Value(1, 1) = 1 Then
Set WB = Workbooks.Open "C:\Documents and Settings\D. Murphy\My
Documents\Book2.xls"
Application.Run "'Book2.xls'!Macro1"
ElseIf .Value(1, 1) = 2 Then
Set WB = Workbooks.Open "C:\Documents and Settings\D. Murphy\My
Documents\Book2.xls"
Application.Run "'Book2.xls'!Macro2"
ElseIf .Value(1, 1) = 3 Then
Set WB = Workbooks.Open "C:\Documents and Settings\D. Murphy\My
Documents\Book2.xls"
Application.Run "'Book2.xls'!Macro3"
ActiveWorkbook.Save
ActiveWorkbook.Close
End Sub

My Macro1, 2 & 3 in Book2:

Sub Macro1()
'
' Macro1 Macro
'

'
Range("B1").Select
Selection.ClearContents
Range("C1").Select
ActiveCell.FormulaR1C1 = "1"
Range("A1").Select
End Sub
Sub Macro2()
'
' Macro2 Macro
'

'
Range("B2").Select
Selection.ClearContents
Range("C2").Select
ActiveCell.FormulaR1C1 = "2"
Range("A1").Select
End Sub
Sub Macro3()
'
' Macro3 Macro
'

'
Range("B3").Select
Selection.ClearContents
Range("C3").Select
ActiveCell.FormulaR1C1 = "3"
Range("A1").Select
End Sub

 

[MP]

Where's the End If?

 

[dim]

Woohoo! I have it! And its all the sweeter because I sorted it out before
anyone got back to me! :-D

Thanks very much Bob & Dave, I couldn't have got it thus far without your
help. I hope everyone had a happy Christmas or whatever other festival you
may celebrate.

Bye for now. Thanks.

Here's what I used in case anyone reads this in future:

Sub Macro1()
'
' Macro1 Macro
'

'
ScreenUpdating = False
If (Cells(1, 1).Value) = 1 Then
Workbooks.Open Filename:= _
"C:\Documents and Settings\D. Murphy\My Documents\Book2.xls"
Application.Run "'Book2.xls'!Macro1"
ActiveWorkbook.Save
ActiveWorkbook.Close
ElseIf (Cells(1, 1).Value) = 2 Then
Workbooks.Open Filename:= _
"C:\Documents and Settings\D. Murphy\My Documents\Book2.xls"
Application.Run "'Book2.xls'!Macro2"
ActiveWorkbook.Save
ActiveWorkbook.Close
ElseIf (Cells(1, 1).Value) = 3 Then
Workbooks.Open Filename:= _
"C:\Documents and Settings\D. Murphy\My Documents\Book2.xls"
Application.Run "'Book2.xls'!Macro3"
ActiveWorkbook.Save
ActiveWorkbook.Close
End If
ScreenUpdating = True
End Sub

 

[Bob Phillips]

A bit cleaner

Sub Macro1()
Application.ScreenUpdating = False
With Cells(1, 1)
If .Value > 0 And .Value < 4 Then

Workbooks.Open Filename:= _
"C:\Documents and Settings\D. Murphy\My Documents\Book2.xls"

Select Case .Value

Case 1: Application.Run "'Book2.xls'!Macro1"
Case 2: Application.Run "'Book2.xls'!Macro2"
Case 3: Application.Run "'Book2.xls'!Macro3"
End Select
ActiveWorkbook.Save
ActiveWorkbook.Close
End If
End With
Application.ScreenUpdating = True
End Sub


--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[dim]

GREAT! I'll change it to that. Yours looks much better. thankyou.

 

[PAT]

I saw how neat is your answer to dim way back in 12/27/2007 about the macros
above.
I wonder if you could help me solve my problem.

can you design a macro that serves as a sound & message alert in
Excel sheet?

It would have to be able to monitor the value in cell A1 (that comes in as a
feed from a DDE link - continous streaming). The alert would be activated
when the value on A1 matches the criteria in cell C1 and according to the
sign ( > ,< or = ) on D1, e.g. A1 > C1 or A1 < C1 or A1 = C1 or =< C1 etc...
When activated, the macro would speak up the value on C1 - say " the quote
is (value of C1) " - and would repeat 4 times the announcement and show a
message saying the same thing, until an OK button is hit.
Then all over again for one more alert but for a different cell to be
monitored, A2, to be matched with C2 according to the sign on D2 , all on the
same sheet as A1 etc..
The catch is that since the value is streaming continuously - say every
second, the alert will be activated continously every second ( independently
of the repetition).There would have to be a brake to make the alert stop when
A1 hits the value of C1 until it is reset by punching an OK button!
Much appreciate any help on that...
PAT