Call .com file with parameters using VBA WScript.Shell

Question

I'm using Excel to upload some files onto an server with WinSCP. This example works:

Sub FTP_upload()
    Dim logfile, ftp_login, file_to_upload, upload_to_folder As String
    logfile = "D:\temp\ftp.log"
    ftp_login = "ftp://ftp_mydomain:mypassword@mydomain.com/"
    file_to_upload = "D:\tmep\myfile.txt"
    upload_to_folder = "/myfolder/"

    'upload the file
    Call Shell("C:\Program Files (x86)\WinSCP\WinSCP.com /log=" & logfile & " /command " & """open """ & ftp_login & " " & """put " & file_to_upload & " " & upload_to_folder & """ " & """exit""")
End Sub

I now want Excel to wait until the shell has closed.

Using the information from Wait for shell command to complete, I put it together this code:

Sub FTP_upload_with_wait()
    Dim wsh As Object
    Set wsh = VBA.CreateObject("WScript.Shell")
    Dim waitOnReturn As Boolean: waitOnReturn = True
    Dim windowStyle As Integer: windowStyle = 1
    Dim errorCode  As Integer

    Dim logfile, ftp_login, file_to_upload, upload_to_folder As String
    logfile = "D:\temp\ftp.log"
    ftp_login = "ftp://ftp_mydomain:mypassword@mydomain.com/"
    file_to_upload = "D:\tmep\myfile.txt"
    upload_to_folder = "/myfolder/"

    execute_string = "C:\Program Files (x86)\WinSCP\WinSCP.com /log=" & logfile & " /command " & """open """ & ftp_login & " " & """put " & file_to_upload & " " & upload_to_folder & """ " & """exit"""

    errorCode = wsh.Run(execute_string, windowStyle, waitOnReturn)

End Sub

Unfortunately, this doesn't work. Excel reports:

run-time error '-2147024894 (80070002)'
Automation error
The system cannot find the file specified

When I replace the string this way, it works:

execute_string = "notepad.exe"

It seems that wsh.Run doesn't like the quotation marks.
How can I make this work?

Martin Prikryl · Accepted Answer · 2020-01-09T08:06:51.050

The path to WinSCP contains spaces, so you need to wrap it to double-quotes (which need to be doubled to escape them in VBA string):

execute_string = """C:\Program Files (x86)\WinSCP\WinSCP.com"" ..."

But that's only the first set of quotes that is wrong in your command.

The correct command would be like:

execute_string = """C:\Program Files (x86)\WinSCP\WinSCP.com"" " & _
    "/log=" & logfile & " /command " & _
    """open " & ftp_login & """ " & _
    """put " & file_to_upload & " " & upload_to_folder & """ " & _
    """exit"""

Assuming that none of logfile, ftp_login, file_to_upload and upload_to_folder contains spaces, in which case would would need a way more double-quotes.

Read ore about WinSCP command-line syntax

The Call Shell must have some heuristics that adds the quotes around C:\Program Files (x86)\WinSCP\WinSCP.com. Though it's just a pure luck that the rest of the command-line works, the quotes are wrong there too. So even your first code is wrong. It runs the following command:

"C:\Program Files (x86)\WinSCP\WinSCP.com" /log=D:\temp\ftp.log /command "open "ftp://ftp_mydomain:mypassword@mydomain.com/ "put D:\tmep\myfile.txt /myfolder/" "exit"

(Note the misplaced quotes around open)

Call .com file with parameters using VBA WScript.Shell

1 Answers1