{"id":665,"date":"2009-06-04T16:10:13","date_gmt":"2009-06-04T16:10:13","guid":{"rendered":"http:\/\/www.mjtnet.com\/blog\/?p=665"},"modified":"2009-06-04T16:13:06","modified_gmt":"2009-06-04T16:13:06","slug":"calling-macro-scheduler-scripts-from-vbvba","status":"publish","type":"post","link":"https:\/\/www.mjtnet.com\/blog\/2009\/06\/04\/calling-macro-scheduler-scripts-from-vbvba\/","title":{"rendered":"Calling Macro Scheduler Scripts from VB\/VBA"},"content":{"rendered":"

I’m often asked how you can run Macro Scheduler<\/a> macros from other programming languages, particularly VB. \u00a0<\/p>\n

Macro Scheduler scripts can be run from the command line. \u00a0See the help file topic “Command Line Options”. \u00a0E.g.:<\/p>\n

msched.exe “c:\\someplace\\mymacro.scp”<\/p><\/blockquote>\n

VB\/VBA lets you execute external commands\/applications via the Shell function:<\/p>\n

Shell “””c:\\program files\\macro scheduler 11\\msched.exe”” “”c:\\scripts\\example.scp”””, vbNormalFocus<\/p><\/blockquote>\n

The only problem with the Shell function is that it does not wait until what it is calling has finished running before continuing. \u00a0So it fires off the macro and the program continues. \u00a0In most cases you’d want to wait for the script to finish before you continue. \u00a0To do this you can use the following ShellAndWait function:<\/p>\n

    Private Declare Sub Sleep Lib \"kernel32\" ( _\r\n        ByVal dwMilliseconds As Long)\r\n    Private Declare Function GetExitCodeProcess Lib \"kernel32\" ( _\r\n        ByVal hProcess As Long, ByVal lpExitCode As Long) As Long\r\n    Private Declare Function timeGetTime Lib \"winmm.dll\" () As Long\r\n    Private Declare Function OpenProcess Lib \"kernel32\" ( _\r\n        ByVal dwDesiredAccess As Long, ByVal bInheritHandle As Long, ByVal dwProcessId As Long) As Long\r\n    Private Const STILL_ACTIVE = &H103\r\n    Private Const PROCESS_QUERY_INFORMATION = &H400\r\n    Private Declare Function CloseHandle Lib \"kernel32\" ( _\r\n        ByVal hObject As Long) As Long\r\n\r\n    Public Function ShellAndWait( _\r\n     ByVal sShell As String, _\r\n            Optional ByVal eWindowStyle As Integer = vbNormalFocus, _\r\n            Optional ByRef sError As String = \"\", _\r\n            Optional ByVal lTimeOut As Long = 2000000000 _\r\n        ) As Boolean\r\n        Dim hProcess As Long\r\n        Dim lR As Long\r\n        Dim lTimeStart As Long\r\n        Dim bSuccess As Boolean\r\n\r\n        On Error GoTo ShellAndWaitError\r\n\r\n        ' This is v2 which is somewhat more reliable: \r\n        hProcess = OpenProcess(PROCESS_QUERY_INFORMATION, False, Shell(sShell, eWindowStyle))\r\n        If (hProcess = 0) Then\r\n            sError = \"This program could not determine whether the process started.\" & _\r\n                 \"Please watch the program and check it completes.\"\r\n            ' Only fail if there is an error - this can happen \r\n            ' when the program completes too quickly. \r\n        Else\r\n            bSuccess = True\r\n            lTimeStart = timeGetTime()\r\n            Do\r\n                ' Get the status of the process \r\n                GetExitCodeProcess(hProcess, lR)\r\n                ' Sleep during wait to ensure the other process gets \r\n                ' processor slice: \r\nDoEvents:       Sleep(100)\r\n                If (timeGetTime() - lTimeStart > lTimeOut) Then\r\n                    ' Too long! \r\n                    sError = \"The process has timed out.\"\r\n                    lR = 0\r\n                    bSuccess = False\r\n                End If\r\n            Loop While lR = STILL_ACTIVE\r\n        End If\r\n        ShellAndWait = bSuccess\r\n\r\n        Exit Function\r\n\r\nShellAndWaitError:\r\n        sError = Err.Description\r\n        Exit Function\r\n    End Function<\/pre>\n
So your code becomes:<\/p>\n
ShellAndWait “””c:\\program files\\macro scheduler 11\\msched.exe”” “”c:\\scripts\\example.scp”””, vbNormalFocus<\/p><\/blockquote>\n
If you have Macro Scheduler Pro<\/a> you can compile the script to an EXE and then just execute the exe, making the command line simpler:<\/p>\n
ShellAndWait “c:\\someplace\\mymacro.exe”, vbNormalFocus<\/p><\/blockquote>\n
The above code is based on the code found here<\/a>.<\/p>\n","protected":false},"excerpt":{"rendered":"
I’m often asked how you can run Macro Scheduler macros from other programming languages, particularly VB. \u00a0 Macro Scheduler scripts can be run from the command line. \u00a0See the help file topic “Command Line Options”. \u00a0E.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 […]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":[],"categories":[5,6],"tags":[],"jetpack_featured_media_url":"","_links":{"self":[{"href":"https:\/\/www.mjtnet.com\/blog\/wp-json\/wp\/v2\/posts\/665"}],"collection":[{"href":"https:\/\/www.mjtnet.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.mjtnet.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.mjtnet.com\/blog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.mjtnet.com\/blog\/wp-json\/wp\/v2\/comments?post=665"}],"version-history":[{"count":13,"href":"https:\/\/www.mjtnet.com\/blog\/wp-json\/wp\/v2\/posts\/665\/revisions"}],"predecessor-version":[{"id":678,"href":"https:\/\/www.mjtnet.com\/blog\/wp-json\/wp\/v2\/posts\/665\/revisions\/678"}],"wp:attachment":[{"href":"https:\/\/www.mjtnet.com\/blog\/wp-json\/wp\/v2\/media?parent=665"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.mjtnet.com\/blog\/wp-json\/wp\/v2\/categories?post=665"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.mjtnet.com\/blog\/wp-json\/wp\/v2\/tags?post=665"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}