Call Unix Script from Excel Vba

Question

I am trying to call a Unix command set from VBA using Plink (putty Command Line), but the commands do not get Executed. I will post the code, any corrections or suggestions will be useful.

Alternative ideas are also welcome. All I have to do is access permission on unix files and move the files to other folders.

Find our code below

Public Sub Chgaccper() Dim vPath As String Dim vFile As String Dim vSubpath As String Dim vscript As String Dim fNum As Long Dim oShell Set fso = CreateObject("scripting.filesystemobject") vPath = ThisWorkbook.Path 'Mounting file command for ftp.exe fNum = FreeFile() Open vPath & "\Chg.txt" For Output As #1 Print #1, "c:\" Print #1, "set PATH=" & vPath & ";%PATH% " Print #1, " " Print #1, "plink server Name -l uname -pw Password " Print #1, " " Print #1, "cd /root/home/temp " Print #1, " " Print #1, "chmod 666 *.csv " Print #1, " " Print #1, "cd /root/home/temp1 " Print #1, " " Print #1, "chmod 666 *.csv " Print #1, " " Print #1, "exit " Print #1, " " Close #1 vscript = "" & vPath & "\Chg.txt" If fso.FolderExists("C:\Windows\System32") = False Then Shell "C:\WINNT\system32\cmd.exe -s:" & vscript & "" Else Shell "C:\WINDOWS\system32\cmd.exe -s:" & vscript & "" End If SetAttr vPath & "\Chg.txt", vbNormal Kill vPath & "\Chg.txt" End Sub 

Answer 1

One option is to open a plink session in WScript.Shell instead of executing it with a script file using VBA Shell . The plink program runs interactively from the command line, and the WshExec object gives you direct access to the standard input and standard output from the process you are running. This short example demonstrates its interactive use (it logs on to the telhack.com telnet server system and executes the fnord command), and all console output is copied to the nearest 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 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 is not much “interaction” with the server you are connecting to, so it can be as simple as sending all your commands directly to StdIn . Given the wide range of protocol support that plink has, I would be surprised if running a script file is significantly different from 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 this runs too fast, you can always check to make sure you get the appropriate server responses or add a short wait between sending commands to StdIn .

Answer 2

The problem with the way you open and write the file:

 fNum = FreeFile() Open vPath & "\Chg.txt" For Output As #1 Print #1, "c:\" 

You check the next available file number, saving it as the "fNum" variable, and then open the file as # 1, regardless of what FreeFile () returns. As far as I know, you may have a file number conflict. Also, at my end, the -s: command line argument fails. Instead, try using the .cmd file and name it as the command:

 Public Sub Chgaccper() Dim vPath As String Dim vFile As String Dim vSubpath As String Dim vscript As String Dim fNum As Long Dim oShell Set fso = CreateObject("scripting.filesystemobject") vPath = ThisWorkbook.Path 'Mounting file command for ftp.exe fNum = FreeFile() Open vPath & "\Chg.cmd" For Output As fNum Print #fNum, "c:\" Print #fNum, "set PATH=" & vPath & ";%PATH% " Print #fNum, " " Print #fNum, "plink server Name -l uname -pw Password " Print #fNum, " " Print #fNum, "cd /root/home/temp " Print #fNum, " " Print #fNum, "chmod 666 *.csv " Print #fNum, " " Print #fNum, "cd /root/home/temp1 " Print #fNum, " " Print #fNum, "chmod 666 *.csv " Print #fNum, " " Print #fNum, "exit " Close #fNum vscript = "" & vPath & "\Chg.cmd" If fso.FolderExists("C:\Windows\System32") = False Then Shell "C:\WINNT\system32\cmd.exe /k " & vscript & "" Else Shell "C:\WINDOWS\system32\cmd.exe /k " & vscript & "" End If SetAttr vPath & "\Chg.cmd", vbNormal Kill vPath & "\Chg.cmd" End Sub 

Link: https://msdn.microsoft.com/en-us/library/office/gg264526.aspx

Answer 3

This method creates 2 files. One (chg.bat) that calls Plink and registers with the server. In addition, he instructs Plink to execute commands in the second file (commands.txt).

 Public Sub Chgaccper() Dim vPath As String Dim vscript As String vPath = ThisWorkbook.Path Open vPath & "\Chg.bat" For Output As #1 Print #1, "c:\" Print #1, "set PATH=" & vPath & ";%PATH% " Print #1, "plink ServerName -l uname -pw Password -m commands.txt" Close #1 Open vPath & "\commands.txt" For Output As #2 Print #2, "cd /root/home/temp" Print #2, "chmod 666 *.csv" Print #2, "cd /root/home/temp1" Print #2, "chmod 666 *.csv" Print #2, "exit" Close #2 vscript = "" & vPath & "\Chg.bat" Shell vscript SetAttr vPath & "\Chg.bat", vbNormal SetAttr vPath & "\commands.txt", vbNormal Kill vPath & "\Chg.bat" Kill vPath & "\commands.txt" End Sub