Solved

Microsoft Access 2010 Automate FTP

Posted on 2013-11-14
26
844 Views
Last Modified: 2014-01-01
I need to be able to download from a website in the middle of the night, I will use task scheduler for this but I would like to be able to store the user name and password in the database and have it open the website and download the file. I have found some code in access but just haven't been able to get it to work Old program?? not sure. Can anyone help me with code for this using MS Access as much as possible or a step by step bat file that can be set to run in the middle of the night like from Access maybe?? The website requires a user name and password and requires you to select ok. However, It does allow you to save the password but not sure if that is the best solution. Thanks for any thoughts or code.
0
Comment
Question by:marlind605
  • 7
  • 6
  • 4
  • +4
26 Comments
 
LVL 84

Accepted Solution

by:
Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 167 total points
ID: 39650910
The website requires a user name and password and requires you to select ok.
I don't think you can select the "OK" button via code. Do you have the URL of the website?

Have you contacted the website to see if they have alternate methods to manage this? Often a website that is designed for these sorts of things will provide you with a webservice you can connect with and download, without human intervention.
0
 
LVL 57
ID: 39650916
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.
0
 
LVL 57

Assisted Solution

by:Jim Dettman (Microsoft MVP/ EE MVE)
Jim Dettman (Microsoft MVP/ EE MVE) earned 167 total points
ID: 39650924
Here's a sample of a FTP download that is scripted.  This code won't run for you directly, but will give you a good idea on how this is done with a script (batch) file.

Jim.

Function FTPDownloadFile(strLocalFileName As String, strFTPFilename As String, strFTPSiteName As String, strUserName As String, strPassword As String, Optional strTransferType As String) As Boolean

          ' Procedure to download a file from a FTP site.
          ' Sends e-mail to ITALERT if download fails and returns false.
          Const RoutineName = "FTPDownloadFile"
          Const Version = "1.4"

          Dim strFTPCommandFile As String
          Dim strFTPScriptFile As String
          Dim strFTPLogFile As String

          Dim lngHWnd As Long
          Dim intFileNum As Integer
          Dim strMailMessage As String
          Dim oOCS_SendMail As New OCS_SendMail

10        On Error GoTo FTPDownloadFile_Error

          ' Generate file names
20        strFTPScriptFile = "\FTP_" & AppShortName() & "_" & RoutineName & ".txt"
30        strFTPCommandFile = "\FTP_" & AppShortName() & "_" & RoutineName & ".bat"
40        strFTPLogFile = "\FTP_" & AppShortName() & "_" & RoutineName & ".log"

          ' Write script file
50        intFileNum = FreeFile
60        Open strFTPScriptFile For Output As #intFileNum
70        Print #intFileNum, strUserName
80        Print #intFileNum, strPassword
90        Print #intFileNum, "Type " & IIf(strTransferType = "B", "binary", "ascii")
100       Print #intFileNum, "Get " & Chr$(34) & strFTPFilename & Chr$(34) & " " & Chr$(34) & strLocalFileName & Chr$(34)
110       Print #intFileNum, "quit"
120       Close #intFileNum

          ' Write command file
130       intFileNum = FreeFile
140       Open strFTPCommandFile For Output As #intFileNum
150       Print #intFileNum, "@ftp -i -s:" & strFTPScriptFile & " " & strFTPSiteName & " > " & strFTPLogFile
160       Close #intFileNum

          ' Execute
170       lngHWnd = Shell(strFTPCommandFile, vbHide)
180       WaitWhileRunning (lngHWnd)

          ' Check log file
190       If IsValidFTP(strFTPLogFile) Then
200           FTPDownloadFile = True
210       Else
220           If DebugMode() = True Then
230               Stop
240               FTPDownloadFile = False
250           Else
260               oOCS_SendMail.SetParams "ITALERT", ".", "."
270               oOCS_SendMail.Subject = "FTP download failed"
280               strMailMessage = "The file: " & strFTPFilename & " did not download." & vbCrLf
290               strMailMessage = strMailMessage & "Command, script, and log files are attached." & vbCrLf & vbCrLf
300               strMailMessage = strMailMessage & "App name:" & AppShortName() & " Version: " & AppVersion()
310               oOCS_SendMail.Message = strMailMessage
320               oOCS_SendMail.Attachment = strFTPCommandFile & ";" & strFTPScriptFile & ";" & strFTPLogFile
330               oOCS_SendMail.Send
340               FTPDownloadFile = False
350           End If
360       End If

FTPDownloadFile_Exit:
370       On Error Resume Next

380       If Dir(strFTPCommandFile) <> "" Then Kill strFTPCommandFile
390       If Dir(strFTPScriptFile) <> "" Then Kill strFTPScriptFile
400       If Dir(strFTPLogFile) <> "" Then Kill strFTPLogFile

410       Close #intFileNum

420       Exit Function

FTPDownloadFile_Error:
430       UnexpectedError ModuleName, RoutineName, Version, Err.Number, Err.Description, Err.Source, VBA.Erl
440       FTPDownloadFile = False
450       Resume FTPDownloadFile_Exit

End Function
0
 
LVL 84
ID: 39650927
Right - the FTP methods are simple. It's the "OK" button I'm not sure of ...
0
 

Author Comment

by:marlind605
ID: 39651154
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.
0
 
LVL 57
ID: 39651214
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.
0
 
LVL 16

Expert Comment

by:AlexPace
ID: 39651226
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?
0
 
LVL 57
ID: 39651260
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.
0
 
LVL 16

Expert Comment

by:AlexPace
ID: 39651263
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.
0
 

Author Comment

by:marlind605
ID: 39651337
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?
0
 
LVL 57
ID: 39651380
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.
0
 
LVL 57
ID: 39651422
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.
0
Complete Microsoft Windows PC® & Mac Backup

Backup and recovery solutions to protect all your PCs & Mac– on-premises or in remote locations. Acronis backs up entire PC or Mac with patented reliable disk imaging technology and you will be able to restore workstations to a new, dissimilar hardware in minutes.

 

Author Comment

by:marlind605
ID: 39651434
Thanks Jim and Alex. I will find out more in a few minutes.
Mark
0
 

Author Comment

by:marlind605
ID: 39664584
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.
0
 
LVL 21

Assisted Solution

by:Boyd (HiTechCoach) Trimmell, Microsoft Access MVP
Boyd (HiTechCoach) Trimmell, Microsoft Access MVP earned 166 total points
ID: 39664692
If yu now the direct path the the file you can download it using this:

From Download a file from a URL

'--- place this code in a standard module ---'
Option Compare Database
Option Explicit
Private Declare Function URLDownloadToFile Lib "urlmon" _
     Alias "URLDownloadToFileA" (ByVal pCaller As Long, _
     ByVal szURL As String, ByVal szFileName As String, _
     ByVal dwReserved As Long, ByVal lpfnCB As Long) As Long
 
Public Sub bimDownloadURLtoFile(pURL As String, _
     pFullFilePath As String)
 
   Call URLDownloadToFile(0, pURL, pFullFilePath, 0, 0)
 
End Sub
 
''' end code ''' 

Open in new window



It may be possible to pass the username and password on the URL like this:

http://username:password@hostname/pathtofile
0
 

Author Comment

by:marlind605
ID: 39667827
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.
0
 
LVL 21
ID: 39670508
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")

Open 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?
0
 

Author Comment

by:marlind605
ID: 39697136
I've requested that this question be deleted for the following reason:

I had to give the project to someone else.
0
 
LVL 14

Expert Comment

by:Bill Ross
ID: 39697133
See below.
0
 
LVL 14

Expert Comment

by:Bill Ross
ID: 39697137
Lots of good info here...  Perhaps should keep for reference rather than delete.

Bill
0
 
LVL 12

Expert Comment

by:pdebaets
ID: 39697152
Please don't delete this thread- it has useful code.
0
 
LVL 21
ID: 39697477
Ditto
0
 

Author Comment

by:marlind605
ID: 39698036
Does anyone have anything they can test it with to see what works?
0
 
LVL 21
ID: 39734175
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 .
0

Featured Post

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Join & Write a Comment

Suggested Solutions

When you are entering numbers in a speadsheet, and don't remember what 6×7 is, you just type “=6*7" instead. It works in every cell! This is not so in Access. To enter the elusive 42 in a text box, you have to find a calculator, and then copy the re…
Regardless of which version on MS Access you are using, one of the harder data-entry forms to create is one where most data from previous entries needs to be appended to new records, especially when there are numerous fields and records involved.  W…
Familiarize people with the process of utilizing SQL Server stored procedures from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Micr…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

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

15 Experts available now in Live!

Get 1:1 Help Now