从 Excel Vba 调用 Unix 脚本
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/20796017/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Call a Unix Script from Excel Vba
提问by user2834105
Im trying to call a set of Unix commands from VBA using Plink ( putty Command Line) but the commands are not getting Executed . I ll post the code any corrections or suggestions would be helpful.
我试图使用 Plink（腻子命令行）从 VBA 调用一组 Unix 命令，但这些命令没有得到 Executed 。我会发布代码，任何更正或建议都会有所帮助。
alternative Ideas are also Welcomed , All i have to do is Access unix file change access permission and move the files to other folders.
也欢迎其他想法，我所要做的就是访问 unix 文件更改访问权限并将文件移动到其他文件夹。
Pls find the 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
回答by Comintern
One option would be to open the plinksession in a WScript.Shellinstead of executing it with a script file using VBA's Shell. The plinkprogram will run in interactive mode from the command line, and the WshExecobject 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 fnordcommand) with all of the console output being copied into the immediate window:
一种选择是plink在 a 中打开会话，WScript.Shell而不是使用 VBA 的Shell. 该plink程序将从命令行以交互模式运行，该WshExec对象使您可以直接访问您正在执行的进程的标准输入和标准输出流。这个简短的示例演示了交互式使用它（它登录到公共 telehack.com telnet 服务器并执行fnord命令），并将所有控制台输出复制到即时窗口中：
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 plinkhas, I'd be surprised if running the script file is substantially different that this:
在您的情况下，与您连接的服务器没有太多“交互”，因此可能就像将所有命令直接发送到StdIn. 鉴于具有广泛的协议支持，plink如果运行脚本文件与此有很大不同，我会感到惊讶：
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.
如果运行速度太快，您始终可以进行测试以确保您获得适当的服务器响应或在向StdIn.
回答by Oystein
There's a problem with how you open and write to the file:
打开和写入文件的方式存在问题：
fNum = FreeFile()
Open vPath & "\Chg.txt" For Output As #1
Print #1, "c:\"
You're checking the next available file number, storing the number as a variable "fNum" and then opening a file as #1, regardless of what FreeFile() returned. You might have a file number conflict, as far as I can see. Also, on my end the "-s:" as command line argument fails. Try using a .cmd file instead, and call it as a command:
您正在检查下一个可用的文件编号，将该编号存储为变量“fNum”，然后将文件作为 #1 打开，无论 FreeFile() 返回什么。据我所知，您可能有文件号冲突。另外，在我的最后，“-s：”作为命令行参数失败。尝试改用 .cmd 文件，并将其作为命令调用：
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
Ref: https://msdn.microsoft.com/en-us/library/office/gg264526.aspx
参考：https: //msdn.microsoft.com/en-us/library/office/gg264526.aspx
回答by tlemaster
This method creates 2 files. One (chg.bat) which invokes Plink and logs into the server. Additionally it instructs Plink to execute the commands in the 2nd file (commands.txt).
此方法创建 2 个文件。一个 (chg.bat) 调用 Plink 并登录到服务器。此外，它还指示 Plink 执行第二个文件 (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
