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