Okay,
I gave it a go myself using code similar to what Colo posted, with success. My steps were:
1) Created a text file to be uploaded called C:\Test\somefile.txt
2) Created a batch file call C:\Test\testftp.bat
3) ran a shell command from VBA
the batch file listed the ftp commands, and in short was just a simple text file like this:
open mysite.com
username
password
send C:\Test\somefile.txt
bye
the vba shell command to execute the batch file (and, BTW, launch the ftp program) was simply:
Code:
Sub foo()
Call Shell("FTP -i -s:C:\Test\testftp.bat")
End Sub
You may want to open up a dos prompt and type ftp to get to an ftp prompt - you should be able to execute the very same commands from your .bat file, verbatim. But you'll get some messages if anything goes wrong. Note that this is a good reminder that the vba will execute without any warnings or errors even if it fails - so it can be a little trying at first and also may not guarantee success. It will also execute asynchronously so your VBA will not "wait" for the transfer to finish, but will continue on executing your vba even if the FTP program is not finished with the transfer. We've got a very simple implementation here.
As a footnote, (it probably doesn't apply to you as you're probably already using FTP outside of VBA) -- some computers, firewalls, or group policies will block ftp traffic.
