使用VBA运行WinSCP脚本

I am able to download files from SFTP in CMD window, by using following code:

我可以使用以下代码在CMD窗口中从SFTP下载文件：

WinSCP.com
# Connect to the host and login using password
open user:pw@address
# get all the files in the remote directory and download them to a specific local directory
lcd C:\Users\xx\Desktop
get *.xlsx
# Close and terminate the session
exit

I searched online and found out that I can put these codes in a bat file and use

我在网上搜索，发现我可以把这些代码放在一个bat文件中并使用

Call Shell("cmd.exe /c C:\Users\xx\Desktop\WinSCPGet.bat", 1)

However, only the first line of the bat file WinSCP.com is being executed. It will pop up the cmd window, showing this, without doing anything else.

但是，只有bat文件WinSCP.com的第一行正在执行。它将弹出cmd窗口，显示此信息，而不执行任何其他操作。

How to execute all the lines at one time?

如何一次执行所有行？

Thanks

谢谢

2 个解决方案

#1

The code you have is not a Windows batch file. It's one Windows command followed by WinSCP commands. The first command runs winscp.com application, which then sits and waits for input. If you eventually close it, Windows command interpreter (cmd.exe) will carry on executing the remaining commands, failing most, as they are not Windows commands. See also WinSCP script not executing in batch file.

您拥有的代码不是Windows批处理文件。它是一个Windows命令，后跟WinSCP命令。第一个命令运行winscp.com应用程序，然后它坐下并等待输入。如果你最终关闭它，Windows命令解释器（cmd.exe）将继续执行剩余的命令，失败最多，因为它们不是Windows命令。另请参见不在批处理文件中执行的WinSCP脚本。

So you either have to save the commands (open to exit) to a WinSCP script file (say script.txt) and execute the script using the /script switch:

因此，您必须将命令（打开以退出）保存到WinSCP脚本文件（例如script.txt）并使用/ script开关执行脚本：

Call Shell("C:\path\winscp.com /ini=nul /script=c:\path\script.txt")

Alternatively, specify all commands on WinSCP command line, using the /command switch:

或者，使用/ command开关在WinSCP命令行上指定所有命令：

Call Shell("C:\path\winscp.com /ini=nul /command ""open user:pw@address"" ""lcd C:\Users\xx\Desktop"" ""get *.xlsx"" ""exit""")

Regarding the quotes: With the /command switch, you have to enclose each command to double-quotes. In VBA string, to use a double-quote, you have to escape it by doubling it.

关于引号：使用/ command开关，您必须将每个命令括在双引号中。在VBA字符串中，要使用双引号，您必须通过加倍来逃避它。

Also note that you generally should use the /ini=nul switch to isolate the WinSCP script run from your WinSCP configuration. This way you can also make sure that the script will run on other machines. Your script won't, as it lacks the -hostkey switch to verify the SSH host key fingerprint. Using the /ini=nul will help you realize that.

另请注意，通常应使用/ ini = nul开关将WinSCP脚本运行与WinSCP配置隔离开来。这样，您还可以确保脚本将在其他计算机上运行。您的脚本不会，因为它缺少-hostkey开关来验证SSH主机密钥指纹。使用/ ini = nul将帮助您实现这一点。

You can have WinSCP GUI generate complete command-line (including the -hostkey) for you.

您可以让WinSCP GUI为您生成完整的命令行（包括-hostkey）。

另请参阅使用WinSCP自动将文件传输到SFTP服务器。

#2

I like this small and compact procedure, and use it in my own projects. No temp-files required. Fast and reliable.

我喜欢这个小而紧凑的程序，并在我自己的项目中使用它。无需临时文件。快速可靠。

Parse a string src (an absolute filepath) to uploadImageByFTP. Etc. C:\Users\user\Desktop\image.jpg, and the file will be uploaded.

将字符串src（绝对文件路径）解析为uploadImageByFTP。等等C：\ Users \ user \ Desktop \ image.jpg，文件将被上传。

Replace:

更换：

<username> with FTP-User
使用FTP-User的
<password> with FTP-Password
使用FTP密码
<hostname> with FTP-hostname (etc. example.com)
带有FTP主机名的（例如example.com）
<WinSCP.com path> with path on your WinSCP-client (etc. C:\Program Files (x86)\WinSCP\WinSCP.com. Caution: WinSCP.com and not WinSCP.exe)
在WinSCP客户端上使用路径的（等等C：\ Program Files（x86）\ WinSCP \ WinSCP.com。警告：WinSCP.com而不是WinSCP.exe）路径>
<FTP-path> with path on your FTP-client (etc. /httpdocs/wp-content/uploads)
带有FTP客户端路径的（等等/ httpdocs / wp-content / uploads）

    Sub uploadImageByFTP(src As String)
        Dim script As Object: Set script = VBA.CreateObject("WScript.Shell")
        Dim waitOnReturn As Boolean: waitOnReturn = True
        Dim windowStyle As Integer: windowStyle = 1

        'Not empty
        If (src <> vbNullString) Then

           'Execute script 
            script.Run _
                """<WinSCP.com path>"" " + _
                "/ini=nul " + _
                "/command " + _
                """open ftp://<username>:<password>@<hostname>/"" " + _
                """cd <FTP-path>"" " + _
                """put " & """""" & src & """""" & """ " + _
                """close"" " + _
                """exit""", windowStyle, waitOnReturn

        End If

    End Sub

WScript.Shell is more powerful than the default Shell(), as you can append a waitOnReturn-command; this tells VBA, that further execution isn't allowed before the file(s) have been uploaded to the FTP-server.

WScript.Shell比默认的Shell（）更强大，因为你可以附加一个waitOnReturn命令;这告诉VBA，在文件上传到FTP服务器之前不允许进一步执行。

Change windowStyle to 0, if you don't like the command prompt to open on each execution.

如果您不希望在每次执行时打开命令提示符，请将windowStyle更改为0。

#1