从Excel Vba调用Unix脚本

Question

我正在尝试使用Plink（putty命令行）从VBA调用一组Unix命令，但是这些命令没有被执行。我将发布代码，任何更正或建议都将有所帮助。

也欢迎提供其他想法，我所要做的就是访问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

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

- user2834105

---

你的代码有什么结果？你有一个 Chg.txt 文件吗？你能展示一下它的内容吗？当你尝试直接执行脚本时会发生什么？ - Walter A

Answer 1

一种选择是在 WScript.Shell 中打开plink会话，而不是使用VBA的Shell执行脚本文件。 plink 程序将在命令行中以交互模式运行，而 WshExec 对象可以直接访问您正在执行的进程的标准输入和标准输出流。以下示例演示了如何交互地使用它（它登录到公共 telnet 服务器 telehack.com 并执行 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

在你的情况下，与你连接的服务器之间并没有太多的“交互作用”，因此只需将所有命令直接发送到 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

如果运行速度过快，你可以测试以确保你正在获得适当的服务器响应，或在发送命令到StdIn之间添加短暂等待。

- Comintern

Answer 2

你打开和写入文件的方式存在问题：

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

您正在查找下一个可用的文件编号，将该编号存储为变量"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

参考：https://msdn.microsoft.com/zh-cn/library/office/gg264526.aspx

这篇文章介绍了如何使用Microsoft Graph API来访问和管理Office 365中的用户、组、邮件、日历等资源。通过使用Graph API，开发者可以轻松地从多种设备和平台上获取和更新Office 365数据。同时，Graph API还支持OAuth 2.0身份验证，保证了数据的安全性。

- Oystein

Answer 3

这个方法会创建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

- tlemaster