Using VBA Code to Transfer Files
You can see how easy it is to move files between the FTP server and your PC. Even if the
FTP server is on the other side of the world, the process is still very fast, depending on the
size of file being transferred.
However, you may require a VBA procedure to transfer files from an FTP server either at
a user’s request or at a set time, such as an overnight job.
You can use VBA to do this by using API calls. See Chapter 20 for more information on
what API calls are and what they can do for you.
Open a new module by clicking Insert | Module on the VBE toolbar. Enter the following
code into it:PrivateDeclareFunctionInternetOpenLib "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 LongPrivate 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 LongPrivate 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 BooleanPrivate 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 BooleanPrivate Declare Function InternetCloseHandle Lib "wininet.dll" _
(ByVal hInet As Long) As IntegerThese statements set up declarations in the General section of your module to use FTP
commands in the wininet.dll dynamic link library (which is supplied as part of Windows).338 Microsoft Access 2010 VBA Macro Programming