VBA SendKeys to specific window

Question

I have a spreadsheet that summarizes usage of a server. The spreadsheet references a text file that is updated when I have VBA send some commands to the Linux server through the Windows command prompt (and Cygwin). My problem is that the only way I have found to execute commands in Cygwin is through the SendKeys command. This results in two problems:

1. I have to trust that the user will not click somewhere until the script is done running.
2. I have to hardcode the amount of time to wait for the command to be executed before the next command can be sent.

I would (and did) try creating a bash file to run in Cygwin, but I don't know of a way to do this that also allows the user to enter their password to log into the Linux server. I am currently getting the user's password from a UserForm that is run before the main code is executed. This password is then saved to a "very hidden" worksheet that is deleted when the application is closed.

I am currently using the following script to run the commands:

Public Sub test()
    ' Declare Variables
    Dim cmd As String
    Dim ret As Double
    Dim LastRow As Integer
    Dim PssWrd As String

    PssWrd = Worksheets("PssWrd").Range("A1").Value

    ' Run Linux Commands
    cmd = "C:\cygwin\Cygwin.bat"
    ret = Shell(cmd, vbMinimizedFocus)
    wait 0.02
    cmd = "ssh " & Worksheets("Settings").Range("LOGIN") & "~" ' Format: user@hostname
    SendKeys cmd
    cmd = PssWrd & "~"
    SendKeys cmd
    wait 2.78
    cmd = "{(} date ; fs ; qstat ; date {)} > & zout &~"
    SendKeys cmd
    cmd = "exit~"
    SendKeys cmd
    wait 0.5
    cmd = "exit~"
    SendKeys cmd
    wait 5
    ' Update PivotTable and other data
End Sub
Public Sub wait(PauseTime As Double)
    Dim Start As Double
    Dim Finish As Double
    Dim TotalTime As Double
    Start = Timer    ' Set start time
    Do While Timer < Start + PauseTime
        DoEvents    ' Yield to other processes
    Loop
    Finish = Timer    ' Set end time
    TotalTime = Finish - Start    ' Calculate total time
End Sub

Is there any way to at least send the keystrokes to a specific window instead of just the active window? Any help would be greatly appreciated, and any additional necessary information will be readily provided.

Answer 1

Okay. I think I found the solution. Thanks for all the help (@anishsane) pointing me in the right direction! Here is my updated code:

Public Sub test()
    ' Declare Variables
    Dim cmd As String
    Dim ret As Double
    Dim LastRow As Integer
    Dim PssWrd As String
    Dim WshShell As Object
    Dim plink_object As Object

    PssWrd = Worksheets("PssWrd").Range("A1").Value

    ' Run Linux Commands
    Set WshShell = CreateObject("WScript.Shell")
    On Error Resume Next
    Set plink_object = WshShell.Run("""C:\Program Files (x86)\PuTTY\plink.exe"" -ssh " & Worksheets("Settings").Range("LOGIN") & " -pw " & PssWrd & " ""(date; fs; qstat; date) > &zout&""", 7, True)
    On Error GoTo 0
    ' Update PivotTable and other data
End Sub

Evidently, in VBA the WScript is not needed. Also, wshshell.exec seems to fail at some point because the commands never get executed (though PuTTY/Plink is started). For some reason, the WshShell.Run command resulted in the "Run-time error '424': Object required" error, but the essential part of the commands still get executed properly, so I just have the error being ignored. I also figured out how to execute my command all in one line so I don't have to worry about the stdIn.Write command that wasn't working before. I could use the Shell() command, but I need to make sure that the command has finished executing (via the bWaitOnReturn option) to make sure I have the most up-to-date file when the update script runs. Having said that, I believe that since I am having Linux write out the output of the command to a file, the file hasn't finished updating when VBA says the command has finished executing. However, I believe I've figured out a means to check that the last line of text is formatted correctly in the file (since it should be the date), so that shouldn't be a problem.