48

I have a fixed command which i need to pass to command prompt using VBA and then the command should run. e.g. "perl a.pl c:\temp"

following is the command i am trying to use but it just opens command prompt and doesn't run the command.

Call Shell("cmd.exe -s:" & "perl a.pl c:\temp", vbNormalFocus)

Please check.

TotsieMae
  • 835
  • 6
  • 17
user1699227
  • 583
  • 1
  • 4
  • 4
  • 2
    you don't actually even need to open up cmd.exe first -> Shell() can pass your perl args straight to perl.exe – Cor_Blimey Jul 30 '13 at 20:35

1 Answers1

69

The S parameter does not do anything on its own.

/S      Modifies the treatment of string after /C or /K (see below) 
/C      Carries out the command specified by string and then terminates  
/K      Carries out the command specified by string but remains

Try something like this instead

Call Shell("cmd.exe /S /K" & "perl a.pl c:\temp", vbNormalFocus)

You may not even need to add "cmd.exe" to this command unless you want a command window to open up when this is run. Shell should execute the command on its own.

Shell("perl a.pl c:\temp")



-Edit-
To wait for the command to finish you will have to do something like @Nate Hekman shows in his answer here

Dim wsh As Object
Set wsh = VBA.CreateObject("WScript.Shell")
Dim waitOnReturn As Boolean: waitOnReturn = True
Dim windowStyle As Integer: windowStyle = 1

wsh.Run "cmd.exe /S /C perl a.pl c:\temp", windowStyle, waitOnReturn
Community
  • 1
  • 1
Ripster
  • 3,545
  • 2
  • 19
  • 28
  • 6
    Keep in mind that shell will return after launching the process. It will not wait for your Perl-Script to finish, just saying. – AKDADEVIL Jul 31 '13 at 07:46
  • Thanks a lot. this works fine. What would be the code to "wait for my Perl-Script to finish"? – user1699227 Jul 31 '13 at 20:34
  • I think would have to do something like [this](http://stackoverflow.com/questions/15951837/excel-vba-wait-for-shell-command-to-complete) – Ripster Jul 31 '13 at 20:42
  • If instead of fixed command, I need to pass one of the variables from excel cells then how do i do that? As of now, i have tried in following manner(X is variable) **wsh.Run "cmd.exe /S /C perl a.pl" & X & "100", windowStyle, waitOnReturn** – user1699227 Aug 09 '13 at 21:41
  • Another way to wait for a command to finish is to use the CreateProcessA function. For the VBA code, see this Microsoft article, "Determine when a shelled process ends," https://learn.microsoft.com/en-us/office/vba/access/concepts/windows-api/determine-when-a-shelled-process-ends – scenography Dec 05 '20 at 02:23