none
How to download csv files from FTP server using VBA in excel RRS feed

  • Question

  • Hi,

    I have been going around in circles with this for a few days now and I cant figure what is wrong with my code. Everywhere I search on the internet says the code below should work.

    So I am trying to automate a manual process here where my colleague have to manually download files from this ftp server. I have a url that works which is used by my colleague. It contains username and password.

    My code is below, btw if there is any other way to do the same thing pls let me  know.

    I have to alter the passwords, username and the link as this from work and due to data protection rules.

    The URL that works is ftp://MY_USER_NAME:MY_PASSWORD@ftp.datacentre.com/country_forecast/CWG_eceps_19012206.csv

    CODE:

    Option Explicit
      
    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 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 InternetCloseHandle Lib "wininet" ( _
        ByVal hInet As Long) As Long
        

     
    Sub 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)
        Dim hOpen   As Long
        Dim hConn   As Long
       
     
     
        hOpen = InternetOpenA("FTPGET", 1, vbNullString, vbNullString, 1)
        hConn = InternetConnectA(hOpen, strHost, lngPort, strUser, strPass, 1, 0, 2)
       
     
        If FtpGetFileA(hConn, strRemoteFile, strLocalFile, 1, 0, FTP_TRANSFER_TYPE_UNKNOWN Or INTERNET_FLAG_RELOAD, 0) Then
            Debug.Print "Success"
        Else

            Debug.Print "Fail"
            
        End If

        'Close connections
        InternetCloseHandle hConn
        InternetCloseHandle hOpen
    End Sub

    Sub Get_File_From_FTP()
     
        Dim HostURL, fileSource, FileDestination As String
        HostURL = "ftp.datacentre.com"
        fileSource = "/country_forecast/CWG_eceps_19012206.csv"
        FileDestination = "C:\Development\Test1\newFile.txt"

    FtpDownload fileSource, FileDestination, HostURL, 21, "MY_USER_NAME", "MY_PASSWORD"

    End Sub

    Tuesday, February 12, 2019 11:35 AM

All replies

  • Hello,

    In .Net you already have component for operation with FTP:

    https://docs.microsoft.com/en-us/dotnet/framework/network-programming/ftp

    Examples show how to use it. 

    Do not forget, that on the network exists such thinks like net, subnet, firewall & etc...

    For example - different DNS server can point your request t different server and this server could be behind firewall.


    Sincerely, Highly skilled coding monkey.

    Tuesday, February 12, 2019 12:42 PM
  • I just put this together from some other stuff I had.  I downloaded a file from an Azure website. 

    Option Explicit
    Option Base 0
    Option Private Module
    
    Private Const MAX_PATH = 260
    Private Const openHTTP = 0
    Private Const openFTP = 1
    Private Const ERROR_SUCCESS As Long = 0
    Private Const MAX_DOMAIN_NAME_LEN As Long = 128
    Private Const MAX_HOSTNAME_LEN As Long = 128
    Private Const MAX_SCOPE_ID_LEN As Long = 256
    Private Const ASCII_TRANSFER = 1
    Private Const BINARY_TRANSFER = 2
    
    Public Type FILETIME
      dwLowDateTime As Long
      dwHighDateTime As Long
    End Type
    
    
    Public Type WIN32_FIND_DATA
      dwFileAttributes As Long
      ftCreationTime As FILETIME
      ftLastAccessTime As FILETIME
      ftLastWriteTime As FILETIME
      nFileSizeHigh As Long
      nFileSizeLow As Long
      dwReserved0 As Long
      dwReserved1 As Long
      cFileName As String * MAX_PATH
      cAlternate As String * 14
    End Type
    
    Public 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
    
    Public 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
    
    Public 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
    
    Public Declare Function InternetCloseHandle Lib "wininet.dll" (ByVal hInet As Long) As Integer
    
    Public 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
    
    Public Declare Function FTPDeleteFile Lib "wininet.dll" Alias "FtpDeleteFileA" _
                                          (ByVal hFtpSession As Long, ByVal lpszFileName As String) As Boolean
    
    Public Declare Function FtpFindFirstFile Lib "wininet.dll" Alias "FtpFindFirstFileA" _
                                             (ByVal hFtpSession As Long, ByVal lpszSearchFile As String, _
                                              lpFindFileData As WIN32_FIND_DATA, ByVal dwFlags As Long, _
                                              ByVal dwContent As Long) As Long
    
    Private Declare Function InternetOpenUrl Lib "wininet.dll" Alias _
                                             "InternetOpenUrlA" (ByVal hInternetSession As Long, ByVal lpszUrl As String, _
                                                                 ByVal lpszHeaders As String, ByVal dwHeadersLength As Long, _
                                                                 ByVal dwFlags As Long, ByVal dwContext As Long) As Long
    
    Private Declare Function InternetReadFile Lib "wininet.dll" (ByVal hFile As _
                                                                 Long, ByVal lpBuffer As String, ByVal dwNumberOfBytesToRead As Long, _
                                                                 lNumberOfBytesRead As Long) As Integer
                                                                 
    Sub OpenSession(controlName As String, openType As Integer, lngINET As Long)
      lngINET = InternetOpen(controlName, openType, vbNullString, vbNullString, 0)
    End Sub
    
    Sub OpenConnection(lngINET As Long, SITE As String, LOGINID As String, PASSWD As String, lngINETConn As Long)
      lngINETConn = InternetConnect(lngINET, SITE, 0, _
                                    LOGINID, PASSWD, 1, 0, 0)
    End Sub
    
    Sub OpenURL(lngINET As Long, url As String, hUrl As Long)
      Const INTERNET_FLAG_EXISTING_CONNECT = &H20000000
      hUrl = InternetOpenUrl(lngINET, url, vbNullString, 0, INTERNET_FLAG_EXISTING_CONNECT, 0)
    End Sub
    
    Function GetRemoteFile(httpLoc As String, fileName As String, statusStr As String) As Boolean
    
      Dim buffer As String
      Dim fName As String
      Dim sessionHandle As Long
      Dim connHandle As Long
      Dim connHandle2 As Long
      Dim numberOfBytesRead As Long
      Dim ok As Boolean
      Const SITE As String = "xxxxxxxxx.ftp.azurewebsites.windows.net"
      Const LOGINID As String = "xxxxxxxxxxxxxxxxxxxxxxxxx"
      Const PASSWD As String = "Rl3x34SwTTdsyx9xxxxxxxxxxxxxxxx"
      Const REMOTEJSONFILE As String = "site/wwwroot/data/tombstone.json"
    
      On Error GoTo ErrorHandler
    
      ' Delete file if it exists
      fName = Dir("C:\temp\" & fileName, vbDirectory)    ' Retrieve the first entry.
      If fName <> "" Then
        Call DeleteFile("C:\temp\" & fName)
      End If
    
      Application.StatusBar = statusStr
      Call OpenSession("MyHTTP", openHTTP, sessionHandle)
      If sessionHandle > 0 Then
        Call OpenConnection(sessionHandle, SITE, LOGINID, PASSWD, connHandle)
        If connHandle > 0 Then
          If FtpGetFile(connHandle, REMOTEJSONFILE, "C:\temp\" & fileName, 1, 0, BINARY_TRANSFER, 0&) Then
            GetRemoteFile = True
          End If
        End If
        InternetCloseHandle connHandle
      End If
      InternetCloseHandle sessionHandle
      Application.StatusBar = ""
      Exit Function
    
    ErrorHandler:
    
    End Function
    
    Sub DeleteFile(fName As String)
      On Error GoTo ErrorHandler
      Kill fName
      Exit Sub
    
    ErrorHandler:
      MsgBox "ERROR" & Err & ": " & Error(Err)
    End Sub
    
    Sub GetFile()
      If GetRemoteFile("site/wwwroot/data/", "tombstone.json", "") Then
        MsgBox ("GotFile")
      End If
    End Sub
    

    Tuesday, February 12, 2019 10:05 PM
  • Hi 

    Thanks for the code, im still having the same problem.

    Wednesday, February 13, 2019 11:05 AM
  • Hi 

    My problem is in the line below as the code opens a session ID but the connection returns 0.

    Unfortunately my user need this done in Excel. It would be a lot easier in .net I know.

    Many Thanks,

    K1985

    Wednesday, February 13, 2019 11:08 AM
  • As an experiment verify you can download file with WinSCP.  The FTP server may use SFTP or some other thing preventing connection.
    Wednesday, February 13, 2019 1:26 PM