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

A

alexgoaga

New Member
Joined
Jun 9, 2017
Messages
9
Hello,


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


PHP: 
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.


Code: 
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.
 

Excel Facts

Format cells as time
Select range and press Ctrl+Shift+2 to format cells as time. (Shift 2 is the @ sign).
Sort by date Sort by votes
I'm not an expert on the Windows API, but parameters like dwFlags are just data values, not pointers, therefore don't change them to LongPtr. Also, the Win64 compiler constant is True if you've installed the 64-bit version of Excel, not whether Windows is 64-bit. I don't think any of the Internet/Ftp functions you're using differ between 32- and 64-bit Excel, therefore I think you should use the VBA7 constant instead. Try these declarations, untested though:

Code: 
#If VBA7 Then

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
 
Upvote 0
Hello, i've tested your code and a new error is now displayed : type mismatch here --> ret = InternetOpen(

ftpMode = 0 'active mode FTP
'ftpMode = INTERNET_FLAG_PASSIVE 'passive mode FTP

located here after the ftp mode declaration :
ret = InternetOpen("ftp VBA", 1, vbNullString, vbNullString, 0)
 
Upvote 0
See if this works :
Code: 
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 Long, _
    ByVal sProxyName As String, _
    ByVal sProxyBypass As String, _
    ByVal lFlags As Long) As Long

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

Private Declare PtrSafe Function InternetCloseHandle Lib "wininet.dll" _
    (ByVal hInet As Long) 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

 
Public Sub Ftp_Download_Newest_File()
     
    Dim hOpen As Long, hConn As Long, hFind As Long
    Dim ret As Long
    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)
        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
 
Last edited:
Upvote 0
alexgoaga said:
Hello, i've tested your code and a new error is now displayed : type mismatch here --> ret = InternetOpen(

ftpMode = 0 'active mode FTP
'ftpMode = INTERNET_FLAG_PASSIVE 'passive mode FTP

located here after the ftp mode declaration :
ret = InternetOpen("ftp VBA", 1, vbNullString, vbNullString, 0)
Click to expand...
I haven't looked at your whole code, only the code posted here. The VBA7 declaration of InternetOpen returns a LongPtr, therefore your declaration of the above 'ret' variable should be:

Code: 
#If VBA7 Then
   Dim ret As LongPtr
[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=Else]#Else[/URL] 
   Dim ret As Long
[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=End]#End[/URL]  If
 
Upvote 0
i've made the following changes and

Code: 
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 Long, _
    ByVal sProxyName As String, _
    ByVal sProxyBypass As String, _
    ByVal lFlags As Long) As Long


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


Private Declare PtrSafe Function InternetCloseHandle Lib "wininet.dll" _
    (ByVal hInet As Long) 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


 
Public Sub Ftp_Download_Newest_File()
     
    Dim hOpen As Long, hConn As Long


    
    [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=If]#If[/URL]  VBA7 Then
        Dim ret As LongPtr
        Dim hFind As LongPtr
    [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=Else]#Else[/URL] 
       Dim ret As Long
       hFind As Long
    [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=End]#End[/URL]  If
    
    
    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 '<---------- HERE IS THE NEW ERROR
    
    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)
        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


Error mismach at hOpen = ret found at :


Code: 
    ret = InternetOpen("ftp VBA", 1, vbNullString, vbNullString, 0)
    hOpen = ret '<-
 
Upvote 0
You need 2 LongPtr declarations for hOpen and hConn then, also within the #If VBA7 clause. Remember, it's not just the API declarations which need to be changed but also the variables used as arguments and the variables which the return values are assigned to.
 
Upvote 0
ok sorry for the response with delay :

Modified to :
Code: 
    [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=If]#If[/URL]  VBA7 Then
        Dim ret As LongPtr
        Dim hFind As LongPtr
        Dim hOpen As LongPtr
        Dim hConn As LongPtr
    [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=Else]#Else[/URL] 
       Dim ret As Long
       Dim hFind As Long
       Dim hOpen As Long
       Dim hConn As Long
    [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=End]#End[/URL]  If

Error now at hopen from here (mismach):

Code: 
    ret = InternetOpen("ftp VBA", 1, vbNullString, vbNullString, 0)
    hOpen = ret
    
    If ret > 0 Then
        ret = InternetConnect([I][B][U]hOpen <---here[/U][/B][/I], hostName, port, username, password, INTERNET_SERVICE_FTP, ftpMode, 0)
        hConn = ret
    End If
 
Upvote 0
That last error suggests the #Else part of the #If Win64 in your code in post no. 7 is being executed, which means your Excel isn't 64-bit. Run this to find out:
Code: 
Sub test()
    [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=If]#If[/URL]  Win64 Then
        MsgBox "Excel is 64-bit"
    [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=Else]#Else[/URL] 
        MsgBox "Excel is 32-bit"
    [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=End]#End[/URL]  If
End Sub
As I said previously, I think you should be using #If VBA7 around the API declarations instead of #If Win64, regardless of bit length.
 
Upvote 0
You must log in or register to reply here.

Similar threads

Jaafar Tribak
  • Question Question
KeyPress Event for Worksheet Cells (Subclassing Without Crashing)
Replies
0
Views
253
Jaafar Tribak
Jaafar Tribak
D
  • Solved
More problems with 64 bit transition
Replies
3
Views
649
davidam
D
Jaafar Tribak
  • Question Question
Capturing the Paste Event (API Hooking - Redirection)
Replies
4
Views
791
Jaafar Tribak
Jaafar Tribak
P
  • Question Question
Excel Macro from 32 to 64 will not run
2
Replies
13
Views
503
Pophil
P
G
  • Solved
Getting code to work in 64 Bit Excel
Replies
5
Views
908
Gborden800
G

Forum statistics

Threads
1,223,911
Messages
6,175,329
Members
452,635
Latest member
laura12345

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top