call another macro

[Guest]

I have a macro which may have to call another macro if a condition is
satisfied. I was using the command Application.Run "macro's name" but then
at the end of that macro the VB does not keep executing the macro that had
triggered the process.

Any ideas? I appreciate. Thanks in advance.

Daniel (Brazil)

 

[Guest]

hi,
instead of application.run, use the Call command.

Call MacroOther

works for me.
Regards
FSt1

 

[Bob Phillips]

That should not be the case. Can you post the code?

--
HTH

Bob

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

 

[Guest]

It doesn't work for me. I suppose that this is because of the fact that the
macro called is in another workbook that was also opened if that macro must
be executed.

Thanks anyway...

 

[Guest]

Sub Analyse()
'
' Analyse Macro
' Macro gravada em 25/04/2007 por Daniel Spilberg
'

Dim a, b As Integer
Dim fichier, codusi, USINE As String

For a = 15 To 33 Step 2
If Cells(a, 3).Value = True Then

codusi = Cells(a, 6).Value
fichier = Cells(5, 9) & Cells(3, 9) & "_Analyse_" & Cells(a, 6) &
".xls"
USINE = Cells(a, 4)

For b = 17 To 19 Step 2

If Cells(b, 7).Value = True Then

Select Case Cells(b, 9)
Case "Génération de l'analyse"
Workbooks.OpenText _
Filename:="U:\test.xls"
' pour ouvrir le fichier qui sera traité (M.xls)
Windows("Traitement_Vérification_Impression.xls").Activate
Workbooks.Open _
Filename:=Cells(a, 1), IgnoreReadOnlyRecommended:=True
Sheets("Initial").Select

Application.DisplayAlerts = False
ActiveWorkbook.SaveAs Filename:="S:\Gestion
DT\2007\Test\M.xls"
Application.DisplayAlerts = True

Windows("test.xls").Activate
Application.Run "test.xls!A_test"

Windows("Traitement_Vérification_Impression.xls").Activate


Case "Vérification"
Workbooks.OpenText _
Filename:="S:\Gestion
DT\Vérification_cohérence_salaire_effectif\vérif_cohérence_salaire_effectif.xls"

Windows("Traitement_Vérification_Impression.xls").Activate


End Select

End If
Next b

'Workbooks(fichier).Close Savechanges:=False

End If


Next a

End Sub

 

[Guest]

Bob, do you also you think you'll need the code of the macro called? I think
it doesn't interfere...

 

[Guest]

Daniel,

Your code should work. Are you saying it exits out of the For loop after
executing the other workbook's macro? Which part of the code are you
expecting to execute? The only line of code after the call to the other
macro is to activate a window. after that it should go back to the start of
the for loop...

Also, you don't need to activate the workbook window to execute it's macro..
For example, I have 2 workbooks: book1.xls and book2.xls. In book2.xls,
inside a module, I have:

Public Sub Test()
Debug.Print "Hello, World from Book2"
End Sub


Then, in Book1.xls, I have:

Public Sub test()
Application.Run "Book2.xls!test"
Debug.Print "Hello, World from Book1"
End Sub


When I execute the test macro from Book1, the output in the immediate window
shows:

Hello, World from Book2
Hello, World from Book1

 

[Guest]

Adriano,

I am saying exactly that. It executates one time the other book's macro and
the exit the For loop.

I was expecting it to go the For loop, open the other book, executate its
macro, then go to the For loop again and do the loop all over and over again,
till the loop ends.

But it is not that what is happening. It goes inside the loop executates the
other macro's book once and then doesn't return to the loop.

Once my loop is over, my macro is also over. I simplified a bit my code and
am copying it down again.

Thanks for the complimentary piece of information!

Daniel (Brazil)

Sub Analyse()
'
' Analyse Macro
' Macro gravada em 25/04/2007 por Daniel Spilberg
'

Dim a, b As Integer

For a = 17 To 19 Step 2

If Cells(a, 7).Value = True Then

Select Case Cells(a, 9)
Case "Génération de l'analyse"

Workbooks.OpenText _
Filename:="U:\test.xls"

Windows("Traitement_Vérification_Impression.xls").Activate
For b = 15 To 33 Step 2
If Cells(b, 3).Value = True Then

' pour ouvrir le fichier qui sera traité (M.xls)
Windows("Traitement_Vérification_Impression.xls").Activate
Workbooks.Open _
Filename:=Cells(b, 1), IgnoreReadOnlyRecommended:=True,
UpdateLinks:=False
Sheets("Initial").Select

Application.DisplayAlerts = False
ActiveWorkbook.SaveAs Filename:="S:\Gestion
DT\2007\Test\M.xls"
Application.DisplayAlerts = True

Windows("test.xls").Activate

Application.Run "test.xls!A_test"

Windows("Traitement_Vérification_Impression.xls").Activate
'Workbooks(fichier).Close Savechanges:=False
End If
Next b


Case "Vérification"
Columns("C:C").Select
Selection.Copy
Workbooks.OpenText _
Filename:="S:\Gestion
DT\Vérification_cohérence_salaire_effectif\vérif_cohérence_salaire_effectif.xls"
Columns("C:C").Select
ActiveSheet.Paste
Range("a1").Select
Application.Run
"vérif_cohérence_salaire_effectif.xls!VERIFIER"
Windows("Traitement_Vérification_Impression.xls").Activate
Workbooks("vérif_cohérence_salaire_effectif.xls").Close
Savechanges:=True

End Select



End If


Next a

End Sub

 

[Guest]

Hey people thanks for your help.

I already discovered the problem. The macro called had as the last line the
command "bookcalled.close". As so, it did not come back to the loop. When I
cut out that command and put it immediately after the returning point inside
the loop on the main macro, it worked perfectly.

That was the reason!