Automatically Excuting a Macro in a different workbook ?
 

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.... :-(

Automatically Excuting a Macro in a different workbook ?
 

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)



"dim" wrote in message
...
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.... :-(

Automatically Excuting a Macro in a different workbook ?
 

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




Bob Phillips wrote:

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)

"dim" wrote in message
...
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.... :-(



--

Dave Peterson

Automatically Excuting a Macro in a different workbook ?
 

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?

:-(

"Dave Peterson" wrote:

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




Bob Phillips wrote:

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)

"dim" wrote in message
...
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.... :-(



--

Dave Peterson

Automatically Excuting a Macro in a different workbook ?
 

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" wrote in message
...
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?

:-(

"Dave Peterson" wrote:

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




Bob Phillips wrote:

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)

"dim" wrote in message
...
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.... :-(



--

Dave Peterson

Automatically Excuting a Macro in a different workbook ?
 

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

Automatically Excuting a Macro in a different workbook ?
 

Where's the End If?

"dim" wrote in message
...
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

Automatically Excuting a Macro in a different workbook ?
 

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 futu

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

Automatically Excuting a Macro in a different workbook ?
 

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" wrote in message
...
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 futu

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

Automatically Excuting a Macro in a different workbook ?
 

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

"Bob Phillips" wrote:

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" wrote in message
...
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 futu

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

Automatically Excuting a Macro in a different workbook ?
 

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


"Bob Phillips" wrote:

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" wrote in message
...
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 futu

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