Upload File with winscp and sftp

[jochryem]

I have a folder that contains a zip file and I need to write vba code in excel that will open winscp using sftp protocol and upload the zip file. Ideally, I would get a text file that lets me know if the upload was successful or not.

I've been searching the internet and haven't found a solution as of yet.

Does anyone know how to do this?

[SamT]

Winscp: https://winscp.net/eng/index.php
WinSCP Scripting: https://winscp.net/eng/docs/scripting

[jochryem]

I don't see how this helps me solve my VBA issue. I want VBA to do the work...I don't see anywhere on those links that shows me how it would work.

[SamT]

It's out of my league, but if somebody else comes along to help, those will help them help you.

VBA can access and use other Script Languages, if one knows the other language.

[JKwan]

Right from the site that Sam pointed out with an example script. You should be able to call the script file (batch file) from VBA


# Connect
open sftp://user:password@example.com/ -hostkey="ssh-rsa 2048 xx:xx:xx:xx:xx:xx:xx:xx:xx:xx:xx:xx:xx:xx:xx:xx"
# Change remote directory
cd /home/user
# Force binary mode transfer
option transfer binary
# Download file to the local directory d:\
get examplefile.txt d:\
# Disconnect
close
# Connect as a different user
open sftp://user2:password@example.com/
# Change the remote directory
cd /home/user2
# Upload the file to current working directory
put d:\examplefile.txt
# Disconnect
close
# Exit WinSCP
exit


Here is an example:
https://winscp.net/eng/docs/script_upload_single_file

[snb]

The way I do it to upload the file G:\OF\lokaal_006.zip

Replace
- G:\OF
- username
- password
- filename lokaal_006.zip


Sub M_snb()
CreateObject("scripting.filesystemobject").CreateTextFile("G:\OF\snb_vba.ftp").write Replace("open ftp.snb-vba.eu|username|password|binary|cd domains/snb-vba.eu/public_html/bestanden|send G:\OF\lokaal_006.zip|quit", "|", vbCrLf)
Shell "ftp -v -i -s:G:\OF\snb_vba.ftp"
End Sub

[jochryem]

Thank you for this. I think my problem is more extensive though. I did review the area where you can upload a single file using a single command line but I need to somehow understand how you write a batch file that I can call in the script_upload_single_file or use a string to put everything together for me in the right format (with proper quotations etc) in order to use the single line command function (that would be ideal as my VBA code looks at multiple different file that get uploaded at different times throughout the process it runs.

I started to write the following and thought I could use a string where I call the PID but if I try to replace the PID commands with my string, it errors out and won't run.

Any thoughts on how I can make this work where I can change the filename within my VBA code whenever I need to and have the VBA code automatically upload it?

[snb]

Why do you ignore the working solutions ?

[jochryem]

I'm not a VBA expert and haven't found a solution that works in any of the threads that have been returned. Am I missing something?