FtpFindFirstFile always returns zero

Question

I've hit a brick wall trying to get FTP working in Excel VBA (64-bit Office on 64-bit Windows 10). As an early proof of concept, I'm just trying to list the name of the single text file that I've uploaded to the FTP server.

The sub I'm running is ListFilesOnFTP. hOpen and hConnection both get set to handle values successfully by InternetOpen and InternetConnect respecitvely.

blReturn is set to True by FtpSetCurrentDirectory, indicating that this is not failing.

The problem I have is in EnumFiles - no matter what combination of wildcards I use for lpszSearchfile, FtpFindFirstFile always returns zero, and therefore EnumFiles exits immediately.

Obviously I have provided placeholder values below for strFTPServerIP, strUsername, strPassword and strRemoteDirectory, but I am 100% certain that the IP address and credentials are correct, and that the directory with the provided name does exist under the root of the FTP server.

Any ideas where I'm going wrong here?

Relevant constant and type declarations:

Private Const MAX_PATH As Integer = 260
Private Const INTERNET_FLAG_RELOAD = &H80000000
Private Const INTERNET_FLAG_NO_CACHE_WRITE = &H4000000
Private Const INTERNET_OPEN_TYPE_PRECONFIG = 0
Private Const INTERNET_DEFAULT_FTP_PORT = 21
Private Const INTERNET_SERVICE_FTP = 1
Private Const INTERNET_FLAG_PASSIVE = &H8000000
Private Const INTERNET_NO_CALLBACK = 0

Private Type FILETIME

    dwLowDateTime As Long
    dwHighDateTime As Long
    
End Type

Private 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

Relevant wininet.dll function declarations (please note - I do have these wrapped in the usual #If VBA7 Then... #Else... #End If conditional compilation structures, with 32-bit compatible declarations in the else clause, but for brevity I have only provided the PtrSafe functions here):

    Private Declare PtrSafe Function InternetCloseHandle Lib "wininet.dll" ( _
        ByVal hInet As LongPtr) As LongPtr

    Private Declare PtrSafe Function InternetOpen Lib "wininet.dll" Alias "InternetOpenA" ( _
        ByVal sAgent As String, _
        ByVal lAccessType As LongPtr, _
        ByVal sProxyName As String, _
        ByVal sProxyBypass As String, _
        ByVal lFlags As LongPtr) As LongPtr

    Private Declare PtrSafe Function InternetConnect Lib "wininet.dll" Alias "InternetConnectA" ( _
        ByVal hInternetSession As LongPtr, _
        ByVal sServerName As String, _
        ByVal nServerPort As LongPtr, _
        ByVal sUsername As String, _
        ByVal sPassword As String, _
        ByVal lService As LongPtr, _
        ByVal lFlags As LongPtr, _
        ByVal lContext As LongPtr) As LongPtr

    Private Declare PtrSafe Function FtpSetCurrentDirectory Lib "wininet.dll" Alias "FtpSetCurrentDirectoryA" ( _
        ByVal hFtpSession As LongPtr, _
        ByVal lpszDirectory As String) As Boolean

    Private Declare PtrSafe Function FtpGetCurrentDirectory Lib "wininet.dll" Alias "FtpGetCurrentDirectoryA" ( _
        ByVal hFtpSession As LongPtr, _
        ByVal lpszCurrentDirectory As String, _
        ByVal lpdwCurrentDirectory As LongPtr) As LongPtr

    Private Declare PtrSafe Function FtpFindFirstFile Lib "wininet.dll" Alias "FtpFindFirstFileA" ( _
        ByVal hFtpSession As LongPtr, _
        ByVal lpszSearchFile As String, _
        ByRef lpFindFileData As WIN32_FIND_DATA, _
        ByVal dwFlags As LongPtr, _
        ByVal dwContent As LongPtr) As LongPtr

Procedures using the above:

Public Sub EnumFiles(ByVal hConnection As LongPtr)

    Dim pData As WIN32_FIND_DATA
    
    #If VBA7 Then
        Dim hFind As LongPtr, lRet As LongPtr
    #Else
        Dim hFind As Long, lRet As Long
    #End If

    ' Create a buffer
    pData.cFileName = String(MAX_PATH, vbNullChar)

    ' Find the first file
    hFind = FtpFindFirstFile(hConnection, "*.*", pData, INTERNET_FLAG_RELOAD Or INTERNET_FLAG_NO_CACHE_WRITE, 0)
    
    ' If there's no file, then exit sub
    If hFind = 0 Then Exit Sub

    ' Show the filename
    MsgBox Left$(pData.cFileName, InStr(1, pData.cFileName, String(1, 0), vbBinaryCompare) - 1)

    Do
    
        ' Create a buffer
        pData.cFileName = String(MAX_PATH, vbNullChar)
        
        ' Find the next file
        lRet = InternetFindNextFile(hFind, pData)
        
        ' If there's no next file, exit loop
        If lRet = 0 Then Exit Do
        
        ' Show the filename
        MsgBox Left$(pData.cFileName, InStr(1, pData.cFileName, String(1, 0), vbBinaryCompare) - 1)
    
    Loop

    ' Close the search handle
    InternetCloseHandle hFind

End Sub

Public Sub ListFilesOnFTP()

    #If VBA7 Then
        Dim hOpen As LongPtr, hConnection As LongPtr
    #Else
        Dim hOpen As Long, hConnection As Long
    #End If
    
    Dim blReturn As Boolean
    Dim strFTPServerIP As String, strUsername As String, strPassword As String, _
        strRemoteDirectory As String
    
    strFTPServerIP = "12.345.678.901"
    strUsername = "username"
    strPassword = "password"
    strRemoteDirectory = "directory_name/"
    
    ' Open an internet connection
    hOpen = InternetOpen("FTP", _
        INTERNET_OPEN_TYPE_PRECONFIG, _
        vbNullString, _
        vbNullString, _
        0)
    
    hConnection = InternetConnect( _
        hOpen, _
        strFTPServerIP, _
        INTERNET_DEFAULT_FTP_PORT, _
        strUsername, _
        strPassword, _
        INTERNET_SERVICE_FTP, _
        INTERNET_FLAG_PASSIVE, _
        INTERNET_NO_CALLBACK)
    
    blReturn = FtpSetCurrentDirectory(hConnection, strRemoteDirectory)
    
    Call EnumFiles(hConnection)
    
    InternetCloseHandle hConnection
    InternetCloseHandle hOpen

End Sub

Cross-posted here: excelforum.com

And here: mrexcel.com

Answer 1

So I gave up on this idea a couple of weeks ago, got rid of the FTP server I'd created, and had resolved to rethinking my approach to the problem I was trying to solve with FTP.

But... although my original FTP server is now long gone, I just made some changes to the API declarations after some advice from elsewhere about the datatypes of arguments to API calls not necessarily having to be LongPtrs (depends if the API function expects a Long), and then went looking for a test FTP server - found one here: https://dlptest.com/ftp-test/

Connected to it in File Explorer, used netstat -abno in command prompt to find the 'foreign address' with port 21 at the end of it, and used that IP in my code, along with the credentials listed on the webpage above.

And then boom...

immediate window output

actual directory content

As you can see, FtpGetCurrentDirectory doesn't like one of the parameters I'm passing it (a quick Google suggests that's what LastDllError code 87 means), but FtpSetCurrentDirectory obviously did run OK (the directory was set successfully)... so the hFtpSession argument being a LongPtr can't be a huge problem (or it is a problem for FtpGetCurrentDirectory, but weirdly not for FtpSetCurrentDirectory).

I tried changing all the parameters in the PtrSafe declarations to Longs, but because some of them are LongPtrs returned by other API calls, to get the code to compile I had to relent and put some parameters back to being LongPtrs.

So either the changes to the API declarations worked, or there was a problem with the previous FTP server that I got rid of which caused FtpFindFirstFile to fail.

Anyway... my working code in full (bear in mind that because this is an open test FTP server, the directories which exist on there are changing all the time... the one in my code below and in the screenshots above is now no longer there!! Just connect to it via File Explorer or FileZilla first to get the name of a directory which currently exists, and set strRemoteDirectory in ListFilesOnFTP to that):

Option Explicit

Private Const FTP_TRANSFER_TYPE_UNKNOWN = &H0
Private Const FTP_TRANSFER_TYPE_ASCII = &H1
Private Const FTP_TRANSFER_TYPE_BINARY = &H2

Private Const INTERNET_SERVICE_FTP = 1
Private Const INTERNET_SERVICE_HTTP = 3

Private Const INTERNET_OPEN_TYPE_PRECONFIG = 0 ' use registry configuration
Private Const INTERNET_OPEN_TYPE_DIRECT = 1 ' direct to net
Private Const INTERNET_OPEN_TYPE_PROXY = 3 ' via named proxy
Private Const INTERNET_OPEN_TYPE_PRECONFIG_WITH_NO_AUTOPROXY = 4 ' prevent using java/script/INS

Private Const INTERNET_CONNECTION_CONFIGURED = &H40
Private Const INTERNET_CONNECTION_LAN = &H2
Private Const INTERNET_CONNECTION_MODEM = &H1
Private Const INTERNET_CONNECTION_OFFLINE = &H20
Private Const INTERNET_CONNECTION_PROXY = &H4
Private Const INTERNET_RAS_INSTALLED = &H10

Private Const INTERNET_INVALID_PORT_NUMBER = 0
Private Const INTERNET_DEFAULT_FTP_PORT = 21
Private Const INTERNET_DEFAULT_GOPHER_PORT = 70
Private Const INTERNET_DEFAULT_HTTP_PORT = 80
Private Const INTERNET_DEFAULT_HTTPS_PORT = 443
Private Const INTERNET_DEFAULT_SOCKS_PORT = 1080
Private Const INTERNET_NO_CALLBACK = 0

Private Const INTERNET_FLAG_PASSIVE = &H8000000 ' used for FTP connections
Private Const INTERNET_FLAG_RELOAD = &H80000000
Private Const INTERNET_FLAG_NO_CACHE_WRITE = &H4000000

Private Const MAX_PATH As Integer = 260
Private Const GENERIC_READ = &H80000000
Private Const MAXDWORD As Double = (2 ^ 32) - 1
Private Const ERROR_NO_MORE_FILES = 18&

Private Type FILETIME

    dwLowDateTime As Long
    dwHighDateTime As Long
    
End Type

Private 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

#If Win64 Then

    Private Declare PtrSafe Function InternetGetConnectedState Lib "wininet.dll" ( _
        ByRef dwFlags As Long, ByVal dwReserved As Long) As LongPtr
    
    Private Declare PtrSafe Function InternetCheckConnection Lib "wininet.dll" Alias "InternetCheckConnectionA" ( _
        ByVal lpszUrl As String, _
        ByVal dwFlags As Long, _
        ByVal dwReserved As Long) As Boolean
    
    Private Declare PtrSafe Function InternetCloseHandle Lib "wininet.dll" ( _
        ByVal hInet As LongPtr) As LongPtr

    Private Declare PtrSafe 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 LongPtr

    Private Declare PtrSafe Function InternetConnect Lib "wininet.dll" Alias "InternetConnectA" ( _
        ByVal hInternetSession As LongPtr, _
        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 LongPtr

    Private Declare PtrSafe Function FtpSetCurrentDirectory Lib "wininet.dll" Alias "FtpSetCurrentDirectoryA" ( _
        ByVal hFtpSession As LongPtr, _
        ByVal lpszDirectory As String) As Boolean

    Private Declare PtrSafe Function FtpGetCurrentDirectory Lib "wininet.dll" Alias "FtpGetCurrentDirectoryA" ( _
        ByVal hFtpSession As LongPtr, _
        ByVal lpszCurrentDirectory As String, _
        ByVal lpdwCurrentDirectory As LongPtr) As Boolean

    Private Declare PtrSafe Function InternetGetLastResponseInfo Lib "wininet.dll" Alias "InternetGetLastResponseInfoA" ( _
        ByVal lpdwError As Long, _
        ByVal lpszBuffer As String, _
        ByVal lpdwBufferLength As Long) As Boolean

    Private Declare PtrSafe Function FtpFindFirstFile Lib "wininet.dll" Alias "FtpFindFirstFileA" ( _
        ByVal hFtpSession As LongPtr, _
        ByVal lpszSearchFile As String, _
        ByRef lpFindFileData As WIN32_FIND_DATA, _
        ByVal dwFlags As Long, _
        ByVal dwContent As Long) As LongPtr

    Private Declare PtrSafe Function InternetFindNextFile Lib "wininet.dll" Alias "InternetFindNextFileA" ( _
        ByVal hFind As LongPtr, _
        ByRef lpFindData As WIN32_FIND_DATA) As LongPtr

#Else

    Private Declare Function InternetGetConnectedState Lib "wininet.dll" ( _
        ByRef dwflags As Long, ByVal dwReserved As Long) As Long
    
    Private Declare Function InternetCheckConnection Lib "wininet.dll" Alias "InternetCheckConnectionA" ( _
        ByVal lpszUrl As String, _
        ByVal dwFlags As Long, _
        ByVal dwReserved As Long) As Boolean
    
    Private Declare Function InternetCloseHandle Lib "wininet.dll" ( _
        ByVal hInet As Long) As Long

    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

    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

    Private Declare Function FtpSetCurrentDirectory Lib "wininet.dll" Alias "FtpSetCurrentDirectoryA" ( _
        ByVal hFtpSession As Long, _
        ByVal lpszDirectory As String) As Boolean
    
    Private Declare Function FtpGetCurrentDirectory Lib "wininet.dll" Alias "FtpGetCurrentDirectoryA" ( _
        ByVal hFtpSession As Long, _
        ByVal lpszCurrentDirectory As String, _
        lpdwCurrentDirectory As Long) As Boolean

    Private Declare Function InternetGetLastResponseInfo Lib "wininet.dll" Alias "InternetGetLastResponseInfoA" ( _
        lpdwError As Long, _
        ByVal lpszBuffer As String, _
        lpdwBufferLength As Long) As Boolean

    Private 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 InternetFindNextFile Lib "wininet.dll" Alias "InternetFindNextFileA" ( _
        ByVal hFind As Long, _
        lpFindData As WIN32_FIND_DATA) As Long

#End If

Public Sub EnumFiles(ByVal hConnection As LongPtr)

    Dim pData As WIN32_FIND_DATA
    
    #If Win64 Then
    
        Dim hFind As LongPtr, lRet As LongPtr
    
    #Else
    
        Dim hFind As Long, lRet As Long
    
    #End If

    ' Create a buffer
    pData.cFileName = String(MAX_PATH, vbNullChar)

    ' Find the first file
    hFind = FtpFindFirstFile(hConnection, "*.*", pData, INTERNET_FLAG_RELOAD Or INTERNET_FLAG_NO_CACHE_WRITE, 0)
    
    ' If there's no file, then exit sub
    If hFind = 0 Then Exit Sub

    ' Show the filename
    Debug.Print vbNewLine & "FILES FOUND:" & vbNewLine & Left$(pData.cFileName, InStr(1, pData.cFileName, String(1, 0), vbBinaryCompare) - 1)

    Do
    
        ' Create a buffer
        pData.cFileName = String(MAX_PATH, vbNullChar)
        
        ' Find the next file
        lRet = InternetFindNextFile(hFind, pData)
        
        ' If there's no next file, exit loop
        If lRet = 0 Then Exit Do
        
        ' Show the filename
        Debug.Print Left$(pData.cFileName, InStr(1, pData.cFileName, String(1, 0), vbBinaryCompare) - 1)
    
    Loop

    ' Close the search handle
    InternetCloseHandle hFind

End Sub

Public Sub ListFilesOnFTP()

    #If Win64 Then

        Dim hOpen As LongPtr, hConnection As LongPtr, lngCurrentDirLength As LongPtr

    #Else
    
        Dim hOpen As Long, hConnection As Long, lngCurrentDirLength As Long
    
    #End If
    
    Dim fConnectionTestFlags As Long
    Dim blCheckConnection As Boolean
    Dim blCheckGetDirSuccess As Boolean, blCheckSetDirSuccess As Boolean
    Dim strFTPServerIP As String, strUsername As String, strPassword As String
    Dim strRemoteDirectory As String, strCurrentDirectory As String
    
    strFTPServerIP = "35.163.228.146"
    strUsername = "dlpuser"
    strPassword = "rNrKYTX9g7z3RgJRmxWuGHbeu"
    strRemoteDirectory = "File"
    
    ' Open an internet connection
    hOpen = InternetOpen("FTP Client", _
        INTERNET_OPEN_TYPE_DIRECT, _
        vbNullString, _
        vbNullString, _
        0)
    
    hConnection = InternetConnect( _
        hOpen, _
        strFTPServerIP, _
        INTERNET_DEFAULT_FTP_PORT, _
        strUsername, _
        strPassword, _
        INTERNET_SERVICE_FTP, _
        INTERNET_FLAG_PASSIVE, _
        INTERNET_NO_CALLBACK)
    
    blCheckConnection = InternetCheckConnection(strFTPServerIP, 0, 0)
    
    If blCheckConnection Then
    
        InternetGetConnectedState fConnectionTestFlags, 0
        
        ' Debug.Print "INTERNET_CONNECTION_CONFIGURED = " & CBool((fConnectionTestFlags And INTERNET_CONNECTION_CONFIGURED) > 0)
        ' Debug.Print "INTERNET_CONNECTION_LAN = " & CBool((fConnectionTestFlags And INTERNET_CONNECTION_LAN) > 0)
        ' Debug.Print "INTERNET_CONNECTION_MODEM = " & CBool((fConnectionTestFlags And INTERNET_CONNECTION_MODEM) > 0)
        ' Debug.Print "INTERNET_CONNECTION_OFFLINE = " & CBool((fConnectionTestFlags And INTERNET_CONNECTION_OFFLINE) > 0)
        ' Debug.Print "INTERNET_CONNECTION_PROXY = " & CBool((fConnectionTestFlags And INTERNET_CONNECTION_PROXY) > 0)
        ' Debug.Print "INTERNET_RAS_INSTALLED = " & CBool((fConnectionTestFlags And INTERNET_RAS_INSTALLED) > 0)
    
        blCheckSetDirSuccess = FtpSetCurrentDirectory(hConnection, strRemoteDirectory)
        
        Debug.Print "Set current directory successful = " & blCheckSetDirSuccess
        
        If blCheckSetDirSuccess Then
        
            ' Create buffer
            strCurrentDirectory = String(MAX_PATH, vbNullChar)

            blCheckGetDirSuccess = FtpGetCurrentDirectory(hConnection, strCurrentDirectory, lngCurrentDirLength)
            
            If blCheckGetDirSuccess Then
            
                Debug.Print "Current directory = " & strCurrentDirectory
            
            Else
            
                Debug.Print "Get current directory call failed - " & GetError
                Debug.Print "LastDllError code = " & Err.LastDllError
            
            End If
        
            Call EnumFiles(hConnection)
            
        End If
    
    End If
    
    InternetCloseHandle hConnection
    InternetCloseHandle hOpen

End Sub

Private Function GetError() As String

    Dim lngErrorCode As Long, strError As String, lngBufferLength As Long
    Dim blGetInfoSuccess As Boolean
    
    ' Get the required buffer size
    InternetGetLastResponseInfo lngErrorCode, strError, lngBufferLength
    
    ' Create a buffer
    strError = String(lngBufferLength, 0)
    
    ' Retrieve the last response info
    blGetInfoSuccess = InternetGetLastResponseInfo(lngErrorCode, strError, lngBufferLength)
    
    If blGetInfoSuccess Then
    
        GetError = "Error code " & CStr(lngErrorCode) & ": " & strError
    
    Else
    
        GetError = "error information could not be retrieved"
    
    End If

End Function