Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 991
  • Last Modified:

FTP download in Excel VBA

I am trying to do something so simple (or it seems to me a .NET programmer) in VBA.  Not a VBA guy so please give me the answer like I am 10 years old lol

All I need to do is download a file in Excel in a macro to a local directory.

I have read countless Google searches and even on here many different posts but cannot get any of them to work...

This is for many users all over the globe so we cannot install anything or use a 3rd party tool.  We have to achieve this inside the macro.  Shell command, anything.  Just download a file.

Please help!  :)

Thanks experts
0
cyimxtck
Asked:
cyimxtck
  • 6
  • 5
  • 3
  • +1
1 Solution
 
cyimxtckAuthor Commented:
Sorry forgot to mention it is an FTP download.
0
 
rspahitzCommented:
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.
0
 
cyimxtckAuthor Commented:
All the users don't have an FTP app on their machines.  I wish it were that simple.
0
NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

 
rspahitzCommented:
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.
0
 
AlexPaceCommented:
Do they have FTP-enabled web browsers?
0
 
cyimxtckAuthor Commented:
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"
0
 
rspahitzCommented:
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.
0
 
AlexPaceCommented:
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.
0
 
AlexPaceCommented:
@rspahitz - good idea
0
 
cyimxtckAuthor Commented:
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.
0
 
AlexPaceCommented:
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.
0
 
AlexPaceCommented:
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.
0
 
cyimxtckAuthor Commented:
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.
0
 
[ fanpages ]IT Services ConsultantCommented:
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:
[ http://www.experts-exchange.com/Software/Office_Productivity/Office_Suites/MS_Office/Excel/Q_21277896.html#a13084065 ]

and
[ http://www.experts-exchange.com/Software/Office_Productivity/Office_Suites/MS_Office/Excel/Q_21277896.html#a13084137 ]
0
 
AlexPaceCommented:
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.
0
 
[ fanpages ]IT Services ConsultantCommented:
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 ]
0

Featured Post

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

  • 6
  • 5
  • 3
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now