Run Automation from VBA


I'm trying to run a toad data point automation from VBA. Essentially calling the application from VBA (to run an automation task). However, for some reason, VBA has a problem with calling Toad. Below are two scripts, the first works (and has spaces in the folder name, the second (The call to TDP) which also has spaces does not run, and throws a "Run-time error '5': Invalid procedure call or argument. Yet if I copy and past the strings from each into the command line, (including quotation marks, they both will start their respective programs (Chrome, and Toad Data Point).

Any assistance would be greatly appreciated.

Program 1:
Sub RunChrome()
Run Shell("C:\Program Files\Google\Chrome\Application\chrome.exe", vbMaximizedFocus)
End Sub

Sub RunTDP()
Run Shell("C:\Program Files\Quest Software\Toad Data Point 5.0\toad.exe", vbMaximizedFocus)
End Sub

Kevin Regan

I don't have much hands on with VB/A, personally, but examples I've seen usually either

  • CALL the shell() function (vs. using RUN), or
  • Set a variable to the return value of the shell() function

Maybe one of the above options works?
BTW, I'm guessing what you've tried so far is merely to make sure Toad is callable, but when you get your test running successfully, don't forget that calling Toad's executable to run an automation script will require a command line flag (e.g. -batch) and the name of the automation (.tas) file.

Ok, this one took a little while to figure out. Apparently, windows Defender and or specifically the Attack Surface Reduction capability of windows defender needs to whitelist Toad Data Point.

Sub CLI_Test()
Call Shell("""C:\Program Files\Quest Software\Toad Data Point 5.0\toad.exe"" -batch=true ""E:\Test Folder\test folder 2\scripts\CLI_TEST.tas""", vbMaximizedFocus)
End Sub

I changed the folder names, but the above should work, and is an example of a Command line run from VBA with spaces in both source and target.