Captura o valor de saída de um comando shell no VBA?

Encontrou esta function http://www.cpearson.com/excel/ShellAndWait.aspx

Mas também precisaria capturar a saída da saída do shell. Alguma sugestão de código?

Você pode CreateProcess o aplicativo redirecionando seu StdOut para um pipe e, em seguida, leia esse pipe diretamente; http://pastebin.com/CszKUpNS

 dim resp as string resp = redirect("cmd","/c dir") resp = redirect("ipconfig","") 

Com base na resposta de Andrew Lessard, aqui está uma function para executar um comando e retornar a saída como uma 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 

Uso:

 MsgBox ShellRun("dir c:\") 

Você sempre pode redirect a saída do shell para um arquivo e ler a saída do arquivo.

Com base na resposta do bburns.km , adicionei a input de passagem (usando StdInput) ao executável durante a chamada. Apenas no caso de alguém tropeçar e ter a mesma necessidade.

 '''  ''' Executes the given executable in a shell instance and returns the output produced ''' by it. If iStdInput is given, it is passed to the executable during execution. ''' Note: You must make sure to correctly enclose the executable path or any given ''' arguments in quotes (") if they contain spaces. '''  '''  ''' The full path to the executable (and its parameters). This string is passed to the ''' shell unaltered, so be sure to enclose it in quotes if it contains spaces. '''  '''  ''' The (optional) input to pass to the executable. Default: Null '''  Public Function ExecuteAndReturnStdOutput(ByVal iExecutablePath As String, _ Optional ByVal iStdInput As String = vbNullString) _ As String Dim strResult As String Dim oShell As WshShell Set oShell = New WshShell Dim oExec As WshExec Set oExec = oShell.Exec(iExecutablePath) If iStdInput <> vbNullString Then oExec.StdIn.Write iStdInput oExec.StdIn.Close ' Close input stream to prevent deadlock End If strResult = oExec.StdOut.ReadAll oExec.Terminate ExecuteAndReturnStdOutput = strResult End Function 

Nota: Você precisará include uma referência ao Windows Script Host Object Model para que os tipos WshShell e WshExec sejam conhecidos.
(Para isso, vá para Extras -> References na barra de menus do VBA IDE.)

 Sub StdOutTest() Dim objShell As Object Dim objWshScriptExec As Object Dim objStdOut As Object Dim rline As String Dim strline As String Set objShell = CreateObject("WScript.Shell") Set objWshScriptExec = objShell.Exec("c:\temp\batfile.bat") Set objStdOut = objWshScriptExec.StdOut While Not objStdOut.AtEndOfStream rline = objStdOut.ReadLine If rline <> "" Then strline = strline & vbCrLf & CStr(Now) & ":" & Chr(9) & rline ' you can handle the results as they are written to and subsequently read from the StdOut object Wend MsgBox strline 'batfile.bat 'ping 1.1.1.1 -n 1 -w 2000 > nul 'echo 2 'ping 1.1.1.1 -n 1 -w 2000 > nul 'echo 4 'ping 1.1.1.1 -n 1 -w 2000 > nul 'echo 6 'ping 1.1.1.1 -n 1 -w 2000 > nul 'echo 8 End Sub