Microsoft Access 2010 Automate FTP

Understand that there are different "flavors" of FTP:   FTP, sFTP, and FTPs.

Depending on which the web site needs determines the method/client that you use to talk to it.

 What you want to do is entirely possible and I have one client now where we use FTP extensively.

There are two approaches to FTP in general:

1. Script out a batch file in VBA, then execute that, which calls a command line client.

2. Do the FTP directly yourself with something like the winet lib.

I like #1 myself.  It means I don't need to know all the details of a specific flavor of FTP and more imprtantly to me, I can manually execute the commands to test, then automate, which makes development of automation jobs far easier.

The FTP Clients I use are:

1. Microsoft client built into windows for normal FTP
2. MoveIt Freely client for sFTP
3. WinSCP client for FTPS

All these clients are free.

In a little bit, I'll post code for FTP download for one of the clients so you can see how this might be setup.

 In the meantime, you need to:

1. Find out what type of FTP the web site uses.
2. Get the client

 I would simply try the built-in Microsoft client and see if it works.

Jim.

Right - the FTP methods are simple. It's the "OK" button I'm not sure of ...

JDettman, I am not comfortable posting the website here but looked at your bio I would like to send it to you but didn't see a way I could do that. Is there a way to send you a message. There is a link to the webmaster on the site should I ask him if so what should I ask him. I have never don't script before but have wrote my own code in Access in VBA and can do anything I want to in Access but just have done any scripting but willing to try.

What your looking for is the method of FTP transfer.  As I said, I would first simply try the download with the Microsoft FTP client.

First you need the address.   Go out to the web site, enter your username and password, click the button and look at the URL your being directed to.  Most often,
 it will be something like:

ftp://<address>

If that's the case, then also more often then not, you can go directly to the FTP server without using the web page.

You can then click, start, run, enter CMD and click OK.  You'll get a DOS command window.  Type in FTP and hit return.

Then type OPEN and the address you got above.  Should get prompted for your username and password.   At that point, assuming you get a login, you can use a DIR to see the files, then a GET to download a file.

These would be the commands the script would need.

So first job is to find out how to get at the file.

If you forward me the URL at jimdettman"at"online-computer-services.net, I'll tell you how you need to do it.

Jim.

Usually when someone talks about logging into a web site they are talking about HTML pages delivered over HTTP(S) so what is the FTP angle?

Web sites can transfer files by HTTP or FTP.  At the moment, it's hard to tell which he needs.   He mentions a login on a web page, and that probably means HTTP, but sometimes a web site will simply generate a unique URL based on the login which is nothing more then a FTP transfer (most browsers have a built-in FTP client).   Also the question title mentions FTP.

So at the moment, we don't really know which it is, and it could be either.

Jim.

Jim is correct, if it is an FTP anchor link in a web page then you can bypass the the HTTP server and go directly to the FTP server.  One problem you might run into is that web browsers and modern FTP clients tend to default to passive mode for FTP transfers but the command line ftp.exe client can only do active mode... you'll know if this is a problem if you can connect and authenticate with ftp.exe but it hangs trying to transfer files.  This issue can be resolved by modifying the firewall or by using a modern scriptable FTP client like Robo-FTP, etc.

it shows websitename.org/ftp. The title of the page shows Index of /ftp/ then it list all the files. Does that help or am I being to protective of the site?

I think a little too pretective if you need a user name and password to sign-on.   But if you really don't want to post it, that's fine.  Just send it to me off-line.

Jim.

OK, I got the url off-line and checked and it's not reachable by FTP, so it looks like this is a HTTP file download through the web site.   But marlind605 is going to double check with the folks hosting the site and see what's available.

Jim.

Thanks Jim and Alex. I will find out more in a few minutes.
Mark

Sorry it took me so long to get back to you. It looks like what I see is what I get no separate FTP sight is available. Looks like I can construct a link using www.mylink.com/AA131911.ZIP for yesterday. So can how can I set up Access to open that website at 1:00 (Task Scheduler maybe?) and I think it will let me save it by just getting that link to the browser. I am kind of lost right now.

Thanks thehitechcoach this didn't work for me but I did try this Dim DN
    Set DN = CreateObject("internetexplorer.application")
        DN.navigate "https://www.notrealweb.com/ftp/SQB_DSIM/CR131120.ZIP"
        DN.Visible = True
     I was able to get internet explorer to open but it wants me to click on yes or no to a potential security concern. It also wants me to click on save. After i log into to the site in Chrome and ask it to remember me I am able to just copy the url and paste it in the browser and it saves without me clicking okay password.
1.  How do I get rid of the "Potential Security Concern"
2.  Can I change my code to Chrome and get it to work.
Thanks for putting up with me on this question.

Curious, what did not work?

Using my code, will it  download the url:  

https://www.notrealweb.com/ftp/SQB_DSIM/CR131120.ZIP

example:

Call bimDownloadURLtoFile("https://www.notrealweb.com/ftp/SQB_DSIM/CR131120.ZIP", "C:\myfolder\CR131120.ZIP")

Select allOpen in new window

or

https://username:password@www.notrealweb.com/ftp/SQB_DSIM/CR131120.ZIP

replacing username:password with the correct username and password

Do you get an error message?

I've requested that this question be deleted for the following reason:

I had to give the project to someone else.

See below.

Lots of good info here...  Perhaps should keep for reference rather than delete.

Bill

Please don't delete this thread- it has useful code.

Ditto

Does anyone have anything they can test it with to see what works?

You might consider using:

AutoHotkey - Free Mouse and Keyboard Macro Program

AutoHotkey is a free, open-source utility for Windows. With it, you can:* Automate almost anything by sending keystrokes and mouse clicks. You can write a mouse or keyboard macro by hand or use the macro .