Thread: FTP Download - Needs Help
2013-12-01, 22:13 #1
- Join Date
- Oct 2013
- Thanked 0 Times in 0 Posts
FTP Download - Needs Help
I found this VBA script online and i use to download the files from FTP Server, .img & .xml files from an ftp server. It works fine, successfully.
However, there are some issues I would need your help in resolving them.
1. The files are updated at night between 8:00 to 12:00 p.m., and delivers a end of session flag file, I want this downloading script to commence the downloading of files only after the end of session file is found in the FTP Server directory, till that time the script to hold on.
The close of session file description : 3_29112013.eos [3_ always be constant /current date[ddmmyyyy]/.eos ]
2. I want to download the lastest files which have current date in file name. File names : PRD_xxx_current date[ddmmyyyy]_01.img
3. After successful downloading of files at local disk folder create a report file in text format and email to admin as attachment.
Here is the code I have been using manually downloading of files which does not support wildcards format.
Can you please, please help?
Option Explicit ' Open the Internet object Private Declare Function InternetOpen Lib "wininet.dll" Alias "InternetOpenA" _ (ByVal sAgent As String, ByVal lAccessType As Long, ByVal sProxyName As String, _ ByVal sProxyBypass As String, ByVal lFlags As Long) As Long ' Connect to the network Private Declare Function InternetConnect Lib "wininet.dll" Alias "InternetConnectA" _ (ByVal hInternetSession As Long, ByVal sServerName As String, _ ByVal nServerPort As Integer, ByVal sUsername As String, _ ByVal sPassword As String, ByVal lService As Long, _ ByVal lFlags As Long, ByVal lContext As Long) As Long ' Get a file using FTP Private Declare Function FtpGetFile Lib "wininet.dll" Alias "FtpGetFileA" _ (ByVal hFtpSession As Long, ByVal lpszRemoteFile As String, _ ByVal lpszNewFile As String, ByVal fFailIfExists As Boolean, _ ByVal dwFlagsAndAttributes As Long, ByVal dwFlags As Long, _ ByVal dwContext As Long) As Boolean ' Send a file using FTP Private Declare Function FtpPutFile Lib "wininet.dll" Alias "FtpPutFileA" _ (ByVal hFtpSession As Long, ByVal lpszLocalFile As String, _ ByVal lpszRemoteFile As String, ByVal dwFlags As Long, _ ByVal dwContext As Long) As Boolean ' Close the Internet object Private Declare Function InternetCloseHandle Lib "wininet.dll" _ (ByVal hInet As Long) As Integer Sub GetIMG_XML() ' I've commented out the uninteresting MsgBox lines; they show long (and meaningless) numbers. Dim AgentStr As String Dim AccessTypeLong As Long Dim ProxyNameStr As String Dim ProxyBypassStr As String Dim FlagsLong As Long Dim InternetSessionLong As Long Dim ServerNameStr As String Dim ServerPortInt As Integer Dim UserNameStr As String Dim PasswordStr As String Dim ServiceLong As Long Dim ContextLong As Long Dim FTPSessionLong As Long Dim RemoteFileStr As String Dim NewFileStr As String Dim FailIfExistsBool As Boolean Dim FlagsAndAttributesLong As Long Dim SomeThingLong As Long Dim MyInternetHandleLong As Long Dim MyFTPHandleLong As Long Dim SomeInteger As Integer Dim FTPSuccessBool As Boolean ' Did the FTP download work? ' ** Call INTERNET OPEN first ** AgentStr = "FTP Download" ' can be whatever AccessTypeLong = 0 ' zero appears to work fine ProxyNameStr = "" ' nul works fine here ProxyBypassStr = "" ' nul works fine here FlagsLong = 0 ' zero appears to work fine MyInternetHandleLong = InternetOpen(AgentStr, AccessTypeLong, ProxyNameStr, ProxyBypassStr, FlagsLong) ' MsgBox MyInternetHandleLong ' ** Call Internet CONNECT next ** ' The file I want to get is at ftp://xxx.xxx.xxx.xxx in directory /database having .img & .xml files with current date. 'MyInternetHandleLong is obtained above ServerNameStr = "xxx.xxx.xxx.xxx" ' address of the FTP server, WITHOUT the "ftp://" part ServerPortInt = 21 ' default FTP port UserNameStr = "zmagic" PasswordStr = "password8" ' nul is the default ServiceLong = 1 ' this for the FTP service (2 = gopher, 3 = http) FlagsLong = 0 ' 0 appears to work fine here ContextLong = 0 ' 0 appears to work fine here MyFTPHandleLong = InternetConnect(MyInternetHandleLong, ServerNameStr, ServerPortInt, UserNameStr, PasswordStr, ServiceLong, FlagsLong, ContextLong) ' MsgBox "My FTP handle = " & MyFTPHandleLong ' (this is NOT the same value as MyInternetHandle, above) ' ** Call FTP Get File next ** ' MyFTPHandleLong is obtained above RemoteFileStr = "/database/*.*" ' file name on server, including directories NewFileStr = "D:\FTP\" ' file name on MY system FailIfExistsBool = False ' should NOT fail if file already exists on MY computer.... HOWEVER, ' if the file does exist, the FTP DOES fail. Don't know about this. Short answer: ' the target file should NOT exist on my computer before calling this routine! FlagsAndAttributesLong = 128 ' Normal file, no special flags set. FlagsLong = 2 ' FTP Transfer Type Binary (the default) ContextLong = 0 ' apparently not required. FTPSuccessBool = FtpGetFile(MyFTPHandleLong, RemoteFileStr, NewFileStr, FailIfExistsBool, FlagsAndAttributesLong, FlagsLong, ContextLong) MsgBox "FTP Success = " & FTPSuccessBool ' ** Finally, close the connection ** SomeInteger = InternetCloseHandle(MyInternetHandleLong) ' MsgBox SomeInteger ' Seems to return "1" End Sub
Get our unique weekly Newsletter with tips and techniques, how to's and critical updates on Windows 7, Windows 8, Windows XP, Firefox, Internet Explorer, Google, etc. Join our 480,000 subscribers!
+ Get this BONUS — free!
Get the most of Excel! Learn about new features, basics of creating a new spreadsheet and using the infamous Ribbon in the first chapter of Excel 2013: The Missing Manual - Subscribe and download Chapter 1 for free!