FTP Current Dir Call Crashes Excel

[hamiltpj]

Hi there,

I am building an application that uses wininet ftp calls, all of which are working fine except from my request for the current directory. The following is my (cut down) code...

    Option Explicit
    
    Private Const MAX_PATH = 260
    Private Const INTERNET_OPEN_TYPE_PRECONFIG = 0
    Private Const INTERNET_DEFAULT_FTP_PORT = 21
    Private Const INTERNET_SERVICE_FTP = 1
    
    Private Declare Function InternetOpen Lib "wininet.dll" Alias "InternetOpenA" (ByVal lpszAgent As String, ByVal dwAccessType As Long, ByVal lpszProxyName As String, ByVal lpszProxyBypass As String, ByVal dwFlags As Long) As Long
    Private Declare Function InternetConnect Lib "wininet.dll" Alias "InternetConnectA" (ByVal hInternetSession As Long, 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 Long
    Private Declare Function InternetCloseHandle Lib "wininet.dll" (ByVal hInet As Long) As Long
    Private Declare Function FtpGetCurrentDirectory Lib "WinInet" Alias "FtpGetCurrentDirectoryA" (ByVal hFtp As Long, lpszDirectory As String, ByVal BuffLength As Long) As Long
    
    Private hConnect As Long
    Private hFtp As Long
    
    Public Sub Connect(Server As String, User As String, pwd As String)
        hConnect = InternetOpen("Microsoft Excel", INTERNET_OPEN_TYPE_PRECONFIG, vbNullString, vbNullString, 0)
        hFtp = InternetConnect(hConnect, Server, INTERNET_DEFAULT_FTP_PORT, User, pwd, INTERNET_SERVICE_FTP, 0, 0)
    End Sub
    
    Public Sub Disconnect()
        If hConnect <> 0 Then
            InternetCloseHandle hConnect
        End If
    End Sub
    
    Public Function CurrentDir() As String
        Dim RetVal As String * MAX_PATH
        FtpGetCurrentDirectory hFtp, RetVal, Len(RetVal)
        CurrentDir = Trim(RetVal)
    End Function
    

    Public Function CurrentDir() As String
        Dim RetVal As String * MAX_PATH
        FtpGetCurrentDirectory hFtp, RetVal, Len(RetVal)
        CurrentDir = Trim(RetVal)
    End Function

Please help!

Pete

 

[Delphima]

I have the same problem or question. I googled and find this page pinvoke.net: FtpGetCurrentDirectory (wininet)
It seems to tell us that we have to build up something which can receive string text, and code in the page is:

Dim s As New System.Text.StringBuilder

But, when I put it into my VBA project, it gives me ' Compil error: User-defined type not defined'

I also tried something like below:

  Dim sCrnD As Object
  Set sCrnD = CreateObject("System.Text.StringBuilder"): sCrnD.Capacity = MAX_PATH
  Dim hCrnD As Long: hCrnD = FtpGetCurrentDirectoryA(hConn, sCrnD, MAX_PATH)

but still error code happened in FtpGetCurrentDirectoryA line
Run-time error '5':
Invalid procedure call or argument

So, I eventually give it up, and use:

'Check Current Directory (Send 'PWD' to server)
  Dim hCrnD As Long: hCrnD = FtpGetCurrentDirectoryA(hConn, "", MAX_PATH)

It can send PWD commands to the FTP server which satisfies me well.

 

[Delphima]

BTW, MAX_PATH was set as below

Private Const MAX_PATH                    As Long = 260