Solved

FTP download in Excel VBA

Posted on 2013-05-23
16
881 Views
Last Modified: 2013-05-23
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
Comment
Question by:cyimxtck
  • 6
  • 5
  • 3
  • +1
16 Comments
 

Author Comment

by:cyimxtck
ID: 39191192
Sorry forgot to mention it is an FTP download.
0
 
LVL 22

Expert Comment

by:rspahitz
ID: 39191311
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
 

Author Comment

by:cyimxtck
ID: 39191403
All the users don't have an FTP app on their machines.  I wish it were that simple.
0
 
LVL 22

Expert Comment

by:rspahitz
ID: 39191581
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
 
LVL 16

Expert Comment

by:AlexPace
ID: 39191592
Do they have FTP-enabled web browsers?
0
 

Author Comment

by:cyimxtck
ID: 39191622
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
 
LVL 22

Expert Comment

by:rspahitz
ID: 39191638
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
 
LVL 16

Expert Comment

by:AlexPace
ID: 39191654
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
Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

 
LVL 16

Expert Comment

by:AlexPace
ID: 39191663
@rspahitz - good idea
0
 

Author Comment

by:cyimxtck
ID: 39191667
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
 
LVL 16

Expert Comment

by:AlexPace
ID: 39191755
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
 
LVL 16

Accepted Solution

by:
AlexPace earned 500 total points
ID: 39191766
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
 

Author Comment

by:cyimxtck
ID: 39192357
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
 
LVL 35

Expert Comment

by:[ fanpages ]
ID: 39192528
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
 
LVL 16

Expert Comment

by:AlexPace
ID: 39192765
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
 
LVL 35

Expert Comment

by:[ fanpages ]
ID: 39193003
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

Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

Join & Write a Comment

A little background as to how I came to I design this code: Around 5 years ago I designed an add-in that formatted Excel files to a corporate standard, applying different cell colours and font type depending on whether the cells contained inputs,…
Recently Microsoft released a brand new function called CONCAT. It's supposed to replace its predecessor CONCATENATE. But how does it work? And what's new? In this article, we take a closer look at all of this - we even included an exercise file for…
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

743 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now