When trying to execute a third party application, such as Macro Scheduler, from VBA in Office 2007 using the Shell function the following error is returned:

“Invalid procedure call or argument”

Sometimes you might want to run a Macro Scheduler macro from Office using this method and get this error. Searching the web reveals many people reporting the same error when trying to run other applications, including other Office apps! E.g.:

Shell “c:\my macros\macroA.exe”

I suspect there’s an issue with spaces in the path but trying to embed extra quotes with chr(34) doesn’t seem to help. The Shell function also doesn’t provide a way to specify command line parameters other than including them in the entire command string.

The solution is to use the ShellExecute API function. Declare the function in the General Declarations section of your VBA module using this code:

Private Declare Function ShellExecute Lib “shell32.dll” Alias “ShellExecuteA” (ByVal hwnd As Long, ByVal lpOperation As String, ByVal lpFile As String, ByVal lpParameters As String, ByVal lpDirectory As String, ByVal nShowCmd As Long) As Long

Then you can use the function as follows:

ShellExecute 0&, “”, “c:\program files\macro scheduler\msched.exe”, “calculator”, “”, 1

This code runs the Calculator sample macro in Macro Scheduler.

To run an application with no command line parameters leave the fourth parameter empty:

ShellExecute 0&, “”, “c:\my macros\mymacro.exe”, “”, “”, 1

Tweet This

Related posts:

1. Quick Tip: Automating Office 2007
2. Free Copies of Vista and Office 2007!
3. Converting Office VBA to VBScript
4. Office Values at Microsoft
5. The Excel 2007 Bug Explained

This entry was posted on Wednesday, June 27th, 2007 at 3:12 pm and is filed under Automation, Scripting. You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.