run macros in sequence

[smithrd6]

How to run macros in sequence, meaning, when macro 1 is finished, macro 2 fires.

thanks

 

[VoG]

Try like this

Sub BigMacro
Call Macro1
Call Macro2
Call Macro3
End Sub

 

[smithrd6]

the first macro has to complete prior to firing the next macro

 

[VoG]

It will.

 

[gregtx81]

Derivative question (apologies if this should be a new thread):

If I run say, a Java program, that's initiated through a command prompt call from VBA, is there a way to have the macro pause until the console window closes (i.e. the Java program is complete).

I currently just have a message box pop up which I manually close at the appropriate time (thus acting as a pause in VBA). I suppose I could have the Java program create a file at its completion and have the VBA loop until that file has been created.

This isn't a major problem, just wondering if there's something that's less of a work-around...

 

[Jaafar Tribak]

Sure. here is an routine that shells the Windows Calc.exe and waits until the user is done with the calculator - ( see if you can adapt it to your Java Program )

Option Explicit

Private Declare Function OpenProcess Lib "kernel32" _
(ByVal dwDesiredAccess As Long, _
ByVal bInheritHandle As Long, _
ByVal dwProcessId As Long) As Long

Private Declare Function CloseHandle Lib "kernel32" _
(ByVal hObject As Long) As Long

Private Declare Function WaitForSingleObject Lib "kernel32" _
(ByVal hHandle As Long, _
ByVal dwMilliseconds As Long) As Long

Private Const SYNCHRONIZE As Long = &H100000
Private Const INFINITE As Long = &HFFFFFFFF

Private Sub Test()
 
    Dim lPid As Long, lPhndl As Long
    
    lPid = Shell("Calc.exe")
    lPhndl = OpenProcess(SYNCHRONIZE, 0, lPid)
    If lPhndl Then
        Do
            WaitForSingleObject lPhndl, INFINITE
            CloseHandle lPhndl
            Exit Do
            DoEvents
        Loop
    End If
    MsgBox "code is now resuming"

End Sub

 

[gregtx81]

Thanks Jaafar. Works like a charm.

Changed...

    lPid = Shell("Calc.exe")

to...

    dim strCommands as String
    strCommands = "cd c:\pathname\ & java filename"
    lPid = Shell("cmd.exe /c" & strCommands)

Also discovered that if the Excel help window is open, you have to click and refocus on Excel in order for the code to resume.