VBA FTP

Again, from AnalystCave blog, I’ve found an interesting post about how to perform download/upload via FTP protocol in VBA. We should rely on some API functions. The code presented here is 90% the same from the original post, but tidied a bit so it can be reused on a UserForm with ease. There’s only the need for 4 textboxes (userName, Password, Port, SeverHost IP address), and 2 more textboxes (linked one each to 2 listboxes/treebox) to deal with the archives both in the local drive and in the server. And buttons to Upload/Download and Cancel… and you have a clean interface to do FTP under VBA. Following are the common API functions and constants needed:

Private Const FTP_TRANSFER_TYPE_UNKNOWN As Long = 0
Private Const INTERNET_FLAG_RELOAD As Long = &H80000000

Private Declare Function InternetOpenA Lib "wininet.dll" (ByVal sAgent As String, ByVal lAccessType As Long, ByVal sProxyName As String, ByVal sProxyBypass As String, ByVal lFlags As Long) As Long
Private Declare Function InternetConnectA Lib "wininet.dll" (ByVal hInternetSession As Long, ByVal sServerName As String, ByVal nServerPort As Long, ByVal sUsername As String, ByVal sPassword As String, ByVal lService As Long, ByVal lFlags As Long, ByVal lcontext As Long) As Long
Private Declare Function InternetCloseHandle Lib "wininet" (ByVal hInet As Long) As Long
Private Declare Function FtpGetFileA Lib "wininet.dll" (ByVal hConnect As Long, ByVal lpszRemoteFile As String, ByVal lpszNewFile As String, ByVal fFailIfExists As Long, ByVal dwFlagsAndAttributes As Long, ByVal dwFlags As Long, ByVal dwContext As Long) As Long
Private Declare Function FtpPutFileA Lib "wininet.dll" (ByVal hFtpSession As Long, ByVal lpszLocalFile As String, ByVal lpszRemoteFile As String, ByVal dwFlags As Long, ByVal dwContext As Long) As Boolean

VBA FTP Download Files

To download files via FTP protocol in VBA, use this code:

Private Function FtpDownload(ByVal strRemoteFile As String, _
                             ByVal strLocalFile As String, _
                             ByVal strHost As String, _
                             ByVal lngPort As Long, _
                             ByVal strUser As String, _
                             ByVal strPass As String) As Boolean
' to download file from server:
    
    Dim hOpen As Long
    Dim hConn As Long
    Dim lgRetVal As Long

    hOpen = InternetOpenA("FTPGET", 1, vbNullString, vbNullString, 1)
    hConn = InternetConnectA(hOpen, strHost, lngPort, strUser, strPass, 1, 0, 2)
    
    If Not FtpGetFileA(hConn, _
                       strRemoteFile, _
                       strLocalFile, _
                       1, _
                       0, _
                       FTP_TRANSFER_TYPE_UNKNOWN Or INTERNET_FLAG_RELOAD, _
                       0) Then
        lgRetVal = VBA.MsgBox("Error while downloading file [" & strRemoteFile & "] from server", _
                              vbCritical, "W A R N I N G")
    End If

     'Close connections
    InternetCloseHandle hConn
    InternetCloseHandle hOpen
End Function

strRemoteFile  A string path to the file on the remote FTP drive which you want to download e.g. “//home/user/text file.txt” strLocalFile  A string path to the file on the local drive which you want to save the remote file to e.g. “C:\text file.txt” strHost  A string with the FTP server name e.g. “192.168.0.100” or “myserver.domain.com”. lngPort  A number specifying the FTP port. 21 by default. strUser  A string with the FTP user name. strPass A string with the FTP user password.

Example

Now let us use the above VBA FTP Download procedure to download a file from our FTP server.

Public Sub TestDownload()
    FtpDownload strRemoteFile:="//Download/file.txt", _
                strLocalFile:="C:\text file.txt", _
                strHost:="192.168.0.100", _
                lngPort:=21, _
                strUser:="username", _
                strPass:="password"
End Sub

To upload files from your local drive via FTP protocol in VBA, use this code:

Private Function FtpUpload(ByVal strLocalFile As String, _
                           ByVal strRemoteFile As String, _
                           ByVal strHost As String, _
                           ByVal lngPort As Long, _
                           ByVal strUser As String, _
                           ByVal strPass As String) As Boolean
' to upload file to server:
    
    Dim hOpen As Long
    Dim hConn As Long
    Dim lgRetVal As Long

    hOpen = InternetOpenA("FTPGET", 1, vbNullString, vbNullString, 1)
    hConn = InternetConnectA(hOpen, strHost, lngPort, strUser, strPass, 1, 0, 2)

    If Not FtpPutFileA(hConn, _
                       strLocalFile, _
                       strRemoteFile, _
                       FTP_TRANSFER_TYPE_UNKNOWN Or INTERNET_FLAG_RELOAD, _
                       0) Then
        lgRetVal = VBA.MsgBox("Error while uploading file [" & strRemoteFile & "] to server", _
                              vbCritical, "W A R N I N G")
    End If

     'Close connections
    InternetCloseHandle hConn
    InternetCloseHandle hOpen
End Function

Syntax

strLocalFile  A string path to the file on the local drive which you want to upload e.g. “C:\text file.txt” strRemoteFile  A string path with the name of the upload file on the remote drive to e.g. “//home/user/text file.txt” strHost  A string with the FTP server name e.g. “192.168.0.100” or “myserver.domain.com”. lngPort  A number specifying the FTP port. 21 by default. strUser  A string with the FTP user name. strPass A string with the FTP user password.

Example

Public Sub TestUpload()
    FtpUpload strLocalFile:=VBA.Environ("UserProfile") & "\Documents\" & "file.txt", _
              strRemoteFile:="//Download/file.txt", _
              strHost:="192.168.0.100", _
              lngPort:=21, _
              strUser:="username", _
              strPass:="password"
End Sub