FTP download in Excel VBA

Sorry forgot to mention it is an FTP download.

First, I have to assume that you have an FTP program on your computer, which most do.
You can check by going to a command prompt (Start / Run / Cmd) and type ftp in the black window (are you familiar with the DOS window?)
If you get a prompt of "ftp>" then your good.  

For info about ftp, you can then type "ftp /?"

FYI: Type "quit" to exit ftp and "exit" to exit DOS.

What you need VBA to do is to run this ftp command, possibly using a batch file, by calling it through the VBA Shell command, something like this:

...
Shell "cmd /k ftp",vbNormalFocus
...

The cmd will launch the DOS window, /k will keep it open after it completes (/c to close after it completes), ftp is the DOS command to run, and vbNormalFocus will show the window (good for testing, then maybe vbHide.

Once you ahve this working, it's a matter of using the ftp command to do all the work.

What I've done in other projects is to use VBA to create a batch file then have the above command adjusted to run the batch file like this:

Shell "cmd /k ftp s:"& strBatchFileName

The batch file will look something like this:

Open http://mydownloadsite.com
Get somefile.txt
Close

For details about that, go into the FTP subsystem and enter ? or ? open, and maybe check MSDN for details on how to use this.

All the users don't have an FTP app on their machines.  I wish it were that simple.

I think it's free with Windows so you could probably install it.  Other than that, you'll need to somehow get it to them. It's like wanting everyone to run a Window program but some people only have Macs...you need to get them the components they need to do their job.  In lieu of that, I suppose your could re-invent the wheel and create your own drivers to perform the FTP task, but that's beyond my expertise.

Do they have FTP-enabled web browsers?

I am not sure if they do but everyone runs IE corporate wide.  That may be an easy sell to enable FTP on the browser.  But we do need this to run in Excel in a macro.

As my boss puts it "I want to hit a go button and everything is done"

If these all have access to a shared server, you may be able to put FTP on there and just supply a path.  This should be very do-able.

FTP has some advantages but if there are infrastructure issues then by using it you could be taking on support costs that your boss is not yet considering.  Since it is probably much safer to assume pre-existing support for HTTP and you are only talking about a single file you should strongly consider using a web server to distribute the file unless you have security requirement.

@rspahitz - good idea

I don't have those options.

We have an FTP server we do not own which folks will have to retrieve a file from their desktop.  It gets updated and then pulled into Excel for further processing.

An FTP server doesn't do you any good if your users don't have FTP client software.

Sounds like your boss gave you a screwdriver and a box of nails to work with on this project.

Could you use your .NET experience to write a .NET COM library so the FTP download function is exposed to the VBA via COM?

You would need to write it to target .NET 2.0 or however low a .NET version supports the FTPWebRequest library.  The lower version the better or else you just trade one software distrubution problem with another as you have to explain to people how to install the framework.

Actually I wrote a file output in Excel that uses FTP and get the file download....ARGH  Nothing great but it works and gets the job done in the requirements.

I suppose you could try calling socket functions on the Win32 API to write your own FTP client within an Excel macro but that would just be crazy talk.

...but if you search the Previously Asked Questions you will find a few examples of such an undertaking.

From 2005:
[ https://www.experts-exchange.com/questions/21277896/FTP-Through-Excel.html?anchorAnswerId=13084065#a13084065 ]

and
[ https://www.experts-exchange.com/questions/21277896/FTP-Through-Excel.html?anchorAnswerId=13084137#a13084137 ]

Those old threads are not implementing an FTP client via sockets, they are calling the dll that Internet Explorer sits on... I wouldn't count on that working on machines where IE is not available.

I wouldn't count on that working on machines where IE is not available.

I suggest you try it, then.

Another example using "wininet.dll":
[ http://vbnet.mvps.org/index.html?code/internet/ftpdownloadcallback.htm ]