How do you run command line commands from a module? 2

[wmichael]

For example, I want to get a list of files by piping the output of a DIR command to a file.

I am running Office XP, and am specifically want to use Excel for this operation.

Any suggestions?

~wmichael

"small change can often be found under seat cushions"

 

[BuGlen]

Take a look at the Shell function in the VBA help file.

Hope this helps.

Glen Appleton

VB.Net student.

 

[wmichael]

Thanks, Glen. I have been through the SHELL command, though the syntax is eluding me. Perhaps I have been in management too long, and can no longer reason or be logical.

<sigh>

Can anyone spell it out in terms a manager can understand?

Thanks again!

~wmichael

&quot;small change can often be found under seat cushions&quot;

 

[BuGlen]

Certainly. The following example will open a command prompt window from VBA and display the return code:

-----

Sub TestShell()

    Dim dblReturn As Double
    
    dblReturn = Shell("cmd.exe", vbNormalFocus)
    MsgBox dblReturn

End Sub

-----

Be aware that the Shell function does not pause program execution while the external command is being processed. If you need to pause the program execution, the following API will do so:

-----

' Declarations
Declare Function OpenProcess Lib "kernel32" (ByVal dwDesiredAccess As Long, _
    ByVal bInheritHandle As Long, ByVal dwProcessID As Long) As Long
Declare Function GetExitCodeProcess Lib "kernel32" (ByVal hProcess As Long, _
    lpExitCode As Long) As Long

' Constants
Public Const PROCESS_QUERY_INFORMATION = &H400
Public Const STILL_ACTIVE = &H103

'==============================================================================
'   1, 5, 9 Normal with focus.
'   2   Minimized with focus.
'   3   Maximized with focus.
'   4, 8 Normal without focus.
'   6, 7 Minimized without focus.
'==============================================================================
Function ShellAndWait(ByVal Pathname As String, Optional WindowState) As Double

    Dim hProg As Long
    Dim hProcess As Long, ExitCode As Long

    'fill in the missing parameter and execute the program
    If IsMissing(WindowState) Then WindowState = 1
    hProg = Shell(Pathname, WindowState)
    'hProg is a process ID under Win32. To get the process handle:
    hProcess = OpenProcess(PROCESS_QUERY_INFORMATION, False, hProg)
    Do
      'populate Exitcode variable
      GetExitCodeProcess hProcess, ExitCode
      DoEvents
    Loop While ExitCode = STILL_ACTIVE
    
    ShellAndWait = ExitCode

End Sub

-----

Hope this helps.

Glen Appleton

VB.Net student.

 

[wmichael]

Thanks much, muchacho.... I was able to accomplish what I needed quickly.



~wmichael

&quot;small change can often be found under seat cushions&quot;