Capture output value from a shell command in VBA?

by

Based on Andrew Lessard’s answer, here’s a function to run a command and return the output as a string – 

Public Function ShellRun(sCmd As String) As String

    'Run a shell command, returning the output as a string

    Dim oShell As Object
    Set oShell = CreateObject("WScript.Shell")

    'run command
    Dim oExec As Object
    Dim oOutput As Object
    Set oExec = oShell.Exec(sCmd)
    Set oOutput = oExec.StdOut

    'handle the results as they are written to and read from the StdOut object
    Dim s As String
    Dim sLine As String
    While Not oOutput.AtEndOfStream
        sLine = oOutput.ReadLine
        If sLine <> "" Then s = s & sLine & vbCrLf
    Wend

    ShellRun = s

End Function

Usage:

MsgBox ShellRun("dir c:\")

More Related Contents:

  1. Wait for Shell to finish, then format cells – synchronously execute a command
  2. Execute a command in command prompt using excel VBA
  3. VBA Shell and Wait with Exit Code
  4. Loop through files in a folder using VBA?
  5. For Each loop: Some items get skipped when looping through Outlook mailbox to delete items
  6. Pass arguments to Constructor in VBA
  7. Use of symbol # (hash) in VBA Macro
  8. Excel VBA: Can’t get a match, error “Unable to get the Match property of the WorksheetFunction class”
  9. Excel VBA looping through multiple worksheets
  10. When one should use Set [e.g for SpecialCells return value]?
  11. Is there a faster CountIF
  12. Distinction between using .text and .value in VBA Access
  13. VBA – Folder Picker – set where to start [duplicate]
  14. Difference in type between using and not using Set keyword
  15. How to do late binding in VBA?
  16. Getting the headings from a Word document
  17. VBA – Convert string to UNICODE
  18. How to break long string to multiple lines
  19. How to loop through all controls in a form, including controls in a subform – Access 2007
  20. Continue For loop
  21. FileDialog doesn’t work
  22. How to refer to Excel objects in Access VBA?
  23. Can Advanced Filter criteria be in the VBA rather than a range?
  24. In Excel VBA, what is the way to check if the web page is fully loaded?
  25. Excel Useform: How to hide application but have icon in the taskbar
  26. Reading Web Pages using Excel VBA
  27. How to get the procedure or function name at runtime?
  28. How do I get a list of unique values from a range in Excel VBA?
  29. How to get selected value in multicolumn listbox
  30. Query my excel worksheet with VBA

Leave a Comment