Next, set up a subroutine to transfer a file from your chosen FTP server to your local PC:Sub GetFile()
Dim MyConn, MyINet, Chk As Boolean
Chk = False
MyINet = InternetOpen("MyFTP", 1, vbNullString, vbNullString, 0 )
If MyINet > 0 Then
MyConn = InternetConnect(MyINet, "MyOrg.MyServer.net", 21,
"MyUserID", "MyPassword", 1, 0, 0 )
If MyConn > 0 ThenChk = FTPGETFile(MyConn, "MyFolder/MyFileName.txt", _
CurrentProject.Path & "\MyFileName.txt", 0, 0, 1, 0 )
InternetCloseHandle MyConn
End If
InternetCloseHandle MyINet
End If
If (Chk) Then
MsgBox "File downloaded"
Else
MsgBox "FTP Error"
End If
End Sub
This code first of all creates variables MyConn, MyInet, and Chk. Notice that MyConn
and MyInet are set to the default type of Variant. Chk is to hold a value to show that there has
been success in transferring the file. It is set to False initially to show a fail.
TheInternetOpenAPI is then used to open the Internet (similar to the Internet browser
window being initially opened). This returns a value into the variable MyINet on success.
If a handle has been returned (value is not 0), then the API callInternetConnectis used
to establish a connection to the FTP server using the user ID and password required. If no
user ID and password are required, then use empty quotes (“”). Notice that the name of the
FTP server does not include ftp://. The value of 21 is the default port for FTP.
This then returns another handle. If it is non-zero, then the API callFTPGEtFileis used
to transfer the file. The parameter MyFolder/MyFileName.txt is the path and name of the file
on the FTP server and the parameter CurrentProject.Path & "\MyFileName.txt" is the local
path and name of the file. CurrentProject.Path picks up the path of the Access database and
ensures that the file is downloaded to the same path.
Chapter 36: Use FTP in VBA 339