PowerShell with Excel

[MSerg]

Hello. I want to make a list of TCP hosts in an Excel book and test connection by Test-NetConnection. It looks as follow -
Dim a
Dim b As Boolean

Set a = CreateObject("WScript.Shell")
b = a.Run("powershell TNC 192.168.26.10 -CommonTCPPort RDP -InformationLevel Quiet", 0, True)

it works, powershell runs, TNC works and returns True/False if port is opened or closed.
But! it doesn't return correct result to b. It always returnes FALSE if syntax is correct and TRUE if syntax is wrong.
It does not return the result of test connection. Whether the port is open or closed b is FALSE.

If anyone knows how to make TNC return the correct answer please tell me

 

[Dan_W]

The Run method only returns error codes, and not the output of the command you're running. For that you need to use the Exec method.
I note from your Run command, though, that you've opted for it to hide the window and to wait for the command to complete before moving on (the second and third parameters respectively).
The Exec method has neither option as far as I'm aware (or can see from a quick search - someone else had that problem) - but it is still possible to get it to wait until the command using the function I wrote below. Let's call that Solution 1. I set out a workaround to your current method below: Solution 2.

Solution 1

Sub TestCommandLine_Method1()

    Dim Command As String
    Command = "powershell TNC 192.168.26.10 -CommonTCPPort RDP -InformationLevel Quiet"
    Debug.Print GetCMDOutput(Command)

End Sub

Function GetCMDOutput(ByVal Command As String) As String
    
    Dim WShell  As Object
    Dim Exec    As Object
    Dim Output  As String
    
    Const WshRunning = 0
    Const WshFinished = 1
    Const WshFailed = 2
    
    Set WShell = CreateObject("WScript.Shell").Exec(Command)
    
    While WShell.Status = WshRunning
        Pause 0.05
    Wend

    If WShell.Status = WshFailed Then
        GetCMDOutput = WShell.StdErr.ReadAll
    Else
        GetCMDOutput = WShell.StdOut.ReadAll
    End If

    Set WShell = Nothing
    Set Exec = Nothing

End Function

Sub Pause(period As Single)
    
    Dim T As Single
    T = Timer
    Do
        DoEvents
    Loop While T + period > Timer

End Sub

Solution 2
As an alternative, you could continue using the Run method, but then outputting the results to a text file, and then reading those results back with FileSystemObject:

    Function QuickRead(ByVal Filename) As String
        QuickRead= CreateObject("Scripting.FileSystemObject").OpenTextFile(Filename, 1).ReadAll
    End Function

I'm not all that familiar with powershell, though, so you would need to adjust the command below to output to a given file.

Sub TestCommandLine_Method2()
     Dim Command As String
    Command = "powershell TNC 192.168.26.10 -CommonTCPPort RDP -InformationLevel Quiet > D:\OUTPUTFILE.TXT"   ' I don't know what the syntax is to dump output into file in Powershell
    RunCommand Command, 0, True
    Debug.print QuickRead("D:\OUTPUTFILE.TXT")

End Sub

Sub RunCommand(ByVal Command As String, Optional HideWindow As Long = 0, Optional WaitForCompletion As Boolean = True)

    Dim WShell As Object
    Dim Ret As Long

    Ret = CreateObject("WScript.Shell").Run(Command, HideWindow, WaitForCompletion)

End Sub

Does that help?

 

[MSerg]

Thank you very much for so detailed answer. I must say that RUN command itself doesn't return only error codes. It depends. For example if you search youtube you will find a lot of videos where people makes Excel ping hosts. And in case of PING command like that - p = a.Run("PowerShell ping -n 5 -w 1000 192.168.26.10", 0,False) (I also want to say that you don't need to run ping with powershell, you can simply write p = a.Run("ping -n 5 -w 1000 192.168.26.10", 0,False)) it will return the result of ping itself, True of False and not only error.
So my conclusion is that the problem is in TNC commandlet, it is TNC who doesn't return what I neet to "b" with Run command. These commands don't match unfortunately and to make in Excel environment what I want is simply not possible.

 

[MSerg]

Thanks for your help again. Yes your advice works, though I don't like making temp. files outside of excel. But this looks the only way to solve it.

 

[John_w]

The Exec method has neither option as far as I'm aware (or can see from a quick search - someone else had that problem) - but it is still possible to get it to wait until the command using the function I wrote below. Let's call that Solution 1.

You don't need the While ... Wend wait loop if you create separate objects for the "WScript.Shell" object (WshShell class) and its Exec function (WshExec class):

Public Sub Exec_PS_TNC()

    Dim WShell As Object, WSExec As Object
    Dim PScommand As String, DOScommand As String, PSoutput As String
   
    PScommand = "TNC 192.168.26.10 -CommonTCPPort RDP -InformationLevel Quiet"

    DOScommand = "powershell.exe -WindowStyle Minimize -command " & PScommand
   
    Set WShell = CreateObject("WScript.Shell")
    Set WSExec = WShell.Exec(DOScommand)
    PSoutput = WSExec.StdOut.ReadAll
   
    Debug.Print PSoutput
   
End Sub

 

[Dan_W]

Hold the presses! I stumbled across a neater solution (but it uses the clipboard....). I assume Powershell has piping capabilities, but basically it uses the Clip program which should exist on all Windows systems.

Sub TestCommandLine_Method3()
    Dim Command As String
    Command = "powershell TNC 192.168.26.10 -CommonTCPPort RDP -InformationLevel Quiet T"
    Debug.Print GetOutput(Command)
End Sub

Function GetOutput(ByVal Command As String, Optional HideWindow As Long = 0, Optional WaitForCompletion As Boolean = True)
    Dim WShell As Object, Ret As Long, Result As String
    Ret = CreateObject("WScript.Shell").Run(Command & " | Clip", HideWindow, WaitForCompletion)
    Result = CreateObject("htmlfile").ParentWindow.ClipboardData.GetData("text")
    GetOutput = Result
End Function

 

[Dan_W]

I stumbled across a neater solution

(I should just qualify that I hadn't seen John_w's reply before sending my own, so I meant a "neater solution" than that which I offered yesterday )

 

[MSerg]

Yes! Wow, it really works, first send to the ClipBoard then take it from Clipboard to the variable or I can even send directly to the certain cell on the excel sheet.
Thanks buddy

 

[John_w]

Hold the presses! I stumbled across a neater solution (but it uses the clipboard....). I assume Powershell has piping capabilities, but basically it uses the Clip program which should exist on all Windows systems.

Sub TestCommandLine_Method3()
    Dim Command As String
    Command = "powershell TNC 192.168.26.10 -CommonTCPPort RDP -InformationLevel Quiet T"
    Debug.Print GetOutput(Command)
End Sub

Function GetOutput(ByVal Command As String, Optional HideWindow As Long = 0, Optional WaitForCompletion As Boolean = True)
    Dim WShell As Object, Ret As Long, Result As String
    Ret = CreateObject("WScript.Shell").Run(Command & " | Clip", HideWindow, WaitForCompletion)
    Result = CreateObject("htmlfile").ParentWindow.ClipboardData.GetData("text")
    GetOutput = Result
End Function

Your code didn't work for me. The Debug.Print GetOutput(Command) line just outputs whatever I'd last put in the clipboard, so it looks like the | Clip piping isn't working for me.

 

[Dan_W]

Glad to hear it's working for you - I hoped it would work, but wasn't sure if it's use of the clipboard might be a dealbreaker for you.
Slightly concerned that it didn't work for John_w though. I had understood that Clip was available on all windows systems - there should be a CLIP.EXE in C:\Windows\System32\ and\or C:\Windows\SYSWOW64\.

In any event, your point about creating separate objects for the "WScript.Shell" object (WshShell class) and its Exec function (WshExec class) is extremely helpful - I hadn't seen that solution anywhere else, so thank you very much for sharing it.