I’m often asked how you can run Macro Scheduler macros from other programming languages, particularly VB.
Macro Scheduler scripts can be run from the command line. See the help file topic “Command Line Options”. E.g.:
msched.exe “c:\someplace\mymacro.scp”
VB/VBA lets you execute external commands/applications via the Shell function:
Shell “””c:\program files\macro scheduler 11\msched.exe”” “”c:\scripts\example.scp”””, vbNormalFocus
The only problem with the Shell function is that it does not wait until what it is calling has finished running before continuing. So it fires off the macro and the program continues. In most cases you’d want to wait for the script to finish before you continue. To do this you can use the following ShellAndWait function:
Private Declare Sub Sleep Lib "kernel32" ( _
ByVal dwMilliseconds As Long)
Private Declare Function GetExitCodeProcess Lib "kernel32" ( _
ByVal hProcess As Long, ByVal lpExitCode As Long) As Long
Private Declare Function timeGetTime Lib "winmm.dll" () As Long
Private Declare Function OpenProcess Lib "kernel32" ( _
ByVal dwDesiredAccess As Long, ByVal bInheritHandle As Long, ByVal dwProcessId As Long) As Long
Private Const STILL_ACTIVE = &H103
Private Const PROCESS_QUERY_INFORMATION = &H400
Private Declare Function CloseHandle Lib "kernel32" ( _
ByVal hObject As Long) As Long
Public Function ShellAndWait( _
ByVal sShell As String, _
Optional ByVal eWindowStyle As Integer = vbNormalFocus, _
Optional ByRef sError As String = "", _
Optional ByVal lTimeOut As Long = 2000000000 _
) As Boolean
Dim hProcess As Long
Dim lR As Long
Dim lTimeStart As Long
Dim bSuccess As Boolean
On Error GoTo ShellAndWaitError
' This is v2 which is somewhat more reliable:
hProcess = OpenProcess(PROCESS_QUERY_INFORMATION, False, Shell(sShell, eWindowStyle))
If (hProcess = 0) Then
sError = "This program could not determine whether the process started." & _
"Please watch the program and check it completes."
' Only fail if there is an error - this can happen
' when the program completes too quickly.
Else
bSuccess = True
lTimeStart = timeGetTime()
Do
' Get the status of the process
GetExitCodeProcess(hProcess, lR)
' Sleep during wait to ensure the other process gets
' processor slice:
DoEvents: Sleep(100)
If (timeGetTime() - lTimeStart > lTimeOut) Then
' Too long!
sError = "The process has timed out."
lR = 0
bSuccess = False
End If
Loop While lR = STILL_ACTIVE
End If
ShellAndWait = bSuccess
Exit Function
ShellAndWaitError:
sError = Err.Description
Exit Function
End Function
So your code becomes:
ShellAndWait “””c:\program files\macro scheduler 11\msched.exe”” “”c:\scripts\example.scp”””, vbNormalFocus
If you have Macro Scheduler Pro you can compile the script to an EXE and then just execute the exe, making the command line simpler:
ShellAndWait “c:\someplace\mymacro.exe”, vbNormalFocus
The above code is based on the code found here.
