Ftp a file using VBA

I am trying to upload a file from within Access using the Microsoft Internet Transfer Control


Function UploadFile(ByVal HostName As String, _
    ByVal UserName As String, _
    ByVal Password As String, _
    ByVal LocalFileName As String, _
    ByVal RemoteFileName As String) As Boolean

    Dim FTP As Inet

    Set FTP = New Inet
    With FTP
        .Protocol = icFTP
        .RemoteHost = HostName
        .UserName = UserName
        .Password = Password
        .Execute .URL, "Put " + LocalFileName + " " + RemoteFileName
        Do While .StillExecuting
        UploadFile = (.ResponseCode = 0)
    End With
    Set FTP = Nothing
End Function

I get the error message : Method 'Execute' of object IInet failed (at the line following Password)

I call it as Call UploadFile("ftp://myftp.com", "myusername", "mypassword", "Test.rtf", "Test.rtf")

Where am I going wrong?????

Many thanks in advance

Who is Participating?
David LeeConnect With a Mentor Commented:
Oh, one other thought.  You shouldn't prefix the address of your ftp site with ftp://.  When I try adding that onto the address I used I get an error too, although not the same one you got.  The error I got was "Unable to connect to remote host".
David LeeCommented:
I tested your code and only had one problem.  That was creating an instance of the Inet control.  I commented out these two lines

    Dim FTP As Inet
    Set FTP = New Inet

replacing then with an instance of the inet control on the form.  I named the control FTP so the rest of your code could remain the same.  I then called your function and uploaded a file to my FTP site without a problem.
dsackerContract ERP Admin/ConsultantCommented:
Separate your URL and Execute statements (or if your .RemoteHost identifies where you're FTP'ing to, you probably don't even need the URL):

    With FTP
        .Protocol = icFTP
        .RemoteHost = HostName    <-- use either this
        .URL = HostName        <-- or this, but not both
        .UserName = UserName
        .Password = Password
        .Execute , "SEND " + LocalFileName + " " + RemoteFileName   <-- SEND is usually the standard for Inet
Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

martmacAuthor Commented:
Many thanks BlueDevilFan , that did the trick
David LeeCommented:
No problem.  Glad I could help.
Hi, BlueDevilFan and dsacker.  I'm having the same problem.  Working in Excel 2003.

I tried both of your suggestions.  But the macro fails at the function's ".Protocol = icFTP" file.  Run-time error '424'.

BlueDevilFan mentioned (1) commenting out Dim FTP As Inet and  Set FTP = New Inet and (2) replacing them with an instance of the inet control on the form.  [he named the control FTP so the rest of the code could remain the same.]  BlueDevilFan, how did you replace that with an instance of the inet control?  

Any help would be appreciated.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.