Using VBA to run WinSCP script

Question

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

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

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.

How to execute all the lines at one time?

Thanks

Answer 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 and WinSCP FAQ Why are some WinSCP scripting commands specified in a batch file not executed/failing?

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:

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:

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.

---

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.

---

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

See also Automating file transfers to SFTP server with WinSCP.

Answer 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.

Replace:

• <username> with FTP-User
• <password> with FTP-Password
• <hostname> with FTP-hostname (etc. 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)
• <FTP-path> with path on your FTP-client (etc. /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.

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