Call a Unix Script from Excel Vba

by

One option would be to open the plink session in a WScript.Shell instead of executing it with a script file using VBA’s Shell. The plink program will run in interactive mode from the command line, and the WshExec object gives you direct access to the standard input and standard output streams of the process that you’re executing. This short example demonstrates using it interactively (it logs onto the public telehack.com telnet server and executes the fnord command) with all of the console output being copied into the immediate window:

Private Sub Fnord()
    Dim shell As Object
    Set shell = CreateObject("WScript.Shell")
    Dim console As Object
    'Open plink in interactive mode.
    Set console = shell.Exec("c:\putty\plink -telnet telehack.com -P 443")
    'Wait for a command prompt.
    WaitForResponseText console, "."
    'Send the fnord command to standard input.
    console.StdIn.Write ("fnord" & vbCr)
    'Wait for the server to echo it back.
    WaitForResponseText console, ".fnord"
    'Read the standard output through the next command prompt.
    WaitForResponseText console, "."
    'Exit the telent session.
    console.StdIn.Write ("exit" & vbCr)
End Sub

Private Sub WaitForResponseText(console As Object, response As String)
    Dim out As String
    'Make sure there's output to read.
    If console.StdOut.AtEndOfStream Then Exit Sub
    Do
        'Read a line from standard output.
        out = console.StdOut.ReadLine()
        'Not strictly required, but allows killing the process if this doesn't exit.
        DoEvents
        'Send the server output to the immediate window.
        Debug.Print out
        'Check for the response we're waiting for.
        If InStr(out, response) Then
            Exit Do
        End If
    Loop Until console.StdOut.AtEndOfStream
End Sub

In your case, there isn’t much “interaction” going on with the server you’re connecting to, so it may be as easy as just sending all of your commands directly to StdIn. Given the wide range of protocol support that plink has, I’d be surprised if running the script file is substantially different that this:

Public Sub Chgaccper()
    Dim shell As Object
    Set shell = CreateObject("WScript.Shell")
    Dim console As Object
    'Open plink in interactive mode.
    Set console = shell.Exec("c:\putty\plink server Name -l uname -pw Password")
    'Send your commands to the standard input.
    console.StdIn.Write ("cd /root/home/temp" & vbCr)
    console.StdIn.Write ("chmod 666 *.csv" & vbCr)
    console.StdIn.Write ("cd /root/home/temp1" & vbCr)
    console.StdIn.Write ("chmod 666 *.csv" & vbCr)
    console.StdIn.Write ("exit" & vbCr)
End Sub

If that runs too fast, you can always test to make sure you’re getting appropriate server responses or add a short wait between sending commands to StdIn.

More Related Contents:

  1. How to select lines between two marker patterns which may occur multiple times with awk/sed
  2. Find and replace in file and overwrite file doesn’t work, it empties the file
  3. How to use multiple arguments for awk with a shebang (i.e. #!)?
  4. How to read a file into a variable in shell?
  5. How to call python script on excel vba?
  6. Execute a command in command prompt using excel VBA
  7. How to set recurring schedule for xlsm file using Windows Task Scheduler
  8. VBA Shell function in Office 2011 for Mac
  9. How to remove carriage return from a variable in shell script
  10. VBA : save a file with UTF-8 without BOM
  11. How/When does Execute Shell mark a build as failure in Jenkins?
  12. Compare two files line by line and generate the difference in another file
  13. How do you run a .exe with parameters using vba’s shell()?
  14. Floating-point arithmetic in UNIX shell script
  15. Run java jar file on a server as background process
  16. How do you tell if a string contains another string in POSIX sh?
  17. How does the #! shebang work?
  18. Code for looping through all excel files in a specified folder, and pulling data from specific cells
  19. Unix find command, what are the {} and \; for?
  20. Compare a string using sh shell
  21. VBScript – How to make program wait until process has finished?
  22. Bad data selection using VBScript , on date field values
  23. Shell one liner to prepend to a file
  24. How is % (percent sign) special in crontab?
  25. Can I call a function of a shell script from another shell script?
  26. Remove part of path on Unix
  27. Which terminal command to get just IP address and nothing else?
  28. How to add text at the end of each line in unix
  29. How to print 5 consecutive lines after a pattern in file using awk [duplicate]
  30. Unix shell script find out which directory the script file resides?

Leave a Comment