1

I have this code that I am running: 

Public Function checkhost(hostname As String)
    'bla bal...
    strexec = "powershell -WindowStyle Hidden -Command ping " & hostname & " -n 1"
    Set oExec = WshShell.exec(strexec)
    'bla bla...
End Function

From my Excel document, the shell does not close - it remains open. The same code does work, if I use it in Powershell.

With cmd it works fine but I want to hide the cmd window.

strexec = "%comspec% /C %WINDIR%\system32\ping.exe " & hostname & " -n 1"
Set oExec = WshShell.exec(strexec)

AFAIK the only way to hide CMD is to use WshShell.Run but I don't know how to change my existing code. Can anyone suggest a solution?

Complete code

    Public Function checkhost(hostname As String)

    Dim WshShell As Variant
    Set WshShell = CreateObject("Wscript.Shell")

    Dim ReturnErrorLevel As Variant
    strexec = "powershell -WindowStyle Hidden -Command ping " & hostname & " -n 1"
    Set oExec = WshShell.exec(strexec)



    Do Until oExec.Status = 1
        Application.Wait (Now + TimeValue("00:00:01"))
    Loop

    Result = Split(oExec.StdOut.readAll, vbCrLf, , vbTextCompare)

    For Each Item In Result
        Debug.Print Item
        If InStr(1, Item, "Ping request could not find host", vbTextCompare) > 0 Then
            checkhoststatus = "0.0.0.0"
            Exit For
        End If

        If InStr(1, Item, "Request timed out.", vbTextCompare) > 0 Then
            checkhoststatus = "0.0.0.0"
           Exit For
        End If

        If InStr(1, Item, "Reply from ", vbTextCompare) > 0 Then
            checkhoststatus = Mid(Item, 12, InStr(1, Item, ":", vbTextCompare) - 12)
            Exit For
        End If

    Next Item
End Function
Community
  • 1
  • 1
Ingo Miller
  • 11
  • 3
  • 1
    this looks a more solid approach http://stackoverflow.com/questions/21020077/ping-ip-address-with-vba-code-and-return-results-in-excel – Mike Miller Dec 07 '16 at 12:54
  • `;` is a separator for commands in powershell. You can tell PS to exit after your ping command like this: `strexec = "powershell -WindowStyle Hidden -Command ping " & hostname & " -n 1 ;exit"` – henrycarteruk Dec 07 '16 at 12:54
  • @JamesC. Thanks and nice to know but nothing has changed, the window keeps, don't exit. The script remains in the loop and wait for closing the shell. `Do Until oExec.Status = 1 Application.Wait (Now + TimeValue("00:00:01")) Loop` – Ingo Miller Dec 07 '16 at 13:29
  • 1
    Ah that's a shame, next try is WshShell.Run which you'd use like this: `Set oExec = WshShell.Run strexec,0,True` The `0` hides CMD prompt and `True` makes the script wait for the command to finish before it continuing. (I don't have Excel so can't test this so am putting it as a comment not an answer) – henrycarteruk Dec 07 '16 at 13:42
  • Since your evaluation of the ping results resolves to a true/false why not use test-connection instead of ping `strexec = "powershell -WindowStyle Hidden -Command test-Connection " & hostname & " -count 1 -quiet"` –  Dec 07 '16 at 21:57
  • @JamesC. If I try your last code I get a "runtime error 13 type mismatch", if I change the code to `Set oExec = WshShell.Run(strexec, 0, True)` the error massage is "compile error object required". @LotPings Hi, thank you. Also with your code the powershell remains open after the run. @all Should I upload the complete document? – Ingo Miller Dec 07 '16 at 23:47

0 Answers0