VBA FTP file download doesn't work on 64bit machines

[alexgoaga]

Hello,


I'm having some problems regarding the editing and making the code to compile and work successfully on 64-bit Versions

https://www.jkp-ads.com/articles/apideclarations.asp

-> I've followed the differences of the code using the site here were each declaration can be saw on both versions and with a windows api program to display functions declaration correctly but on 64 bit machines the file is detected, but not downloaded. The same code works and downloads the entire test file correctly.</div>



Piece of code (also the entire document has been attached)


Please note
- I've setup a private ftp server with and user and password test with no write access (this will be my case).
- After the problem is solved the user will be deleted.
- The server uses an ftp active connection type.
- It will not work to connect with filezilla.




If someone can suggest a place or a tutorial or something in order to arrange correctly the transition from 32 bit code to 64 i will be forever grateful.

Public Const INTERNET_FLAG_RELOAD = &H80000000


Const MAX_PATH = 260
Const INTERNET_SERVICE_FTP = 1


 
Private Type WIN32_FIND_DATA
    dwFileAttributes As Long
    ftCreationTime As Currency          'low & high 32 bits as 64-bit data type
    ftLastAccessTime As Currency
    ftLastWriteTime As Currency
    nFileSizeHigh As Long
    nFileSizeLow As Long
    dwReserved0 As Long
    dwReserved1 As Long
    cFileName As String * MAX_PATH
    cAlternate As String * 14
End Type
 


[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=If]#If[/URL]  Win64 Then


'Trebuie facuta trecerea si pe x64




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 Long) As LongPtr


Private Declare PtrSafe Function InternetConnect Lib "wininet.dll" Alias "InternetConnectA" _
    (ByVal hInternetSession As LongPtr, _
    ByVal sServerName As String, _
    ByVal nServerPort As Integer, _
    ByVal sUsername As String, _
    ByVal sPassword As String, _
    ByVal lService As LongPtr, _
    ByVal lFlags As LongPtr, _
    ByVal lContext As LongPtr) As Long
 
Private Declare PtrSafe Function FtpGetFile Lib "wininet.dll" Alias "FtpGetFileA" _
    (ByVal hFtpSession As LongPtr, _
    ByVal lpszRemoteFile As String, _
    ByVal lpszNewFile As String, _
    ByVal fFailIfExists As Boolean, _
    ByVal dwFlagsAndAttributes As LongPtr, _
    ByVal dwFlags As LongPtr, _
    ByVal dwContext As LongPtr) As Boolean
 
Private Declare PtrSafe Function FtpPutFile Lib "wininet.dll" Alias "FtpPutFileA" _
    (ByVal hFtpSession As LongPtr, _
    ByVal lpszLocalFile As String, _
    ByVal lpszRemoteFile As String, _
    ByVal dwFlags As LongPtr, _
    ByVal dwContext As LongPtr) As Boolean
 
Private Declare PtrSafe Function FtpSetCurrentDirectory Lib "wininet.dll" Alias "FtpSetCurrentDirectoryA" _
    (ByVal hFtpSession As LongPtr, _
    ByVal lpszDirectory As String) As Boolean
 
Private Declare PtrSafe Function FtpFindFirstFile Lib "wininet.dll" Alias "FtpFindFirstFileA" _
    (ByVal hFtpSession As LongPtr, _
    ByVal lpszSearchFile As String, _
    lpFindFileData As WIN32_FIND_DATA, _
    ByVal dwFlags As LongPtr, _
    ByVal dwContent As LongPtr) As Long
 
Private Declare PtrSafe Function InternetFindNextFile Lib "wininet.dll" Alias "InternetFindNextFileA" _
    (ByVal hFind As LongPtr, _
    lpFindFileData As WIN32_FIND_DATA) As Long


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








[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=Else]#Else[/URL] 






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 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
 
Private 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
 
Private Declare Function FtpSetCurrentDirectory Lib "wininet.dll" Alias "FtpSetCurrentDirectoryA" _
    (ByVal hFtpSession As Long, _
    ByVal lpszDirectory As String) 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, _
    lpFindFileData As WIN32_FIND_DATA) As Long


Private Declare Function InternetCloseHandle Lib "wininet.dll" _
    (ByVal hInet As Long) As Integer






[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=End]#End[/URL]  If

Please note that the issue has been posted also here --> https://www.excelforum.com/excel-pr...e-download-doesnt-work-on-64bit-machines.html but without any answers so far.

 

[alexgoaga]

i've tested and made the modifications. I even setup a vm with excel 64 bit (tested with you code aswell) and still the issue is the same.

 

[alexgoaga]

After some time and new info I managed to fix all the issues but 1 the new code is :
ret = FtpFindFirstFile(hConn, remoteMatchFiles, fileFind, INTERNET_FLAG_RELOAD Or INTERNET_FLAG_NO_CACHE_WRITE, 0)


i think the last problem comes from this part :

Private Type WIN32_FIND_DATA
    dwFileAttributes As Long
    ftCreationTime As Currency          'low & high 32 bits as 64-bit data type
    ftLastAccessTime As Currency
    ftLastWriteTime As Currency
    nFileSizeHigh As Long
    nFileSizeLow As Long
    dwReserved0 As Long
    dwReserved1 As Long
    cFileName As String * MAX_PATH
    cAlternate As String * 14
End Type

Final code without errors but 1

Public Const INTERNET_FLAG_RELOAD = &H80000000ret = FtpFindFirstFile(hConn, remoteMatchFiles, fileFind, INTERNET_FLAG_RELOAD Or INTERNET_FLAG_NO_CACHE_WRITE, 0)

Const MAX_PATH = 260
Const INTERNET_SERVICE_FTP = 1
 
Private Type WIN32_FIND_DATA
    dwFileAttributes As Long
    ftCreationTime As Currency          'low & high 32 bits as 64-bit data type
    ftLastAccessTime As Currency
    ftLastWriteTime As Currency
    nFileSizeHigh As Long
    nFileSizeLow As Long
    dwReserved0 As Long
    dwReserved1 As Long
    cFileName As String * MAX_PATH
    cAlternate As String * 14
End Type
 
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 Integer, _
    ByVal sUsername As String, _
    ByVal sPassword As String, _
    ByVal lService As Long, _
    ByVal lFlags As Long, _
    ByVal lContext As LongPtr) As LongPtr
 
Private Declare PtrSafe Function FtpGetFile Lib "wininet.dll" Alias "FtpGetFileA" _
    (ByVal hFtpSession As LongPtr, _
    ByVal lpszRemoteFile As String, _
    ByVal lpszNewFile As String, _
    ByVal fFailIfExists As Boolean, _
    ByVal dwFlagsAndAttributes As Long, _
    ByVal dwFlags As Long, _
    ByVal dwContext As LongPtr) As Boolean
 
Private Declare PtrSafe Function FtpPutFile Lib "wininet.dll" Alias "FtpPutFileA" _
    (ByVal hFtpSession As LongPtr, _
    ByVal lpszLocalFile As String, _
    ByVal lpszRemoteFile As String, _
    ByVal dwFlags As LongPtr, _
    ByVal dwContext As LongPtr) As Boolean
 
Private Declare PtrSafe Function FtpSetCurrentDirectory Lib "wininet.dll" Alias "FtpSetCurrentDirectoryA" _
    (ByVal hFtpSession As LongPtr, _
    ByVal lpszDirectory As String) As Boolean
 
Private Declare PtrSafe Function FtpFindFirstFile Lib "wininet.dll" Alias "FtpFindFirstFileA" _
    (ByVal hFtpSession As LongPtr, _
    ByVal lpszSearchFile As String, _
    lpFindFileData As WIN32_FIND_DATA, _
    ByVal dwFlags As Long, _
    ByVal dwContext As LongPtr) As Long
     
Private Declare PtrSafe Function InternetFindNextFile Lib "wininet.dll" Alias "InternetFindNextFileA" _
    (ByVal hFind As LongPtr, _
    lpFindFileData As WIN32_FIND_DATA) As Long


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






 
Public Sub Ftp_Download_Newest_File()
     


        Dim ret As LongPtr
        Dim hFind As LongPtr
        Dim hOpen As LongPtr
        Dim hConn As LongPtr


    
    
    Dim hostName As String, port As Long, username As String, password As String
    Dim localFolder As String
    Dim remoteDirectory As String, remoteMatchFiles As String
    Dim ftpMode As Long
    Dim fileFind As WIN32_FIND_DATA
    Dim newestFileTime As Currency
    Dim newestFileName As String
    
    
    
    '========== User-defined settings ==========
    
    localFolder = Locatie_Desktop()
    hostName = "incasuta.ddns.net"
    port = 21
    username = "test"
    password = "test"
    remoteDirectory = "/"
    remoteMatchFiles = "*"
    '===========================================
    
    ftpMode = 0                         'active mode FTP
    'ftpMode = INTERNET_FLAG_PASSIVE    'passive mode FTP
    
    ret = InternetOpen("ftp VBA", 1, vbNullString, vbNullString, 0)
    hOpen = ret
    
    If ret > 0 Then
        ret = InternetConnect(hOpen, hostName, port, username, password, INTERNET_SERVICE_FTP, ftpMode, 0)
        hConn = ret
    End If
    
    If ret > 0 Then
        ret = FtpSetCurrentDirectory(hConn, remoteDirectory)
    End If
    
    If ret > 0 Then
        
        'Find first matching file
        
        fileFind.cFileName = String(MAX_PATH, vbNullChar)
        ret = FtpFindFirstFile(hConn, remoteMatchFiles, fileFind, INTERNET_FLAG_RELOAD Or INTERNET_FLAG_NO_CACHE_WRITE, 0)
        ' ^ --- issue here above at ret file it gives error  87???
        hFind = ret
    
        While ret > 0
            Debug.Print TrimNulls(fileFind.cFileName)
            
            'Is this file newer than the newest file seen so far?
            
            'If fileFind.ftLastWriteTime > newestFileTime Then
                newestFileTime = fileFind.ftLastWriteTime
                newestFileName = TrimNulls(fileFind.cFileName)
            'End If
            
            'Find next matching file
            
            fileFind.cFileName = String(MAX_PATH, vbNullChar)
            ret = InternetFindNextFile(hFind, fileFind)
        
        Wend
        
        Debug.Print "Newest "; newestFileName
        
        'Download the newest file to local folder
        
        ret = FtpGetFile(hConn, newestFileName, localFolder & newestFileName, False, 0, FTP_TRANSFER_TYPE_BINARY Or INTERNET_FLAG_RELOAD, 0)
        
        If ret = 0 Then
            Debug.Print "FtpGetFile error "; Err.LastDllError
        Else
        MsgBox "download ok"
        End If
    
    End If
    
    'Release handles
    
    InternetCloseHandle hFind
    InternetCloseHandle hConn
    InternetCloseHandle hOpen
    
End Sub




Private Function TrimNulls(buffer As String) As String
    TrimNulls = Left(buffer, InStr(buffer, vbNullChar) - 1)
End Function