Wininet FTP Error with FTPFindFirstFile 64-Bit Excel

C

Cyrrhus

New Member
Joined
Jun 10, 2015
Messages
2
I've written some code to use wininet to list and then later download a variety of files on an FTP site. When using Microsoft Office 2010 32-bit the code works fine; however, when using Office 2013 64-bit the program returns garbage info in the WIN32_FIND_DATA type. The correct results when running this program should be a debug output as shown below.
Found winzip145.exe
Found winzip145bp.exe
Found winzip145jp.exe
​

The 64-bit version of Office 2013 that I have prints the following:
Found
Found
Found
​

It seems to be finding the right amount of files, but the data in the WIN32_FIND_DATA type is messed up and missing a vbnullstring so it prints a blank string. Anyone have an idea of what's going on or how to fix it? See the code below.

Thanks,


Code: 
Option Explicit
 
Const MAX_PATH As Integer = 260
Const INTERNET_SERVICE_FTP = 1
Const INTERNET_FLAG_RELOAD = &H80000000
Const INTERNET_FLAG_NO_CACHE_WRITE = &H4000000

#If VBA7 Then
    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 Integer, _
    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 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 LongPtr
     
    Private Declare PtrSafe Function InternetFindNextFile Lib "wininet.dll" Alias "InternetFindNextFileA" _
    (ByVal hFind As LongPtr, _
    lpFindFileData As WIN32_FIND_DATA) As LongPtr
     
    Private Declare PtrSafe Function InternetCloseHandle Lib "wininet.dll" _
    (ByVal hInet As LongPtr) As Integer
    
    Private Type FILETIME
        dwLowDateTime As LongPtr
        dwHighDateTime As LongPtr
    End Type
    
    Private Type WIN32_FIND_DATA
        dwFileAttributes As LongPtr
        ftCreationTime As FILETIME
        ftLastAccessTime As FILETIME
        ftLastWriteTime As FILETIME
        nFileSizeHigh As LongPtr
        nFileSizeLow As LongPtr
        dwReserved0 As LongPtr
        dwReserved1 As LongPtr
        cFileName As String * MAX_PATH
        cAlternate As String * 14
    End Type

#Else
    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 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
    
    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
#End If

Public Sub TestFTP()
  
    #If VBA7 Then
        Dim hOpen As LongPtr, hConn As LongPtr, hFind As LongPtr, ret As LongPtr, ftpMode As LongPtr
    #Else
        Dim hOpen As Long, hConn As Long, hFind As Long, ret As Long, ftpMode As Long
    #End If
    
    Dim hostName As String, username As String, password As String
    Dim remoteDirectory As String, remoteMatchFiles As String, sTargetFileName As String
    Dim fileFind As WIN32_FIND_DATA
    Dim fDate As Date
    Dim port As Integer, iCount As Integer
    
    'testing the ftp functions
     '========== User-defined settings ==========
    hostName = "ftp2.winzip.com"
    port = 21
    username = ""
    password = ""
    remoteDirectory = "/ohhp/"
    remoteMatchFiles = "*"
     '===========================================
     
    ftpMode = 1 'active 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 file
        fileFind.cFileName = String(MAX_PATH, 0)
        ret = FtpFindFirstFile(hConn, remoteMatchFiles, fileFind, INTERNET_FLAG_RELOAD Or INTERNET_FLAG_NO_CACHE_WRITE, 0)
        
        hFind = ret
        
        While ret > 0
            
            iCount = iCount + 1
            sTargetFileName = Left((fileFind.cFileName), InStr(1, fileFind.cFileName, String(1, 0), vbBinaryCompare) - 1)
            
            'fDate = CDate((fileFind.ftLastWriteTime / 86400000#) - 109205#)

            'print the file name and date
            Debug.Print "Found " & sTargetFileName & vbTab '& fDate
            
            'Find next matching file
            fileFind.cFileName = String(MAX_PATH, 0)
            ret = InternetFindNextFile(hFind, fileFind)
        Wend

    End If

    InternetCloseHandle hFind
    InternetCloseHandle hConn
    InternetCloseHandle hOpen
End Sub
 

Excel Facts

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.
Sort by date Sort by votes
AFAIK, all the LongPtr parts in your type declaration should actually be Long.
 
Upvote 0
RoryA said:
AFAIK, all the LongPtr parts in your type declaration should actually be Long.
Click to expand...

Thank you so much! This had me stumped for a while and it's working great now.

I removed the FILETIME and WIN32_FIND_DATA type definition from the VBA7 conditional and changed all the LongPtr parts to Long. I've attached the working code below for clarification for anyone who is interested.

Code: 
Option Explicit
 
Const MAX_PATH As Integer = 260
Const INTERNET_SERVICE_FTP = 1
Const INTERNET_FLAG_RELOAD = &H80000000
Const INTERNET_FLAG_NO_CACHE_WRITE = &H4000000

Private Type WIN32_FIND_DATA
    dwFileAttributes As Long
    ftCreationTime As Currency
    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


#If VBA7 Then
    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 Integer, _
    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 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 LongPtr
     
    Private Declare PtrSafe Function InternetFindNextFile Lib "wininet.dll" Alias "InternetFindNextFileA" _
    (ByVal hFind As LongPtr, _
    lpFindFileData As WIN32_FIND_DATA) As LongPtr
     
    Private Declare PtrSafe Function InternetCloseHandle Lib "wininet.dll" _
    (ByVal hInet As LongPtr) As Integer
    
#Else
    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 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
    
#End If

Public Sub TestFTP()
  
    #If VBA7 Then
        Dim hOpen As LongPtr, hConn As LongPtr, hFind As LongPtr, ret As LongPtr, ftpMode As LongPtr
    #Else
        Dim hOpen As Long, hConn As Long, hFind As Long, ret As Long, ftpMode As Long
    #End If
    
    Dim hostName As String, username As String, password As String
    Dim remoteDirectory As String, remoteMatchFiles As String, sTargetFileName As String
    Dim fileFind As WIN32_FIND_DATA
    Dim fDate As Date
    Dim port As Integer, iCount As Integer
    
    'testing the ftp functions
     '========== User-defined settings ==========
    hostName = "ftp2.winzip.com"
    port = 21
    username = ""
    password = ""
    remoteDirectory = "/ohhp/"
    remoteMatchFiles = "*"
     '===========================================
     
    ftpMode = 1 'active 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 file
        fileFind.cFileName = String(MAX_PATH, 0)
        ret = FtpFindFirstFile(hConn, remoteMatchFiles, fileFind, INTERNET_FLAG_RELOAD Or INTERNET_FLAG_NO_CACHE_WRITE, 0)
        
        hFind = ret
        
        While ret > 0
            
            iCount = iCount + 1
            sTargetFileName = Left((fileFind.cFileName), InStr(1, fileFind.cFileName, String(1, 0), vbBinaryCompare) - 1)
            
            fDate = CDate((fileFind.ftLastWriteTime / 86400000#) - 109205#)

            'print the file name and date
            Debug.Print "Found " & sTargetFileName & vbTab & fDate
            
            'Find next matching file
            fileFind.cFileName = String(MAX_PATH, 0)
            ret = InternetFindNextFile(hFind, fileFind)
        Wend

    End If

    InternetCloseHandle hFind
    InternetCloseHandle hConn
    InternetCloseHandle hOpen
End Sub
 
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
165
Jaafar Tribak
Jaafar Tribak
D
  • Solved
More problems with 64 bit transition
Replies
3
Views
644
davidam
D
G
  • Solved
Getting code to work in 64 Bit Excel
Replies
5
Views
845
Gborden800
G
P
  • Question Question
Excel Macro from 32 to 64 will not run
2
Replies
13
Views
486
Pophil
P
Jaafar Tribak
  • Question Question
Capturing the Paste Event (API Hooking - Redirection)
Replies
4
Views
747
Jaafar Tribak
Jaafar Tribak

Forum statistics

Threads
1,223,228
Messages
6,170,875
Members
452,363
Latest member
merico17

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