Solved

FTP download in Excel VBA

Posted on 2013-05-23
16
895 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
Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

 
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
 
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

VMware Disaster Recovery and Data Protection

In this expert guide, you’ll learn about the components of a Modern Data Center. You will use cases for the value-added capabilities of Veeam®, including combining backup and replication for VMware disaster recovery and using replication for data center migration.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
Freeze panes is an option within all variants of Excel to enable parts of a sheet to remain stationary when the cursor is in another part of the sheet. This is a very useful feature which is overlooked or under used.
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.

770 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