How to use Excel VBA to run WinSCP Script?

Question

I wrote a WinSCP script that upload a local file to a SFTP site. The script is saved into .txt:

WinSCP.com
open sftp://username:password@address
cd /export/home/Desktop/Temp
put C:\Users\Dekstop\JPMC\a.xlsx
close
exit

Then I look at this post and write this code into Excel VBA:

Sub RunWinScp()
Call Shell("C:\Program Files (x86)\WinSCP\WinSCP.com /ini=nul/script=C:\Users\Desktop\WinSCPGetNew.txt")
End Sub

But when I try to run it, nothing happens. There is no error, but the file is not transferred correctly neither. Could someone please help?

Thanks a lot!

You probably just need a space between the command line parameters: `/ini=nul /script=`. — Comintern, Oct 25 '16 at 18:43
Thanks for the advice. Tried to put a space but still doesn't work. Any other thoughts? — vivi11130704, Oct 25 '16 at 18:48
Are you sure that `C:\Users\Desktop\JPMC\a.xlsx` is a valid path? It looks like it should be `C:\Users\JPMC\Desktop\a.xlsx` (assuming JPMC is your username) — ThunderFrame, Oct 25 '16 at 18:58
yes I double checked to make sure that the path is correct. JPMC is the folder name. I eliminate my user name in this post. — vivi11130704, Oct 25 '16 at 19:23
You might wanna check out: https://stackoverflow.com/questions/37280733/using-vba-to-run-winscp-script/45785558#45785558 — Unicco, Aug 20 '17 at 18:33

score 1 · Accepted Answer · edited May 23 '17 at 10:27

1

You need to enclose the path to winscp.com with double quotes, as it contains spaces
You need a space between the /ini=nul and /script=...

Call Shell("""C:\Program Files (x86)\WinSCP\WinSCP.com"" /ini=nul /script=C:\Users\Desktop\WinSCPGetNew.txt")

Remove the winscp.com at the beginning of your WinSCPGetNew.txt script. There's no winscp.com command in WinSCP. You already run WinSCP.com by the Shell function in VBA. This is actually already covered in the question you link to yourself: Using VBA to run WinSCP script.

Though you better specify the commands on WinSCP command-line using the /command switch to avoid a need to for a separate commands file:

Call Shell( _
    """C:\Program Files (x86)\WinSCP\WinSCP.com"" " + _
    "/ini=nul " + _
    "/command " + _
    """open sftp://username:password@example.com/"" " + _
    """cd /export/home/Desktop/Temp"" " + _
    """put C:\users\Desktop\JPMC\a.xlsx"" " + _
    """close"" " + _
    """exit""")

edited May 23 '17 at 10:27

Community

1
1

answered Oct 25 '16 at 19:01

Martin Prikryl

167,268
50
405
846

Thanks for the feedback. I changed the bullet 2 and 3 as you recommended. For bullet 1, I tried to enclose everything into a double quotes by writing: Call Shell(""""C:\Program Files (x86)\WinSCP\WinSCP.com /ini=nul /script=C:\Users\Desktop\WinSCPGetNew.txt"""") but VBA highlighted this line as red. Any thoughts? – vivi11130704 Oct 25 '16 at 19:07
You have too many quotes there. And your syntax is wrong anyway. The quotes have to be around the path only! Use the code I've posted in answer. – Martin Prikryl Oct 25 '16 at 19:12
Thanks for helping out. I removed winscp.com from txt file, add space, and copied both of your codes but still none of them end up working...still no error message but no output.I feel like im so close to the answer but just can't get it... – vivi11130704 Oct 25 '16 at 19:36
I got it work!! I delete the /ini-nul part now it works!! Thank you so much! – vivi11130704 Oct 25 '16 at 19:39
1

OK, you are actually missing the `-hostkey` switch, what is also covered the in the [other question](http://stackoverflow.com/a/37284826/850848). If you remove the `/ini=nul` switch, you are relying on local host key case. So your code won't work if you transfer the xlsx file to another machine. – Martin Prikryl Oct 25 '16 at 20:28
1

You can have [WinSCP GUI generate complete command-line](https://winscp.net/eng/docs/ui_generateurl#script) (including the `-hostkey`) for you. – Martin Prikryl Oct 26 '16 at 06:40
Very helpful. Thanks a lot!! – vivi11130704 Oct 26 '16 at 15:02
One additional question: As your example showed, now I'm trying this: Call Shell("""C:\Program Files (x86)\WinSCP\WinSCP.com"" " + _ "/command " + _ """open sftp://username:password@address"" " + _ """cd /export/home/Desktop/Temp"" " + _ """lcd lcd C:\Users\Desktop\JPMC"" " + _ """get rank_macro.log"" " + _ """close"" " + _ """exit""") But nothing happens when run this code. Could you please let me know why? – vivi11130704 Oct 26 '16 at 15:24
Nothing, really? Not even a brief console window? + What happens if you execute an equivalent command in `cmd.exe` console? `"C:\Program Files (x86)\WinSCP\WinSCP.com" /command "open sftp://username:password@address" "cd /export/home/Desktop/Temp" "lcd lcd C:\Users\Desktop\JPMC" "get rank_macro.log" "close" "exit"` – Martin Prikryl Oct 26 '16 at 15:35
Nothing happens. Not even a console window. I create a new question so you can see my code in a better format. [link](http://stackoverflow.com/questions/40266569/run-winscp-script-inside-vba-doesnt-work) In the meantime, im testing it in console window now. Thanks! – vivi11130704 Oct 26 '16 at 15:43
With your **exact code**, I get a minimized console window (task bar button "WinSCP" appears), even if the connection does not really work (because of the dummy `@address`). – Martin Prikryl Oct 26 '16 at 15:49

How to use Excel VBA to run WinSCP Script?

1 Answers1