The technique is rather simple. To use my technique you'll need:
1) A bat file to run the FTP
2) A text file that contains your FTP commands
3) A call in a VBA module to run the bat file.
The bat file is pretty simple. Do something like:
REM Set up for FTP transfers
cd \_the path where you want your files to arrive_
Fetch.txt (in the last line above) contains the commands you normally manually type if you use command line FTP. E.g.
open _your ftp site_
_your user id_
cd _path to folder containg file to download_
mget _file name_
In your module code:
Shell "c:\_path to bat file_\Fetch.bat", vbNormalFocus
MsgBox "When the FTP has completed, click OK to continue.", vbOKOnly, "Wait for Download"
The "Shell" will open another window and "attempts to set focus" to that window. The thing is, it won't "lock" the focus. The user can click the Ok on the prompt and your code will start processing as if the FTP is already complete. Your users may need some training to understand what the Msgbox prompt means.
Keep in mind, if you use this technique the "Fetch.txt" file is going to contain a record of your password that anyone will be able to see if they know where to look. It's not really great for security in a multi-user environment.
If your users specify their own id and password, you could build the Fetch.txt file just before "Shelling".
Randall J Weers
Membership Vice President
Pacific NorthWest Access Developers Group