shell commands in excel

[venkat1926]

I am fairly proficient with writing excel macros. I am not familiar with what is called "shell commands". I am not able to find any reference to this subject as applied to excel macros/vba. please show me the path.

I have both 2003 and 2007 versions of excel and my os is windows xp

 

[Trevor G]

Shell commands can run executable commands (exe). You don't need to reference the path to the exe as long as you know the name. Here are few examples. If you copy them into a new module sheet and highlight the vb commands and Press F1 you will see help on them, and also on the Shell command

Sub shell1()
Dim retVal As String
retVal = Shell("Notepad.exe", vbNormalFocus)
End Sub
Sub shell2()
Dim retVal As String
retVal = Shell("calc.exe", vbMaximizedFocus)
End Sub
Sub shell3()
Dim retVal As String
retVal = Shell("WinWord.exe", vbNormalFocus)
End Sub

 

[Rick Rothstein]

The following is a compilation of several posts I've given in the past regarding the Shell command...

To execute internal DOS command (Dir, Copy, etc. as well as redirection of screen output), the command processor must be specified (using the Environ$ function and "comspec" as its argument returns the correct command processor path on NT and non-NT systems) . Specifying the command processor is safe & generic and will work with non-internal commands also. That syntax, using an XCopy command as an example is:

Shell  Environ$("comspec") & " /c xcopy """ & Source & """ """ & Destination & """ " & Option, vbHide

You set the Source and Destination (string variables) to the appropriate paths and the Option (string variable), if any, which can be found by opening a Command Prompt window and typing xcopy /?. (Note: You can type /? after any command at a prompt to list the available options for that command.) One more example would be to list all the files in a directory including subdirectories and subdirectories of subdirectories and all of their files.

  CommandLine = "dir """ & FileSpec & """ /s/b > """ & RedirectTo & """"
  Shell Environ$("comspec") & " /c " & CommandLine, vbHide

Here, the output of a Dir command is redirected to a file-path you specify in the RedirectTo (string variable). The /s/b are options to the Dir command that tell it to recurse throught its subdirectories and not to include header or summary information.

I used a variable for the file name so that I could more easily explain the benefit of encasing it in quotemarks. If you redirect to a file that has spaces in its name, or if there are spaces in the path specification itself, then the filename *must* be quoted to protect the spaces from DOS's desire to use them as delimiters. (That's what all those quotemarks in the Shell statement are for.) If the filename doesn't have spaces in it, the quotes aren't necessary BUT they don't hurt either. Hence, the above will work with either.

As for your PING question, something like the following should work:

     strIP = "4.17.23.1"
     Shell Environ$("comspec") & " /c ping " & strIP & " > """ & RedirectFile & """", vbHide

Although you didn't specify it in your original post, I assume you want to use vbHide for the optional 2nd parameter to Shell. This hides the command window so that your user doesn't see it. If you want the command window to remain visible, you would use the vbNormalFocus BUT you must use a /k instead of a /c for the command processor argument. Basically, the /c tells the command processor "here comes a command and, when it's finished executing, close the command shell it is running in" whereas the /k also tells the command processor that a command follows, but it instructs it to leave the command prompt session running.

The above assumes you do NOT have to wait for this file to be completely written before your code continues executing. If you have to work with this file right after it is created, consider one of these (which makes your program wait until the command process is finished):

MICROSOFT'S OFFICIAL WAY
========================
See this link
http://support.microsoft.com/support/kb/articles/Q129/7/96.asp
Note: This method doesn't use Shell -- it uses CreateProcessA.

FAST AND DIRTY METHOD
======================
Paste these lines into a Standard module:

Private Declare Function OpenProcess _
        Lib "kernel32" _
        (ByVal dwDesiredAccess As Long, _
         ByVal bInheritHandle As Long, _
         ByVal dwProcessId As Long) As Long
 
Private Declare Function CloseHandle _
        Lib "kernel32" _
        (ByVal hObject As Long) As Long
 
Private Declare Function WaitForSingleObject _
        Lib "kernel32" _
        (ByVal hHandle As Long, _
         ByVal dwMilliseconds As Long) As Long

Call your Shell command with the appropriate Shell arguments placed in the parentheses:

PID = Shell( **Put Shell Arguments Here** )

And finally, paste the following IMMEDIATELY after the PID=Shell statement above (making sure to handle the possible error where indicated; i.e. stop the code from falling through to your other commands if the Shell failed):

If PID = 0 Then
     '
     'Handle Error, Shell Didn't Work
     '
Else
     hProcess = OpenProcess(&H100000, True, PID)
     WaitForSingleObject hProcess, -1
     CloseHandle hProcess
End If

 

[Trevor G]

Hi Rick,

That is a nice and thorough explination.

 

[venkat1926]

thanks all of you. shall study them.

 

[DaveU]

Hi Rick,
I realize this is an old post, but wanted to say thanks for the great explanation. Maybe you can help me with this:

I've come across two variations of shelling to an exe,

shell "explorer.exe ""C:\Windows\system32\notepad.exe""",vbNormalFocus

and shell "C:\Windows\system32\notepad.exe",vbNormalFocus

They both work, but I don't understand why the reference to "explorer.exe" in the first example.

Thank you.