从 Excel Vba 调用 Unix 脚本 [英] Call a Unix Script from Excel Vba

问题描述

我正在尝试使用 Plink(腻子命令行)从 VBA 调用一组 Unix 命令，但这些命令没有被执行.我会发布代码，任何更正或建议都会有所帮助.

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.

也欢迎其他想法，我所要做的就是访问 unix 文件更改访问权限并将文件移动到其他文件夹.

alternative Ideas are also Welcomed , All i have to do is Access unix file change access permission and move the files to other folders.

请找到下面的代码

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:WindowsSystem32") = False Then
Shell "C:WINNTsystem32cmd.exe -s:" & vscript & ""
Else
Shell "C:WINDOWSsystem32cmd.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 命令)，并将所有控制台输出复制到即时窗口中:

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:puttyplink -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

在您的情况下，与您连接的服务器之间没有太多交互"，因此可能就像将您的所有命令直接发送到 StdIn 一样简单.鉴于 plink 具有广泛的协议支持，如果运行脚本文件与此有很大不同，我会感到惊讶:

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:puttyplink 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

如果运行速度太快，您可以随时进行测试以确保您获得适当的服务器响应，或者在向 StdIn 发送命令之间添加短暂的等待.

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屋！