How to pass argument values from a sub to an excel VBA shell command for remote execution?

Question

I need to run a command (eventually a script) remotely on a linux server invoked locally from within a VBA macro on my windows machine. I tested the command outside of VBA from windows command prompt and it works, but I am not sure how to do the same from within the VBA macro since I need to pass argument values which are the selected values of combobox. I am also unable to redirect the output of Shell to some log file. I looked up few articles online and was able to come up with the following code:

Private Sub UserForm_Initialize()
    comboBox1.List = Array("A", "B", "C", "D")
    comboBox2.List = Array("1", "2", "3", "4")
End Sub


Private Sub CommandButton1_Click()
    Call runCommand
    Unload Me
End Sub


Sub runCommand()
    Dim Ret_Val
    Dim Arg1 As String
    Dim Arg2 As String
    Dim command As String

    Arg1 = comboBox1.Value
    Arg2 = comboBox2.value

    command = "C:\Program Files\PuTTY\plink.exe" & " -v" & " " & "user@host" & " -pw" & " " & "testpw" & " " & "echo &Arg1,&Arg2" 

    Ret_Val = Shell(command & ">C:\logs\log.txt", 1)
    If Ret_Val = 0 Then
        MsgBox "Error!", vbOKOnly
    End If
End Sub

Any idea what I am missing here please?

So the problem is, "it doesn't work". Are you getting any specific errors or anything more tangible? "here's my broken code, please fix it" isn't exactly how this site works... — Mathieu Guindon
– Mathieu Guindon, Commented Jan 10, 2019 at 21:56
You're passing the string "echo &Arg1,&Arg2" instead of substituting the values of Arg1 and Arg2 — Tim Williams
– Tim Williams, Commented Jan 10, 2019 at 21:58
You need something more like: command = "C:\Program Files\PuTTY\plink.exe -v user@host -pw testpw echo " & Arg1 & " " & Arg2 If that doesn't work, then provide an example of a known working command line of the type you're trying to generate. — Tim Williams
– Tim Williams, Commented Jan 10, 2019 at 22:07

Tim Williams · Accepted Answer · 2019-01-11 18:09:17Z

1

You're passing the string "echo &Arg1,&Arg2" instead of substituting the values of Arg1 and Arg2

You need something more like:

command = "C:\Program Files\PuTTY\plink.exe -v user@host -pw testpw echo " & _
           Arg1 & " " & Arg2

If that doesn't work, then provide an example of a known working command line of the type you're trying to generate.

answered Jan 11, 2019 at 18:09

Tim Williams

169k8 gold badges104 silver badges143 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

How to pass argument values from a sub to an excel VBA shell command for remote execution?

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related