从Excel Vba调用Unix脚本 [英] Call a Unix Script from Excel Vba
问题描述
替代想法也欢迎，我所要做的是访问unix文件更改访问权限并移动文件到其他文件夹。
找到以下代码
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
设置fso = CreateObject（scripting.filesystemobject）
vPath = ThisWorkbook.Path
'为ftp安装文件命令。 exe
fNum = FreeFile（）
打开vPath& \Chg.txt输出为＃1
打印＃1，c：\
打印＃1，设置PATH =& vPath& ;％PATH％
打印＃1，
打印＃1，plink服务器名称-l uname -pw密码
打印＃1，
打印＃1，cd / root / home / temp
打印＃1，
打印＃1，chmod 666 * .csv
打印＃1，
打印＃1，cd / root / home / temp1
打印＃1，
打印＃1，chmod 666 * .csv
打印＃1，
打印＃1，退出
打印＃1，
关闭＃1
vscript =& vPath& \Chg.txt
如果fso.FolderExists（C：\Windows\System32）= False然后
ShellC：\WINNT\system32\ cmd.exe -s：& vscript&
Else
ShellC：\WINDOWS\system32\cmd.exe -s：& vscript&
End If
SetAttr vPath& \Chg.txt，vbNormal
Kill vPath& \Chg.txt
End Sub
一个选项是在 WScript.Shell 中打开 plink 而不是使用VBA的 Shell 使用脚本文件执行它。 plink 程序将以交互模式从命令行运行，而 WshExec 对象可让您直接访问标准您正在执行的进程的输入和标准输出流。这个简短的例子演示如何使用它进行交互式访问（它登录到公共telehack.com telnet服务器并执行 fnord 命令），所有的控制台输出被复制到立即窗口中：
Private Sub Fnord（）
Dim shell As Object
设置shell = CreateObject（WScript.Shell）
Dim console As Object
'以交互模式打开plink。
设置console = shell.Exec（c：\putty\plink -telnet telehack.com -P 443）
'等待命令提示符。
WaitForResponseText控制台，。
'将fnord命令发送到标准输入。
console.StdIn.Write（fnord& vbCr）
'等待服务器回传它。
WaitForResponseText控制台，.fnord
'通过下一个命令提示符读取标准输出。
WaitForResponseText控制台，。
'退出远程会话。
console.StdIn.Write（exit& vbCr）
End Sub
Private Sub WaitForResponseText（console As Object，response As String）
Dim out As String
'确保有要读取的输出。
如果console.StdOut.AtEndOfStream然后退出Sub
Do
'从标准输出读取一行。
out = console.StdOut.ReadLine（）
'不是严格要求的，但如果没有退出，则允许杀死进程。
DoEvents
'将服务器输出发送到即时窗口。
Debug.Print out
'检查我们正在等待的响应。
如果InStr（out，response）然后
退出Do
结束If
循环直到console.StdOut.AtEndOfStream
End Sub
在您的情况下，您正在连接的服务器没有太多的交互，所以它可能会像只需将所有命令直接发送到 StdIn 。鉴于 plink 具有广泛的协议支持，如果运行脚本文件与以下不同：我们会感到惊讶：
Public Sub Chgaccper（）
Dim shell As Object
设置shell = CreateObject（WScript.Shell）
Dim console As Object
'以交互模式打开plink。
设置console = shell.Exec（c：\putty\plink server name -l uname -pw Password）
'将命令发送到标准输入。
console.StdIn.Write（cd / root / home / temp& vbCr）
console.StdIn.Write（chmod 666 * .csv& vbCr）
控制台。 StdIn.Write（cd / root / home / temp1& vbCr）
console.StdIn.Write（chmod 666 * .csv& vbCr）
console.StdIn.Write（exit & vbCr）
End Sub
如果运行速度太快，可以随时测试确保您得到适当的服务器响应或在发送命令之间添加一个短暂的等待到 StdIn 。
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.
alternative Ideas are also Welcomed , All i have to do is Access unix file change access permission and move the files to other folders.
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
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.
这篇关于从Excel Vba调用Unix脚本的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
